SET WARNING TO OFF VBA (1 Viewer)

lwarren1968

Registered User.
Local time
Today, 08:23
Joined
Jan 18, 2013
Messages
77
I created a macro to turn off warnings (see below) and it works for me, however when someone else accesses the same data base they still receive the warnings. Please help.

Private Sub cmdRunMakeTable_Click()
DoCmd.Hourglass True
Turns off the Access warning messages
DoCmd.SetWarnings False
DoCmd.OpenQuery “mktqry_MakeNewTable”
DoCmd.Hourglass False
Turns the Access warning messages back on
DoCmd.SetWarnings True
End Sub
 

Ranman256

Well-known member
Local time
Today, 11:23
Joined
Apr 9, 2015
Messages
4,339
paste into a module.

simplified, just run:
HrGlass true
DoCmd.OpenQuery “mktqry_MakeNewTable”
HrGlass false



Code:
Public Sub HrGlass(Optional ByVal pbOn As Boolean = True)
DoCmd.Hourglass pbOn
DoCmd.SetWarnings Not pbOn
DoEvents
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:23
Joined
May 7, 2009
Messages
19,175
it's a make table? is the new table created in the BE? you need to check first if the table (an empty table) is
already created, therefore preventing duplication of same process.
 

lwarren1968

Registered User.
Local time
Today, 08:23
Joined
Jan 18, 2013
Messages
77
it's a make table? is the new table created in the BE? you need to check first if the table (an empty table) is
already created, therefore preventing duplication of same process.
no, append.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:23
Joined
May 7, 2009
Messages
19,175
create a semaphore table (with field "Locked" Yes/No).
add 1 record to this table (Locked=False).
to prevent deadlock, two users doing same append,
you need to check this table first if the Locked is False.
if False, set it to True and proceed with appending record.
when done, reset it to false.
if the value of the field is True, you need to to show
a msg that someone else is currently appending records and cancel
the request. retrying until it get false and you can append record.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:23
Joined
Feb 19, 2002
Messages
42,986
The only two macros I use in every database are mWarningsOn and mWarningsOff. The first turns hourglass off and warnings on and the second does the reverse. I use macros rather than functions because they are easy to run if I am stepping through code. and end up with the warnings off because I stopped the code before the second macro ran. Also, since there are rarely any macros except these two, they are always easy to find and I don't have to remember what module my function is in.

The Hourglass is a visual clue. You reallllllllllllllllllllllllly don't want to forget to turn warnings back on. Forgetting once will usually leave a permanent memory marker and you won't do it a second time depending on how much of your work got lost. But just in case you're like me - the hourglass being on is sufficiently annoying to jog your memory.
 

Minty

AWF VIP
Local time
Today, 15:23
Joined
Jul 26, 2013
Messages
10,355
The other option is to run the query using

Code:
CurrentDb.Execute "mktqry_MakeNewTable", dbSeeChanges

Which avoids the warning messages problem completely.
 

GPGeorge

Grover Park George
Local time
Today, 08:23
Joined
Nov 25, 2004
Messages
1,776
I created a macro to turn off warnings (see below) and it works for me, however when someone else accesses the same data base they still receive the warnings. Please help.

Private Sub cmdRunMakeTable_Click()
DoCmd.Hourglass True
Turns off the Access warning messages
DoCmd.SetWarnings False
DoCmd.OpenQuery “mktqry_MakeNewTable”
DoCmd.Hourglass False
Turns the Access warning messages back on
DoCmd.SetWarnings True
End Sub
Being a natural born nit-picking pedant, I have to point out that this is NOT a macro in Access. It's a VBA Sub. All other Office applications (Word, Excel, etc.) are limited to a single coding language which they interchangeably refer to as both macros and VBA. Access is blessed with two distinct coding languages, VBA and macros.
 

Users who are viewing this thread

Top Bottom