Error in query referring to Combo Box

[PaulSchueller]

I am very new to using VBA to build forms and am having a problem. I
trying to embed a query within a public sub routine. The goal of this
query is to use an alphanumeric code entered into a combo box on the
form to retrieve an associated ID within a table. The bound column of
the combo box is text. I have used the following code:

Public Sub GetSturID(intSturID As Integer)
Dim rsSturID As ADODB.Recordset
Set rsSturID = New ADODB.Recordset
rsSturID.Open "SELECT tblSturg.SturgID FROM tblSturg WHERE
(tblSturg.PIT = '" & cmbPIT.Value & "')" _
& "GROUP BY tblSturg.SturgID", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic, adCmdTable
rsSturID.MoveFirst
intSturID = rsSturID("SturgID")
rsSturID.Close
End Sub

This querying technique I have used before, but not with strings and I
am not sure what the problem is. I have tried many things, but to no
avail. Your help is much appreciated.

 

[George Nicholson]

You can try:
....& " GROUP BY.."
(add a leading space, or, if only one match is expected omit the Group By
altogether...)

DLookup might be a bit easier though.
intSturID = nz(Dlookup("SturgID","tblSturg","[PIT] = '" cmbPIT.Value &
"'"),0)

Dlookup returns Null if no match, which would cause a TypeMismatch without
the NZ() wrapper.

Otherwise, you might provide some details as to what isn't working. I see
you are passing intSturID to your sub for no other reason than to assign it
a new value (effectively making your sub a function). Nothing wrong with
that I guess, but its a bit unusual.

HTH,