By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,871 Members | 2,337 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,871 IT Pros & Developers. It's quick & easy.

DoCmd.SetWarnings WarningsOn/Off

P: 53
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?
Aug 27 '08 #1
Share this Question
Share on Google+
6 Replies


Expert 100+
P: 374
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:

Expand|Select|Wrap|Line Numbers
  1. Docmd.SetWarnings Off
  2. Docmd.SetWarnings On
  3.  
Hope that helps,

Joe P.
Aug 27 '08 #2

P: 53
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

Expert 100+
P: 374
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
Expert 2.5K+
P: 3,532
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
Expert 2.5K+
P: 3,072
It's better to use:
Expand|Select|Wrap|Line Numbers
  1. currentdb.execute (strSQL)
  2.  
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
Expert Mod 15k+
P: 31,489
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

Post your reply

Sign in to post your reply or Sign up for a free account.