Solved

setting controls for running queries using macros

Posted on 2014-03-06
7
326 Views
Last Modified: 2014-03-06
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.
0
Comment
Question by:frank_guess
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 7

Assisted Solution

by:COACHMAN99
COACHMAN99 earned 167 total points
ID: 39909883
use setwarnings = false
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 166 total points
ID: 39909907
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
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39909956
in addition to setwarnings, use hourglass to change icon when busy.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 36

Expert Comment

by:PatHartman
ID: 39910745
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.
0
 

Author Comment

by:frank_guess
ID: 39910767
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.
0
 
LVL 36

Accepted Solution

by:
PatHartman earned 167 total points
ID: 39910865
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.
0
 

Author Closing Comment

by:frank_guess
ID: 39910916
I will look for the different methods and build the program.  You have each gave me some great ideals.
Thank you
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question