Set rst = db.OpenRecordset() Help!

[Simmo2010]

I want to use Set rst = db.OpenRecordset("query_name") to open a query but the query has a parameter which is a form field. I have the form open but when I get to this line it asks me for an expected parameter!

Any help much appreciated!

 

[Moniker]

Change this:

Set rst = db.OpenRecordset("query_name")

To This:

Set rst = db.OpenRecordset("SELECT * FROM Query_Name WHERE ParameterField = '" & YourFormControlName & "'")

Or, if your Parameter field is numeric:

Set rst = db.OpenRecordset("SELECT * FROM Query_Name WHERE ParameterField = " & YourFormControlName)

Or, if your Parameter field is a date:

Set rst = db.OpenRecordset("SELECT * FROM Query_Name WHERE ParameterField = #" & YourFormControlName "#")

 

[Simmo2010]

Weird thing is the error is now saying 2 parameters expected! But definitely only one in the query!?

Set rst = db.OpenRecordset("SELECT * FROM query_name WHERE ParameterField = '" & [Forms]![frmRepAll]![cmp_name] & "'")

 

[Moniker]

Are you replacing query_name with the name of your query, and ParameterField with the name of the field in the query that is the parameter?

If not, that's two parameters missing in the SQL Select statement.

 

[Simmo2010]

What a muppet! I didnt replace ParameterField with the name!!! What a duffnut! Sorry mate!

 

[Acke]

... and what if I need to filter a record that contains a string from control on the form but does not match entirely. Like if the control contains "Lon", command shuld filter all data containg "Lon", like "London", "Londoner", "long"... How is this possible in VBA? I managed this in query using >> Like '*' & YourFormControlName & '*' <<.

Also, is it possible to use the code Moniker suggested, but without making a query. Is it possible to make the query as string in the code?

Thanks!