setting controls for running queries using macros

frank_guess
frank_guess used Ask the Experts™
on
I am wanting to run multiple queries one right after the other using the maro language but how do I code so the queries run without having to answer yes or no to each one.  Also how do I show a spining globe or a completion bar as each one starts and completes.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
use setwarnings = false
Most Valuable Expert 2012
Top Expert 2014
Commented:
I'm not sure you can do this with macros, but it's simple enough with VBA:

1) Add a button to a form, and then add code like this to the Click event:

Sub MyButton_Click()
  Screen.MousePointer = 11 'hourglass
  Currentdb.Execute "FirstQueryName"
  Currentdb.Execute "Next QueryName"
  etc etc
  Screen.Mousepointer = 1 'default
End Sub

You can also use

DoCmd.HourGlass True

to show the wait cursor and

DoCmd.HourGlass False

to hide the wait cursor
in addition to setwarnings, use hourglass to change icon when busy.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Distinguished Expert 2017

Commented:
It can't be overemphasized that you should use a visual clue that you have turned off warnings (that's what the hourglass is doing).  When warnings are off, Access will not prompt you to ask if you want to save your changes when you close an object.  It will just silently discard the changes.  You only have to be burned once by Access discarding hours of work because you forgot to specifically save at a time when you had warnings off.

Author

Commented:
Thank you for the information but I still have a question to my first statement.  I am using Access 2010 and I cannot find anything within the macro tool that allows me to either docmd or just to setwarnings off or setwarnings = false.

use setwarnings = false

I wonder if I am going to have to convert the macro to code and place this behind a button to execute the entire list of queries that I am running using the macro.
Looks to me like they have removed parts of a good tool.  Looks like I might have to build a function that does the same as setwarnings = false
Is there anyone that can give me a few pointers.
Distinguished Expert 2017
Commented:
This is the only thing I actually use macros for.  I have two.  One that turns the hourglass on and warnings off and the second reverses the settings.  So, I know for a fact the options are available in a macro.  It is possible that you need to change the security setting or whatever it is called so you can see the missing options.

I much prefer to do these things in VBA.  It is always easier to customize VBA if you have to just run "3" of the queries this time.  It is also easier to document and read.  If you want a one stop shop and you don't want to create a form with a button to run the code, write the VBA as a function rather than a sub and create a macro to run the function.

Author

Commented:
I will look for the different methods and build the program.  You have each gave me some great ideals.
Thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial