Disable Access Update Records warning

[kbremner]

Is there a way to disable the MSAccess Update Records warning box in
certain instances? I have a button that performs an update query on
the record set. It first sets a field in all of the records to a
specific response and then copies the data from another source into
the record set. This will prompt the user twice that they are making
changes. I am afraid this is going to confuse users. I want to either
eliminate these warnings or replace them with my own.

 

[John W. Vinson]

Is there a way to disable the MSAccess Update Records warning box in
certain instances? I have a button that performs an update query on
the record set. It first sets a field in all of the records to a
specific response and then copies the data from another source into
the record set. This will prompt the user twice that they are making
changes. I am afraid this is going to confuse users. I want to either
eliminate these warnings or replace them with my own.

There are two ways. One is to use

DoCmd.SetWarnings False

before the line running the query; if you do so you *MUST BE SURE* to put

DoCmd.SetWarnings True

after the query execution line, or you'll turn off all other warning messages
for the rest of the Access session.

Better is to use the Execute method, which allows you to trap and correctly
respond to errors, and doesn't pop up the automatic warning messages:

Dim db As DAO.Database
On Error GoTo Proc_Error
<code preparing for the query to run>
Set db = CurrentDb
db.Execute "YourQueryNameHere", dbFailOnError
<the rest of your code>
Proc_Exit:
Exit Sub
Proc_Error:
If Err.Number = <some specific error>
<handle that error appropriately>
Else
MsgBox "Error " & Err.Number & " in running the update query" _
& vbCrLf & Err.Description
End If
Resume Proc_Exit
End Sub

It looks like a lot of code, but the On Error line and the error handling
block from Proc_Exit to the end (or code like it) should be included in *ALL*
your VBA code.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[David-W-Fenton]

It looks like a lot of code

If you want a simple replacement for DoCmd.RunQuery without writing
new code, see my SQLRun() function after my signature. It's been in
production use in my apps for several years now, and I use it
instead of writing an error handler each time I need to use .Execute
to run DML SQL.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

Public Function SQLRun(strSQL As String, Optional db As Database, _
Optional lngRecordsAffected As Long) As Long
On Error GoTo errHandler
Dim bolCleanup As Boolean

If db Is Nothing Then
Set db = CurrentDb
bolCleanup = True
End If
db.Execute strSQL, dbFailOnError
lngRecordsAffected = db.RecordsAffected

exitRoutine:
If bolCleanup Then
Set db = Nothing
End If
SQLRun = lngRecordsAffected
'Debug.Print strSQL
Exit Function

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