Access - Run 2 make table queries without warning messages

I have two Make table queries that I want to run from One button - I want to NOT have the warning messages about modivying data in the table, deleting the existing table and pasting a new row in the table.  The tables just hold some data for another query and can be replaced each time the the make table queries are run...

VB code would be fine - or I can use a macro - I want to run it from a button on a form.

These are the two Make table queries that I want to run without the warning messages:

GL Daily Cash Date Jrnl Number Seq MTQ
CM Daly Cash Date TranType Number Seq MTQ
cbishopcgrAsked:
Who is Participating?
 
redeux-techConnect With a Mentor Commented:
If you want to create a VBA function that you call from the Button code behind here is the solution.

Function RunMakeTableQueries(qry1 As String, qry2 As String)


'This turns off the warnings.
DoCmd.SetWarnings False

'Now run your queries.
DoCmd.OpenQuery qry1
DoCmd.OpenQuery qry2

'This turns the warnings back on.
DoCmd.SetWarnings True


End Function
0
 
Rey Obrero (Capricorn1)Commented:
docmd.setwarnings false

docmd.openquery "GL Daily Cash Date Jrnl Number Seq MTQ"
docmd.openquery "CM Daly Cash Date TranType Number Seq MTQ "

docmd.setwarnings true
0
 
PatHartmanCommented:
This is the only place in my applications where I use a macro.  Create two macros.  One to turn the warnings off and the second to turn them back on.  In the macro, also turn the hourglass on when you turn warnings Off and off when you turn warnings back on.  I do this so I always have a visual clue that warnings are off.  Leaving warnings off when you are making design changes is extremely dangerous since if you simply close a modified object without saving it, Access will not prompt you to save (no warnings).  It silently discards your changes.  So the hourglass is a self-preservation tool.  I know if the hourglass is on, that warnings are off.  Then the code would be:

DoCmd.RunMacro "mWarningsOff"
DoCmd.OpenQuery "GL Daily Cash Date Jrnl Number Seq MTQ"
DoCmd.OpenQuery "CM Daly Cash Date TranType Number Seq MTQ"
DoCmd.RunMacro "mWarningsOn"

Open in new window

0
 
cbishopcgrAuthor Commented:
Appreciate the quick response - worked perfectly.  Thank you.
0
 
Dale FyeCommented:
Personally, I prefer the Execute method to OpenQuery, for the following reasons:

1.  No need to SetWarnings False/True.  When you use:

 DoCmd.SetWarnings False

You must ensure that you place your

DoCmd.SetWarnings True

statement in a position which ensures that if any errors are encountered, the error handling will include that statement.  Otherwise, you could get yourself in a bind where you think you should get a warning and don't.

2.  By using the Execute method, with the dbFailOnError argument,

currentdb.Execute strSQL, dbFailOnError

or

currentdb.querydefs("qryYourQueryName").execute dbfailonerror

you can run the query and not worry about the warnings, unless an error is encountered.  You can then use an error handler in your procedure to make any adjustments, rollback the queries, or present a specific error message to the user.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.