1

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.

1 Answer 1

2

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.

enter image description here

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".

enter image description here

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.

enter image description here

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...

enter image description here

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:

enter image description here

In the new module, add this code:

enter image description here

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:

enter image description here

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

enter image description here

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()".

enter image description here

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

6
  • Dave, thanks for the detailed answer. Your last paragraph talks about the "final step". This is what I really need help with. Based on the link I provided in my question, I know I could change the criteria by putting the entire SQL statement in my code and putting a variable in there, probably getting its value from a textbox on a form. My question is, essentially, is there an easier way (easier than using the SQL in VBA) to do this "final step". Is there some sort of setting like Queries("Query1").Field("Month").Criteria = "May"?
    – GeoffDS
    Jun 4, 2012 at 14:35
  • Yes, there is an easier way. You don't need to put the SQL in the VBA code. I will edit my answer to show you how. Jun 4, 2012 at 17:51
  • @Graphth - Is this the answer you were looking for? Jun 6, 2012 at 13:54
  • Perhaps, will have to wait until I have a chance to try it out.
    – GeoffDS
    Jun 6, 2012 at 17:30
  • @Graphth - Did this work for you? Jun 18, 2012 at 15:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .