Best Way To Have One Form Referencing Multiple Queries (1 Viewer)

[AJR]

Hi

Just getting the feel of Access but definitely not highly proficient. I'm also starting to learn VBA.

I have a form that I want to filter by different fields on different occasions. i.e., I might look at all records that have a review date of earlier than today on one occasion and I might look at all records that have a Yes in a Yes/No box on another occasion (date then being irrelevant.)

I have finished the form which has a query as it's Record Source.

The only way I can figure out how to accomplish my goal is to copy/paste the form with a new name, copy/paste the query that is it's record source, with a new name, and then put the criteria I want in the new query and make that query the new form's record source.

This not only seems a little cumbersome but will also be time consuming.

Is there a more elegant way I can do this. For example. I have a form consisting of buttons that I use as a Navigation Form. Could I, in the VBA that opens the form when I click on it's button, stipulate which Record Source the form would use?

Thanks for any help you may be able to provide.

A/R

 

[AJR]

Hello Again

Funny thing is that the idea of putting the record source in a VBA procedure just came to me while I was writing. A quick Google revealed that I can in fact set the record source, using VBA, when I open the form from the Nav Form.

Any other ideas would be appreciated but I think this solves it.

A/R

 

[AJR]

Hi

Just in case someone else is trying to do the same thing I thought I should include a more detailed explanation of how I solved this.

1. For each different way I wish to view the form copy and paste the Query used as the form's record source, giving it a new name
2. Apply the criteria to the new query
3. On my custom Nav Form, create a button and use the wizard to have the button open the form.
4. On the Design tab of the ribbon click "Convert form's Macros to Visual Basic" to convert the Macro I just created to VBA
5. Go to the Nav Forms design view and open the Event Procedure for the On Click Event of the button I just created.
6 Alter the line --DoCmd.OpenForm "frmToDo", acNormal, "", "", , acNormal to include the name of the query I wish to use for this event, between the first set of "" quotes.

Works

 

[Mihail]

Too much to read for my poorly English.
But I can ask to first question "Best Way To Have One Form Referencing Multiple Queries":
Bound your form to a query that reference multiple (other) queries.