Run a query to return data into subform?

[Stu Dapples]

Hello again!

I have a table in which there is a subform. I would like the subform to return the records relating to a part number entered in the main form.

I have put the SELECT query together and as far as I can see, it is running OK.

If I start the query builder in Access and choose SQLview, paste the code in and hit run, it returns a neat looking table with the relevant data in. However, if I run the same syntax in VBA, it just runs through and does not populate the subform. I am guessing I am missing a command but I am lost as to what it is I need to do....

<Code>
Private Sub Child42_Enter()
Dim strsql As String, rst As New ADODB.Recordset
strsql = "SELECT ProgStep, ProgNo, Robot, IntercoatDry FROM Programs WHERE PartNo = '" & Me.PartNo.Value & "'"
Debug.Print strsql
rst.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
End Sub
</code>
This appears to get teh recordset but not output them....
HELP!!
Stu

 

[jackd]

The code you have shown doesn't show any of the fields on your forms?
Not sure what exactly you are expecting?

You need to set the Record source of your subform to the query

something like (depending on your form)

Me.RecordSource = "select * from Employee order by LName"
or in your case (where me represents your subform)

me.recordsource = strSQL

 

[Stu Dapples]

OK, more by luck than better judgement, I have now managed to populate the table in my form. I linked the parent and child records and pop! In it went....

So is there now a way to say,

If the Program Step value in the table is less than or equal to the program step in the correspnding run detail table, fill the row green (show the user the step is complete)
Along the lines I think of:

If rst.fields("ProgStep")< Rundetail.Fields("ProgStep")>value where RunNo = me.RunNumber.Value then Row.Backcolour = 60928

Which I am sure wont work but I am sure one of you clever types could sort!

 

[Stu Dapples]

I have managed to get this far:

For Each Record in Me.ProgDetail. Form

Which I am going to use for a loop function to step through the records, can anyone give me the syntax to look at the value of a particular filed within the table? I wan't to fill the row green if the value of the first field is less than the active step (the first field is the steps of the program the product goes though, this is held in another table called RunDetail)

Can't quite get my head around it and not sure it can actually be done....

Any help appreciated,

Stu