ADO - Query - HELP!!!!!!!!

[dynamictiger]

I have the following SQL statement in my code. I am trying to use this in a ADO recordset as set out below.

SELECT tblPoolOrderTreatment.TestOrder, tblPoolOrderTreatment.ReportType, tblPoolTestParameter.TestParameter, tblPoolTestParameter.TestAcronym, tblAdminTestUnit.TestUnit, tblAdminTestUnit.ConversionFactor
FROM tblAdminTestUnit INNER JOIN (tblPoolTestParameter INNER JOIN tblPoolOrderTreatment ON tblPoolTestParameter.TestParameterID = tblPoolOrderTreatment.Parameter) ON tblAdminTestUnit.TestUnitID = tblPoolTestParameter.TestUnit
WHERE (((tblPoolOrderTreatment.ReportType)=[Forms]![frmClient]![ClientSuburb].[Form]![tblClientResults].[Form]![ReportType]))
ORDER BY tblPoolOrderTreatment.TestOrder;

When I place this statement in my code as is e.g. rst.open "Select tblPool etc..", the rst refuses this saying an expected parameter is missing. If I try and run the code as rst.open strSQL the recordset says it is missing the "Select" statement. So I created a query using code and checked the query returns the records I wanted, which it does, and then tried this code which refuses to run as well.

Private Sub ReportType_AfterUpdate()
'As the report type changes we alter the
'labels and make visible to suit
'this code is also called from the sub form

'There are three controls to make visible
'Two controls caption properties to manipulate
Dim rst As ADODB.Recordset
Dim strQueryName As String
Dim strSQL As String
Dim i As Integer

Set rst = New ADODB.Recordset
'rst is the recordset which you have just opened and wish to retrieve the field names for, and I is an integer variable
strSQL = "SELECT tblPoolOrderTreatment.TestOrder, tblPoolOrderTreatment.ReportType," & _
"tblPoolTestParameter.TestParameter, tblPoolTestParameter.TestAcronym, tblAdminTestUnit.TestUnit, " & _
"tblAdminTestUnit.ConversionFactor " & _
"FROM tblAdminTestUnit INNER JOIN (tblPoolTestParameter " & _
"INNER JOIN tblPoolOrderTreatment ON tblPoolTestParameter.TestParameterID = tblPoolOrderTreatment.Parameter) " & _
"ON tblAdminTestUnit.TestUnitID = tblPoolTestParameter.TestUnit " & _
"WHERE (((tblPoolOrderTreatment.ReportType) = " & _
"[Forms]![frmClient]![ClientSuburb].[Form]![tblClientResults].[Form]![ReportType])) " & _
"ORDER BY tblPoolOrderTreatment.TestOrder;"

strQueryName = "AAAAAAAAAAMyTemp"

Call myCreateQuery(strSQL, strQueryName)

rst.Open strQueryName, CurrentProject.Connection

Debug.Print rst.Fields.Count

If Not rst.BOF And rst.EOF Then

For i = 0 To rst.Fields.Count - 1

Me("lbl" & i).Caption = rst.Fields("TestAcronym")

Me("lblO" & i).Caption = rst.Fields("TestUnit")

Me("lbl" & i).Visible = True

Me("txt" & i).Visible = True

Me("lblO").Visible = True

Next

End If

rst.Close

Set rst = Nothing


End Sub

 

[pdx_man]

Quick response, ask if you need further detail.

Just copy and pasting the SQL code from the QBE to code doesn't work when you have parameters. You need to resolve the results of the parameters. Where you have

" ... ThisField = Forms![MyForm]![MyControl_1] AND ThatField = Forms![MyForm]![MyControl_2] ..."

Must now become:

" ... ThisField = " & Forms![MyForm]![MyControl_1] & " AND ThatField = " & Forms![MyForm]![MyControl_2] & " ..."