Selectively running macros/queries

[John P]

My database consists of a few tables, numerous queries and one macro to run all queries. Each month I import the most recent month data and run the macro. In my inefficient world, the macro runs against all months, instead of just the most recent, so it can take up to 15 minutes to complete.

I would prefer that when I select to run my macro, it allows me to select the reporting month (this is a field on my main table that is updated with the queries) or reporting months I wish to have the macro run against. Is this possible without a major overhaul to the existing DB? Any suggestions are appreciated. Thanks

 

[Joe4]

Put a paramater on the month field in your Criteria for that field.

For example, open your query in Design View. In the month field, under criteria enter:
[Enter your month]

Now any time you open this query, it will prompt you to enter the month and will limit your query to those results. If you run the macro off of this query, it will prompt you for the month every time.

See Access help for "Create a parameter query" for more details, if necessary.

 

[John P]

If several of my queries do not currently contain reporting month as part of the query, can I simply add reporting month to the query and click do not show? Also, if I have 30 queries, won't the application prompt me to enter this value 30 times? Lastly, if I set warnings to off, will I still be prompted?

 

[Joe4]

You don't need to add it to all your queries, just the ones where you want to filter out all unnecessary months. It sounds like this may be a very big and involved job you got running there.

Can you connect and link a bunch of queries together to limit the number that your macro accesses when it runs? A bettter solution may be creating a user form to enter selection criteria and then writing VBA code to create SQL statements to do what your macro is accomplishing. As you can imagine, that is it a bit more complicated (especially if you haven't written much VBA code before).