Error when concatenating combobox value to SELECT query when opening recordset

[chathag]

I have the following piece of code behind a combobox on a form in Access 2003:

Dim RS As ADODB.Recordset

Set RS = New ADODB.Recordset

RS.Open "SELECT field1, field2, FROM table1 " & _
"WHERE field1 =" & Me.combo1, _
Application.CodeProject.Connection, adOpenStatic, adLockOptimistic, adCmdText

MsgBox RS("field1")

The combo box list contains text values and the code is situated behind the on click event. When a selection is made from the list the following error is returned:

Run-Time error '2147217900(8004e14)':
Syntax error (missing operator) in query expression 'field1 = combo1 selected value'.


If I replace Me.combo1 with a value from the list i.e. "WHERE field1 = 'John'" the code works. I am assuming there is some sort of setting behind the combobox that needs adjusting.

Thanks

C

 

[DCrake]

You need to encompass the expression in quotes

"....Where field1 = '" & Me.combo1 & "'"

 

[namliam]

"WHERE field1 =" & Me.combo1
Above syntax is OK, for numbers...

What are you exactly replacing it by??? That is 'John'

Yet in your query you are ending up with
Where Field1 = John << Note the missing ''

So you need to add the ''
"WHERE field1 ='" & Me.combo1 & "'"

To make it work

 

[chathag]

Thank you for your help. I am fairly new to this method of programming, but it is all starting to make sense.