SET WARNING TO OFF VBA

[lwarren1968]

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]

paste into a module.

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

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

 

[arnelgp]

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]

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]

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]

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]

The other option is to run the query using

CurrentDb.Execute "mktqry_MakeNewTable", dbSeeChanges

Which avoids the warning messages problem completely.

 

[GPGeorge]

Spoiler: yada yada

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.