coding on the form to select a specific query

[ChrisB37]

I am needing help with the code to select different queries based on different conditions.

The database is to be set up so that I can pull studies at different time points based on if they are under long term, accelerated or stressed conditions. When this condition is selected there are only certain time points which are to be run,
example.....
Long term: 3,6,9,12,24,36,48 and 60 months.
Accelerated: 3,6,9 and 12 months.

As you can see I have the dates calculated for all of the weeks and months that the sample could be pulled at, Inventory Transactions Table. And I am able to generate throught the Pull Dates Form a list of only the records to be pulled under Long Term conditions. I would like to have the form set up so that when I choose the Accelerated condition that it would pull from another Query like the Pull Dates Long Term, but only have the Accelerated time points listed.

My code to select the query is like this.

Private Sub Combo0_Click()
DoCmd.OpenQuery "Pull Dates Query Long Term"
End Sub

Can I add to it to select a different query with accelerated time points when that field is selected?


Also, while I have your attention. Is there a way to limit which sample dates are calculated, maybe by specifying criteria in the Inventory Transactions Pull Dates Query?
Example.......
The samples either have a monthly or weekly pull date, as shown in the Inventory Transaction table. The issue is that for Long Term and Accelerated conditions I have monthly pull dates and for Stressed conditions I only have weekly pull dates. I have no need to calculate pull dates by month for the studies that are completed on a weekly basis. I would like to use the combo box , Samplling Interval, in the Inventory Transaction table to be able to choose by month or week and the corresponding pull dates calculated for the particular conditions.
If you can help with this also that would be great, If not I can get by on what I have created so far.

Thanks

 

[WayneRyan]

Chris,

You can make your selection(s) to an unbound combo-box(es). Use the
AfterUpdate event to:

Me.RecordSource = "YourQuery"

If a combo contains a list of queries:

Me.RecordSource = Me.YourComboBox


You can also have the query use form items in the Criteria Section:

=Forms![YourForm]![YourField]

Then you won't have to replicate queries just to use different dates.

Wayne

 

[ChrisB37]

I am not sure I understand. I am new to programming and am not familiar with the codes you listed.

How will I distinguish between the different queries?

Do I choose Me.RecordSource = "Query 1" Me.RecordSource = "Query 2" and then tell it Me.RecordSource = Me.YourComboBox and it will select the query based on the choice selected in the combo box?

Any help in setting this up would be appreciated.

Thanks,
C

 

[WayneRyan]

Chris,

If you have some number of queries, you can fill your combobox with:

Select Name
From   MSysObjects
Where  Type = 5
Order By Name

Then, using the AfterUpdate event of the combo:

Me.RecordSource Me.YourCombo
Me.ReQuery

Wayne