Using 1 set of criteria for many queries

[FrankRomero]

I would like to set up a form (or a table) that contains one set of criteria that I can draw from as criteria for various queries. This data will need to be changed on a daily basis.

I know this can be done, but I am going crazy trying to figure it out.

Thanks for any help.

Frank

 

[vbaInet]

I'm guessing you're talking about a facility for FILTERING your form? You select a value from a combo, enter an ID in a text box and it finds the records and displays it on the form?

 

[FrankRomero]

No. I have a database that is linked to ODBC tables and I do some monthly (and sometimes, daily) reconciliations of the data. I have set up some parameter filters for many of my queries, but find myself entering the same criteria over and over. I would prefer to enter it once in one place and have the queries grab the criteria from there.

For example, I would like to have a form (or table) is updated daily that contains criteria such as: Report Date, Funding Date, Expense Account Nbr, etc.

I know what I want, but am not sure I'm articulating it correctly. thanks for asking.

 

[vbaInet]

So go ahead and create the table. Have another field that is a YES/NO field. Enter as many records/filter data that you want. You would then tick only ONE filter (using the YES/NO) field and from that you know that you can code it to run that ticked record for that day.

 

[FrankRomero]

So, then what would my criteria expression look like? I am almost getting a picture in my head of what you are trying to tell me, but I am still having trouble. Thanks again for your help.

 

[vbaInet]

Your table could be laid out like:

ID    |    Query Criteria    |     RunMe (Yes/No field)
-------------------------------------------------
1.         "Criteria 1"                    No
2.         "Criteria 2"                    No
3.         "Criteria 3"                    Yes

So you SELECT the query criteria with a YES.

 

[FrankRomero]

So, then, if I have a table with [dtmBegDate] [dtmEndDate] [strFY] [strPD] and [ysnRunMe] as my fields, how would my code look for FY 2010, PD 11? Thanks again for all of your help.

 

[vbaInet]

If you don't want to use you can simply create a subform that draws from this table the YES record. Make the subform hidden. Since the controls are bound to this table, feed your query from the subform's controls values.

So in the criteria row for your query you reference the subform controls.

 

[FrankRomero]

Wow. I'd be happy with either the table or the form or a subform or anything, but I think you assume I know WAY more about Access than I really do. You're speaking a language I barely understand.

Can you just give me an example of what I put in my query criteria row to do this and I will try to understand it from there. Thanks!

 

[gfultz]

Ok. Here is what you do...

1. for each query with the same criteria, you will reference a form to supply the parameter. For example, you have two queries, one of which updates based on project number and the other on project number and date. your form would have two fields, project number and date.

2. You populate these values on the form and setup a run button. This run button runs the two queries, only they don't require any input from you since the form is already populated with the data they need. To program the run button, you can either create a macro and use the command button wizard or you could do it with VBA.

What vbainet was talking about with hidden forms is a way to run multiple criteria to different forms in the background. I use this quite frequently when creating report launcher interfaces where people will query the same field by their choice of combo box (IE Project number, Project Name, Accounting code all relate to the same project). Although he is different in that he is feeding a hidden subform whilst I am opening up a new hidden form.

***For the query criteria you would put [Forms]![Yourform]![YourFormField]

 

[FrankRomero]

Thank you both so much! I have been able to use this to run my queries the way I was hoping to. Now I need more projects to practice this with. I am quite new to using forms.

Thanks.

 

[vbaInet]

Glad you got the hang of it

Happy to have had some input.