Set rst = db.OpenRecordset from query

[Acke]

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!

 

[jdraw]

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!

I think you are asking how to change the record source for a form.

There is a video tutorial here that should help.
http://www.datapigtechnologies.com/flashfiles/buildcustomfilter.html

 

[Acke]

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.

 

[LPurvis]

Hi.

Thread linking day it would seem.
http://www.access-programmers.co.uk/forums/showthread.php?t=157597
It's explained a bit more here.

Cheers.

 

[Acke]

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...

 

[LPurvis]

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.

 

[Acke]

I didn't ralize that it is a function

Thank you, it works now.

 

[LPurvis]

Smashing.