Dynamic query parameters

[Bob]

Got controls with a Rowsource using a select statement, the where clause of
the select statement should use a variable existing in code.
Whats the syntax for writing this
Quick sample:

Dim Myvar as string
Myvar = "abcd"
Say the variable setting above was public in scope and declared well before
the form using it is called.
select * from mytable where myfield = "abcd" what I would think to be the
final result sql statement

What should I write in the Rowsource of the control? I dont want parameter
question boxes to pop up. I just want the control to gran the variable and
use it automatically.


Thanks for any help

Bob

 

[Wayne Morgan]

You could rewrite the Row Source using code.

Me.MyControl.RowSource = "select * from mytable where myfield = """ & MyVar
& """"

This will concatenate in the value of MyVar and place double quotes around
it.

You could also place the value of the variable in a table created just for
this purpose then the SQL statement could find it using a DLookup function.