Help!!! How to allow user changable recordset through textbox entry

[NewfieLab]

I almost have this done but I am held up by a syntax problem. below is the info

"EnvConnect" is the name of my connection to the database and I works fine
"qrySampleInfo" is the name of the query in the database I want to connect to (it also works fine), and "EnvData" is the name I called the recordset.
I want the user to be able to specify the name of the town to pull out into the recordset. the following is the code I am trynig to use:

Set EnvData = New Recordset
EnvData.CursorType = adOpenStatic
EnvData.Open "SELECT * FROM qrySampleInfo WHERE (strsitelocation =_ txtTown.text)" ,EnvConnect, , , dbSQLPassThrough

If I specify an actual name of a town in the database in place of txtTown.text the recordset works fine, but with it will not recognize the valuse entered in the textbox. It gives the following error

Run-time error '-2147217904 (80040e10)'
No value given for one or more required parameters.

I am pretty sure it is a syntax problem. I would appreciate any help.

NewfieLab

 

[sixsmithc]

you need to split the SQL and variable because VB doesn't know which is which

EG

dim strSQL as string

strSQL = "select * from [table name] where [Field Name]='" & me.text1.text & "'"

EnvData.Open strSQL

 

[Guest_imported]

Thanks a lot, that is exactly what I wanted and it makes perfect sense to me now.