Set rst = db.OpenRecordset from query

Acke

Registered User.
Local time
Today, 17:53
Joined
Jul 1, 2006
Messages
158
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. Parameter must not be fixed. I have to use Like command, to filter all data containg the string from form field.

Thanks!
 
Thank you for the prompt reply. I don't need to filter the form data.

I have a form field that filters List Box on the form. The List Box is a list of customers and according to the form field's string, list of customers on the List Box is filtered. Now, I want to have a command that will ask user to confirm certain action if the List Box comes down to just one choice. I want to write SQL in VBA as string and to use that string as recordset. Something like this:

dim db as database
dim rst as recordset
dim st as string

st = "apropriate SQL containing Like criteria"

set db = CurrentDB
set rst = db.OpenRecordset (st, dbOpenDynaset )
If rst.recordcount = 1 then
>> appropriate action <<

I hope this clarifies now.
 
Thank you for your message, but I am not familiar with commands used in the suggested posts. Is there other way arround it?

I almoust found the solution on the post: http://www.access-programmers.co.uk/forums/showthread.php?p=959714

I tried that and it works exactly as I want if form field string, witch is a criteria, matches exactly the string in the table. However, I need to filter the record even if the string is not written in full. I need to have an action as soon as the sting filters records to just one. I would imagine I should use Like "*" form_field "*". Is that possible?

Sorry about that, but I am an amateur...
 
We can keep it simpler then if you prefer.
(There's no requirement to be familiar with the commands used in the linked threads though - you just copy the entire function fDAOGenericRst into a new VBA module. You can then use fDAOGenericRst instead of the standard OpenRecordset as described.)

Anyway - for a simpler option, in your query where you have a parameter such as
WHERE FieldName Like Forms!FormName!ControlName
change it's definition to
WHERE FieldName Like Eval("Forms!FormName!ControlName")

Cheers.
 
I didn't ralize that it is a function :o

Thank you, it works now.
 

Users who are viewing this thread

Back
Top Bottom