DoCmd.SetWarnings WarningsOn/Off

[RZ15]

Hi,

I frequently have code in my forms that will turn warnings off, run make table queries, then turn warnings back on using the following code:

DoCmd.SetWarnings WarningsOff
DoCmd.OpenQuery "Query Name"
DoCmd.SetWarnings WarningsOn

The final line that turns the warnings back on does not seem to be working. Have I done something incorrect here? Here is a sample code for one of my buttons that use this:

Option Compare Database
Private Sub cmdOK_Click()
    DoCmd.SetWarnings WarningsOff
    DoCmd.OpenQuery "*RS INIDP"
    DoCmd.SetWarnings WarningsOn
 
    Dim SufDate As String
    Dim Day As Integer
    Dim Month As Integer
    Dim Year As Integer
 
    Day = DatePart("d", (Date))
    Month = DatePart("m", (Date))
    Year = DatePart("yyyy", (Date))
    SufDate = "(" & Month & "-" & Day & "-" & Year & ")"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "qryQuarterlySales", "C:\QuarterlySales" & SufDate & ".xls", False
    MsgBox "The query has been exported to C:\QuarterlySales" & SufDate & ".xls", vbOKOnly
    Call Shell("excel.exe C:\QuarterlySales" & SufDate & ".xls", 1)
    DoCmd.Close acForm, "frmQuarterlySales"
End Sub
 

This button runs a make table query (i need the data to be static because it runs too slow from the server) and then exports a query to excel.

So how can I properly turn the warnings back on?

[PianoMan64]

Hi,

I frequently have code in my forms that will turn warnings off, run make table queries, then turn warnings back on using the following code:

DoCmd.SetWarnings WarningsOff
DoCmd.OpenQuery "Query Name"
DoCmd.SetWarnings WarningsOn

The final line that turns the warnings back on does not seem to be working. Have I done something incorrect here? Here is a sample code for one of my buttons that use this:

Option Compare Database
Private Sub cmdOK_Click()
    DoCmd.SetWarnings WarningsOff
    DoCmd.OpenQuery "*RS INIDP"
    DoCmd.SetWarnings WarningsOn
 
    Dim SufDate As String
    Dim Day As Integer
    Dim Month As Integer
    Dim Year As Integer
 
    Day = DatePart("d", (Date))
    Month = DatePart("m", (Date))
    Year = DatePart("yyyy", (Date))
    SufDate = "(" & Month & "-" & Day & "-" & Year & ")"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "qryQuarterlySales", "C:\QuarterlySales" & SufDate & ".xls", False
    MsgBox "The query has been exported to C:\QuarterlySales" & SufDate & ".xls", vbOKOnly
    Call Shell("excel.exe C:\QuarterlySales" & SufDate & ".xls", 1)
    DoCmd.Close acForm, "frmQuarterlySales"
End Sub
 

This button runs a make table query (i need the data to be static because it runs too slow from the server) and then exports a query to excel.

So how can I properly turn the warnings back on?

The reason that it isn't coming back on is because your syntax for the Warnings off and Warnings On are incorrect.

The proper syntax is:

Docmd.SetWarnings Off
Docmd.SetWarnings On
 

Hope that helps,

Joe P.

[RZ15]

The reason that it isn't coming back on is because your syntax for the Warnings off and Warnings On are incorrect.

The proper syntax is:

Docmd.SetWarnings Off
Docmd.SetWarnings On
 

Hope that helps,

Joe P.

Weird that it works for Off and not On though...

I will try this.

[PianoMan64]

Weird that it works for Off and not On though...

I will try this.

that reason is both values are Zero. So they both mean false or off.

That is why.

Hope that helps,

Joe P.

[missinglinq]

And actually, the correct syntax, per Access, is

DoCmd.SetWarnings False

and

DoCmd.SetWarnings True

As you've found out, incorrect syntax will sometimes, accidentally, work, but not always!

Linq ;0)>

[nico5038]

It's better to use:

currentdb.execute (strSQL)
 

as this won't trigger a message and also won't turn off all the system messages as the DoCmd.SetWarnings will do after a failing query!

Nic;o)

[NeoPa]

Weird that it works for Off and not On though...

I will try this.

Boolean values (True; False; Yes; No) are naturally two state items, but in Access (VBA) they are stored in numeric variables.

The values used to represent True and False are -1 and 0 respectively (each bit in the value is set to reflect the value required).

As, in real life, these variables can store more than simply those two values, there is a convention on how to handle values that are neither. The convention is to treat any non-zero value as True (or True if ANY bit is True).

If you have variables that are not declared (shame - always use Option Explicit - but that's another story) On; Off; WarningsOn; WarningsOff; etc, then these will be initialised by the compiler to zero. Zero is equivalent to False, so all of these undefined variables would be equivalent to False, giving exactly the results you're experiencing.

Does that help?