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:

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:

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?

Aug 27 '08 #1

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:

Expand|Select|Wrap|Line Numbers

1. DoCmd.SetWarnings WarningsOff
2. DoCmd.OpenQuery "Query Name"
3. 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:

Expand|Select|Wrap|Line Numbers

1. Option Compare Database
2. Private Sub cmdOK_Click()
3.     DoCmd.SetWarnings WarningsOff
4.     DoCmd.OpenQuery "*RS INIDP"
5.     DoCmd.SetWarnings WarningsOn
6.  
7.     Dim SufDate As String
8.     Dim Day As Integer
9.     Dim Month As Integer
10.     Dim Year As Integer
11.  
12.     Day = DatePart("d", (Date))
13.     Month = DatePart("m", (Date))
14.     Year = DatePart("yyyy", (Date))
15.     SufDate = "(" & Month & "-" & Day & "-" & Year & ")"
16.     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "qryQuarterlySales", "C:\QuarterlySales" & SufDate & ".xls", False
17.     MsgBox "The query has been exported to C:\QuarterlySales" & SufDate & ".xls", vbOKOnly
18.     Call Shell("excel.exe C:\QuarterlySales" & SufDate & ".xls", 1)
19.     DoCmd.Close acForm, "frmQuarterlySales"
20. End Sub
21.  

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:

Hope that helps,

Joe P.

Aug 27 '08 #2

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:

Expand|Select|Wrap|Line Numbers

1. Docmd.SetWarnings Off
2. Docmd.SetWarnings On
3.  

Hope that helps,

Joe P.

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

I will try this.

Aug 27 '08 #3

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.

Aug 27 '08 #4

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)>

Aug 27 '08 #5

nico5038

It's better to use:
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)

Aug 28 '08 #6

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?

Aug 30 '08 #7