Solved

setting controls for running queries using macros

Posted on 2014-03-06
7
325 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 35

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 35

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

840 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