Changing the recordsource

[JBParker]

I have a form attatched to a query object. My switchboard has two buttons. One button will open the form to a new record. The other button needs to open the form based upon a user entered parameter.I know I need to change the recordsource of the form to either a VBA SQL statement, or to another query object. I'm having trouble with the actual code to do this. Do I need to dimension variables as strings or recordsets to do this?
What lines of code need to be run in order for me to achieve this?

 

[Bru]

A simple way I did this was to use two query objects or sources (one table, one query).

The sql statement in the query where the record is specific will contain and expression. You can open a dialog box for the user to input the criteria (maybe use a drop down list or whatever) and reference the sql statement to the control in the box while it is still active. Or you just let access make the dialog box for you, but thats pretty hoaky.
The record(s) you need will be availabe in the query. Better yet, move the user defined criteria into a string (or WHY) variable and use some Ifisnull statement to decide wheather to put a filter on the recordset that the form is based on. Make the decision on On Open() Event.
This is somewhat simple, but I have found it to work.
Hope this helps.
Bru