Multiple DLookups

[J Shrimps, Jr.]

Have a table I need to search based on a form control.
More than one value may be returned, but Dlookup
only returns the first value.
Using an SQL statement to return all the matching
values doesn't work - always get "Expecting paramater
1" or 2. Howver, pasting the exact same SQL statement
into the query designer DOES return the correct records.
I don't know why I can't -
Set rst = db.OpenRecordset("SELECT tbl.State as states FROM tblStates;")
but it doesn't work - of course the sql statement is more difficult and
includes form controls - but the sql works in Query designer.

Is there a way to get more than one return from Dlookup?

 

[Rick Brandt]

Have a table I need to search based on a form control.
More than one value may be returned, but Dlookup
only returns the first value.
Using an SQL statement to return all the matching
values doesn't work - always get "Expecting paramater
1" or 2. Howver, pasting the exact same SQL statement
into the query designer DOES return the correct records.
I don't know why I can't -
Set rst = db.OpenRecordset("SELECT tbl.State as states FROM
tblStates;") but it doesn't work - of course the sql statement is
more difficult and includes form controls - but the sql works in
Query designer.

A query can use the Access Expression Service to evaluate form references
like...

Forms!FormName!ControlName

SQL strings executed in VBA do not have this capability, but if you move the
reference outside the string it will work. Instead of...

"SELECT tbl.State as states " & _
"FROM tblStates " & _
"WHERE SomeTextField = Forms!MyForm!MyControl"

....use...

"SELECT tbl.State as states " & _
"FROM tblStates " & _
"WHERE SomeTextField = '" & Forms!MyForm!MyControl & "'"