467,883 Members | 1,261 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,883 developers. It's quick & easy.

DoCmd.SetWarnings WarningsOn/Off

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
  • viewed: 41694
Share:
6 Replies
Expert 256MB
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
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 256MB
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 2GB
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 2GB
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 16PB
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.

Similar topics

12 posts views Thread by Pradeep Varma | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.