Automating Access 2007 Queries (changing one criteria)

Question

So, I have 6 queries and I want to run them all once at the end of each month. (I know a bit about SQL but they're simply built using Access's design view). So, in the next few days, perhaps I'll run the 6 queries for May, as May just ended. I only want the data from the month that just ended, so the query has Criteria set as the name of the month (e.g., May). Now, it's not hugely time consuming to change all of these each month, but is there some way to automate this? Currently, they're all set to April and I want to change them all to May when I run them in a few days. And each month, I'd like to type the month (perhaps in a textbox in a form or somewhere else if you know a better way) just once and have it change all 6 queries, without having to manually open all 6, scroll over to the right field and change the Criteria.

Note (about VBA): I have used Excel VBA so I know the basics of VBA but I don't really know anything specific to Access (other than seeing code a few times). And, others will use this who do not know anything about Access VBA. So, I think I have found a similar question/answer that could do this in VBA, but I'd rather do it some other way. If the query needs to be slightly redesigned later, probably by someone who doesn't know Access VBA at all, it'd be nice to have a solution not involving VBA if that is even possible.

Answer 1

There's a simple way to have the query ask for the month when you run it: In the query, put [Enter Month] in the same place you now have the month name.

When the query runs, a dialog box will appear to enter the month. You can put any text you want in the square brackets - it doesn't have to say "month"; this simply defines what the dialog box will say. This technique is called a "parameter query".

Running all 6 queries with a single step will require VBA code - but very simple code. Your end users don't need to know anything about VBA - they only need to open a form and click a button.

Create a form, put one button on it. In the properties list for that button, choose the Events tab and then the "On Click" event. Click the "..." button for the On Click event.

This opens the VBA code window with the cursor in the correct place - Private Sub Command0_Click - where Command0 is the name of the button. Enter code as shown...

Substitute the correct names for your queries and repeat the line of code as many times as needed. Opening the form and clicking the button will now run all the queries at once.

The Next Step

The final improvement would be to enter the parameter once and have it apply to all the queries automatically. This requires several steps.

In the VBA code window, create a code "Module". Right click in the Project pane of the window and choose Insert/Module:

In the new module, add this code:

gstrMonth is a global variable string to hold the name of the month. The function GetMonth will be used in the queries to retrieve that value.

Now change the Form1 code to this:

The sub "AskForMonth" displays a dialog box for the user to type in a Month name. That input is assigned to the gstrMonth variable.

In the Command0 (button) code we add AskForMonth before the query is run. Again, repeat the "DoCmd" line for each query with the correct query names.

The final step is to replace the parameter in the queries with a call to the function "GetMonth". Instead of [Enter Month] in the query criteria, put "GetMonth()".

I hope this encourages you to use some of the capabilities of VBA to automate your Access tasks!