[Resolved}how to get combo box value into sql statement?

[waely]

hi,
I have a sql statement that get's data based on a combo box value and I can't seem to cencatentate it properly. that's what I'm trying to do:

rs.Open "SELECT * FROM tblEmployees Where Proj = sheet1.combo1.value ", CNN, adOpenDynamic, adLockOptimistic

I want to populate one combo box based on the selection of the previous combo box.

any help is appreciated.
waely

[techgnome]

That's because you've referenced the combo box from inside the SQL statement, which SQL will expect as a literal string.

What you really need is this:
s.Open "SELECT * FROM tblEmployees Where Proj = " & sheet1.combo1.value, CNN, adOpenDynamic, adLockOptimistic

Unless Proj is a text field... in which case you need this:
s.Open "SELECT * FROM tblEmployees Where Proj = '" & sheet1.combo1.value & "'", CNN, adOpenDynamic, adLockOptimistic

-tg

[waely]

thanks tg. Proj is a column in my database. either method I use I get this error message:
[Microsoft][ODBC Microsoft access driver] Syntax error (missing operator) in query expression 'Proj = MSP22003'.

what is the deal?

thanks

[waely]

I changed the statement to bo
rs.Open "SELECT * FROM tblemployees Where Proj" & " = '" & sheet1.combo1.value & "'", CNN, adOpenDynamic, adLockOptimistic

and that fixed it.

thanks