docmd.setwarning false and true

[aspfun via AccessMonster.com]

In order to run a few command, I used code below:

docmd. setwarning false
do something....
docmd.setwarning true

But, warning message never display again.
How to make "docmd.setwarning true" working?

 

[Alex Dybenko]

Hi,
are you getting warnings before running this code? Check Access options,
perhaps you have Confirm action queries option switched off

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[John Spencer]

DoCmd.SetWarnings True --- Note the "s" on SetWarnings.

Do you have error handling on your code? If you don't an an error occurs
before the line
DoCmd.SetWarnings True
the line won't execute. It is usually a good idea to trap errors and in the
error handling routine execute the statement
DoCmd.SetWarnings True

Something like the following

Public Sub sDoStuff()
On Error GoTo Proc_Error

DoCmd.SetWarnings False
'Do stuff here
DoCmd.SetWarnings True

Exit Sub

Proc_Error:
MsgBox Err.Number & ": " & Err.Description
DoCmd.SetWarnings True
End Sub


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Tony Toews [MVP]]

In order to run a few command, I used code below:

docmd. setwarning false
do something....
docmd.setwarning true

Are your commands SQL action queries?

The problem with DoCmd.RunSQL is that it ignores any errors. Either
of the following will display any error messages received by the
query. If using DAO, use Currentdb.Execute strSQL,dbfailonerror..
For ADO use CurrentProject.Connection.Execute strCommand,
lngRecordsAffected, adCmdText You can then remove the
docmd.setwarnings lines.

If you're going to use docmd.setwarnings make very sure you put the
True statement in any error handling code as well. Otherwise weird
things may happen later on especially while you are working on the
app. For example you will no longer get the "Do you wish to save your
changes" message if you close an object. This may mean that unwanted
changes, deletions or additions will be saved to your MDB.

Also performance can be significantly different between the two
methods. One posting stated currentdb.execute took two seconds while
docmd.runsql took eight seconds. As always YMMV.

Tony

 

[David W. Fenton]

The problem with DoCmd.RunSQL is that it ignores any errors.

I have posted many times my SQLRun code, which can be used as a
drop-in replacement for DoCmd.RunSQL. Put the code after my
signature in a public module and then search and replace to change
DoCmd.RunSQL to SQLRun.

SQLRun also returns the count of records affected, should you decide
to use it as a function instead of as a sub.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Public Function SQLRun(strSQL As String, _
Optional db As DAO.Database, _
Optional lngRecordsAffected As Long) As Long
On Error GoTo errHandler

If db Is Nothing Then Set db = dbLocal
DBEngine.Workspaces(0).BeginTrans
db.Execute strSQL, dbFailOnError
lngRecordsAffected = db.RecordsAffected
DBEngine.Workspaces(0).CommitTrans

exitRoutine:
SQLRun = lngRecordsAffected
Exit Function

errHandler:
MsgBox "There was an error executing your SQL string: " _
& vbCrLf & vbCrLf & Err.Number & ": " & Err.Description, _
vbExclamation, "Error in SQLRun()"
Debug.Print "SQL Error: " & strSQL
DBEngine.Workspaces(0).Rollback
Resume exitRoutine
End Function

 

[vanderghast]

That would do as long as the SQL statement does not use
FORMS!formName!ControlName syntax for a parameter, which is automatically
solved, with DoCmd, but NOT, with CurrentDb.Execute. Sure, sure, there are
possible workarounds, but the 'beauty' of DoCmd, in that case, is that you
don't need to fight with special delimiters, none are required since you
supply the container, not the constant itself, neither have to worry if the
value is null, or, as string, has already the delimiter in it, etc. Again,
those are not unpassible obstacles, though, just that it start to become
quite something to do what DoCmd already do, in that case, for a 'general'
syntax replacement.


Vanderghast, Access MVP

I have posted many times my SQLRun code, which can be used as a
drop-in replacement for DoCmd.RunSQL. Put the code after my
signature in a public module and then search and replace to change
DoCmd.RunSQL to SQLRun.

(...)

 

[David W. Fenton]

That would do as long as the SQL statement does not use
FORMS!formName!ControlName syntax for a parameter, which is
automatically solved, with DoCmd, but NOT, with CurrentDb.Execute.
Sure, sure, there are possible workarounds, but the 'beauty' of
DoCmd, in that case, is that you don't need to fight with special
delimiters, none are required since you supply the container, not
the constant itself, neither have to worry if the value is null,
or, as string, has already the delimiter in it, etc. Again, those
are not unpassible obstacles, though, just that it start to become
quite something to do what DoCmd already do, in that case, for a
'general' syntax replacement.

I don't see these as problems but as features. That is, it forces
you to pass SQL that has already been resolved to the values in the
controls. Sure, it does mean that some search-and-replace operations
will not work, but all that means is that you have to resolve the
control reference and pass *that* instead of the hardwired control
reference.

Something like this:

DoCmd.RunSQL "DELETE * FROM MyTable WHERE MyTable.ID=MyForm!ID;"

would simply become:

SQLRun "DELETE * FROM MyTable WHERE MyTable.ID=" & MyForm!ID

Not all that difficult to fix, though, of course, one has to know
about using the proper delimiters. That topic is, in fact, one that
I remember making my eyes glaze over until I understood it. One of
the things that has always helped me a lot is using a global
constant with the double quote in it so I don't have to litter these
string concatenation operations with """ and """" and such.