Solved

setting controls for running queries using macros

Posted on 2014-03-06
7
324 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

786 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