Error in query referring to Combo Box

[PaulSchueller]

I am very new to using VBA to build forms and am having a problem. I
have posted in many groups looking for an answer. I am 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.

 

[John Spencer]

Your query string is suspect. You are missing a space before GROUP BY


rsSturID.Open "SELECT tblSturg.SturgID FROM tblSturg WHERE
(tblSturg.PIT = '" & cmbPIT.Value & "')" _
& "GROUP BY tblSturg.SturgID", CurrentProject.Connection,

Personnally, I assign the query string to a variable, so I can examine it
using debug.Print. Also I can copy and paste the string and test the
generated SQL for validity

StrSQL = "SELECT tblSturg.SturgID" & _
" FROM tblSturg" & _
" WHERE (tblSturg.PIT = '" & cmbPIT.Value & "')" & _
" GROUP BY tblSturg.SturgID"

Debug.Print StrSQL 'Comment out once I have valid SQL string

rsSturID.Open strSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdTable
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[PaulSchueller]

Your query string is suspect. You are missing a space before GROUP BY

rsSturID.Open "SELECT tblSturg.SturgID FROM tblSturg WHERE
(tblSturg.PIT = '" & cmbPIT.Value & "')" _
& "GROUP BY tblSturg.SturgID", CurrentProject.Connection,

Personnally, I assign the query string to a variable, so I can examine it
using debug.Print. Also I can copy and paste the string and test the
generated SQL for validity

StrSQL = "SELECT tblSturg.SturgID" & _
" FROM tblSturg" & _
" WHERE (tblSturg.PIT = '" & cmbPIT.Value & "')" & _
" GROUP BY tblSturg.SturgID"

Debug.Print StrSQL 'Comment out once I have valid SQL string

rsSturID.Open strSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdTable
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

It ended up being that the adCmdTable was not needed at the end of the
select statement.