Running Update Queries Seamlessly

[Guest]

I have created a command button that I want to use to activate 3 update
queries avoiding the messages that tell me how many records I am going to
update and the warning that it can not be undone. How do I turn this function
off?
My code is

Private Sub Command1_Click()
DoCmd.OpenQuery "qryzFloatA"
DoCmd.OpenQuery "qryzFloatR"
DoCmd.OpenQuery "qryzFloatG"
End Sub

Thanks,

Graeme.

 

[Guest]

Try this:

Private Sub Command1_Click()
Currentdb.execute "qryzFloatA", dbFailOnError
Currentdb.execute "qryzFloatR", dbFailOnError
Currentdb.execute "qryzFloatG", dbFailOnError
End Sub

Make sure to have a reference set to the DAO 3.6 Object Library if you
include the optional dbFailOnError parameter.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Nikos Yannacopoulos]

Graeme,

Add two lines of code like this:

Private Sub Command1_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryzFloatA"
DoCmd.OpenQuery "qryzFloatR"
DoCmd.OpenQuery "qryzFloatG"
DoCmd.SetWarnings True
End Sub

HTH,
Nikos

 

[Allen Browne]

Before the first DoCmd, add the line:
DoCmd.SetWarnings False
Don't forget to turn SetWarnings back on again afterwards.

The problem with that approach is that, once you turn SetWarnings off, you
get no message if something goes wrong and the action query does not
complete as expected. If you need to know whether the first one finished
successfully before you start the second, this approach is flawed.

The alternative is to Execute the queries instead. There is no need to turn
SetWarnings off, and if you use the dbFailOnError switch, you do get a
trappable error letting you know not to proceed:
Dim db As DAO.Database
Set db = CurrentDb()
db.Execute "qryzFloatA", dbFailOnError
db.Execute "qryzFloatR", dbFailOnError
db.Execute "qryzFloatG", dbFailOnError

The code does need adjusting if the queries contain parameters or refer to
controls on a form.

You also have the option to place all 3 inside a transaction so you can roll
the whole thing back if any one fails. More info and example in:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html


Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

 

[Rob Parker]

Application.SetOption "Confirm Action Queries", False
will turn off the confirmation, and
Application.SetOption "Confirm Action Queries", True
will turn it on again. So place those lines before and after your
DoCmd.OpenQuery statements.

There are also .SetOption methods for other options:
Application.SetOption "Confirm Record Changes", False
Application.SetOption "Confirm Document Deletions", False

Using the .SetOption methods may change a user's defaults. I use a couple
of functions called in the Load and Unload events of my startup form to save
the user's defaults to a table, set them to what I want while the
application is running, and restore the defaults when my application closes.
Here's the code, if you want to be fancier:

Public Sub SetOptions()
Dim strSQL As String
Dim blnConfirmChange As Boolean
Dim blnConfirmDelete As Boolean
Dim blnConfirmAction As Boolean
Dim strPrinter As String
On Error GoTo SetOptions_Error
blnConfirmChange = Application.GetOption("Confirm Record Changes")
blnConfirmDelete = Application.GetOption("Confirm Document Deletions")
blnConfirmAction = Application.GetOption("Confirm Action Queries")
strPrinter = Application.Printer.DeviceName
strSQL = "UPDATE tblSettings SET ConfirmChange = " _
& blnConfirmChange & ", ConfirmDelete = " & blnConfirmDelete _
& ", ConfirmAction = " & blnConfirmAction _
& ", Printer = '" & strPrinter & "';"
DoCmd.RunSQL strSQL
Application.SetOption "Confirm Record Changes", False
Application.SetOption "Confirm Document Deletions", False
Application.SetOption "Confirm Action Queries", False
Exit Sub
SetOptions_Error:
MsgBox "Error " & Err.Number & " - " & Err.Description & vbCrLf & _
"in procedure SetOptions" & vbCrLf & "of Module GeneralFunctions"
Resume ExitPoint
End Sub

Public Sub RestoreOptions()
On Error GoTo RestoreOptions_Error
Application.SetOption "Confirm Record Changes", DLookup("ConfirmChange",
"tblSettings")
Application.SetOption "Confirm Document Deletions",
DLookup("ConfirmDelete", "tblSettings")
Application.SetOption "Confirm Action Queries", DLookup("ConfirmAction",
"tblSettings")
Set Application.Printer = Application.Printers(DLookup("Printer",
"tblSettings"))
Exit Sub
RestoreOptions_Error:
MsgBox "Error " & Err.Number & " - " & Err.Description & vbCrLf & _
"in procedure RestoreOptions" & vbCrLf & "of Module GeneralFunctions"
End Sub

HTH,

Rob

 

[Guest]

Nikos,

If you are going to use the Set Warnings method, then you should definately
implement an error-handler, and make sure to turn warnings back on as a part
of exiting the procedure. Otherwise, if one of the update queries fails for
any reason, the warnings will not be turned back on. Something like this:

Private Sub Command1_Click()
On Error GoTo ProcError

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryzFloatA"
DoCmd.OpenQuery "qryzFloatR"
DoCmd.OpenQuery "qryzFloatG"

ExitProc:
DoCmd.SetWarnings True
Exit Sub
ProcError:
MsgBox "Your custom error message here" <---Optional
Resume ExitProc
End Sub



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________