ADO - SQL Not working as intended

[dynamictiger]

I have the following code

Set rst = New ADODB.Recordset

rst.Open "SELECT tblClientEquipment.EquipmentID, tblClientEquipment.PoolID, " & _
"tblClientEquipment.EqType, tblClientEquipment.Model, tblClientEquipment.Quantity " & _
"FROM tblClientEquipment " & _
"WHERE (((tblClientEquipment.PoolID) =" & Form_frmClient("ClientSuburb&quot.Form("cboFindEquipo&quot & " ) " & _
"And ((tblClientEquipment.EqType) = " & Form_frmClient("ClientSuburb&quot.Form("cboFindEquipo&quot & &quot);" _
, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

With rst
If Not .EOF Then
!PoolID = mlngPoolID
!EqType = mlngEquipmentType
!Model = mlngModel
!Quantity = mlngQuantity
mlngEquipmentID = !EquipmentID

MsgBox !PoolID & " " & !EqType & " " & !Model & " " & !Quantity
End If
End With

Which should return a record in the message box. However, it is not. I have a query based on the identical code returning a record. Help??????????

 

[Falcon99]

What result are you getting? No records? Error Message?

Start troubleshooting by replacing the form variables with hard coded values. Does that work?

Is this code in a module or form module?

What version of Access are you working with?

I am more familiar with 97, where form control referencing syntax goes more like this: forms!controlname!control

 

[BillPower]

I’ve 2 observations:

1. PoolID and EqType both equal cboFindEquipo.... Is that right?

"WHERE (((tblClientEquipment.PoolID) =" & Form_frmClient("ClientSuburb&quot.Form("cboFindEquipo&quot & " ) " & _
"And ((tblClientEquipment.EqType) = " & Form_frmClient("ClientSuburb&quot.Form("cboFindEquipo&quot & &quot);" _

2. If Form_frmClient("ClientSuburb&quot.Form("cboFindEquipo&quot ia a control in a subform try

"WHERE (((tblClientEquipment.PoolID) =" & Me!ClientSuburb.Form!cboFindEquipo & " ) " & _
"And ((tblClientEquipment.EqType) = " & Me!ClientSuburb.Form!cboFindEquipo & &quot);" _