How turn turn off warnings

[Darrell Childress]

I have a form which contains a button to run an update query. When
clicking the button, it warns of running an update query to which one
must respond "Yes". Then it informs you that it is going to update x
number of records to which one must again respond "Yes". I have used
macros in the past to turn off and then back on these warnings, but
lately I have tried to write the code instead of macros. What would be
the code for this?
For reference, this is what I have right now:

Private Sub UpdateMPS_Button_Click()
On Error GoTo Err_UpdateMPS_Button_Click

Dim stDocName As String

stDocName = "qryTotalHours_WorkCenters_Update"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Me.Requery

Exit_UpdateMPS_Button_Click:
Exit Sub

Err_UpdateMPS_Button_Click:
MsgBox Err.Description
Resume Exit_UpdateMPS_Button_Click

End Sub

Thanks much,
Darrell

 

[Douglas J. Steele]

Private Sub UpdateMPS_Button_Click()
On Error GoTo Err_UpdateMPS_Button_Click

Dim qdfUpdate As DAO.QueryDef
Dim stDocName As String

stDocName = "qryTotalHours_WorkCenters_Update"
Set qdfUpdate = CurrentDb.QueryDefs(stDocName)
qdfUpdate.Execute dbFailOnError
Me.Requery

Exit_UpdateMPS_Button_Click:
Exit Sub

Err_UpdateMPS_Button_Click:
MsgBox Err.Description
Resume Exit_UpdateMPS_Button_Click

End Sub

This assumes you've already got a reference set to DAO. (By default, Access
2000 and 2002 don't). Go to Tools | References while in the VB Editor and
check. If you don't, scroll through the list of available references until
you find the one for Microsoft DAO 3.6 Object Library, select it, then close
the dialog.

 

[Guest]

There are two solutions to the problem. One is to use SetWarnings to turn
them on and off.

To turn them off:
Docmd.SetWarnings False
To turn them on:
Docmd.SetWarnings True

But, for action queries, there is a better method. It is much faster
because it does not go through the Access UI, it goes directly to Jet and
therefore, does not trigger the messages. Replace these lines:

Dim stDocName As String

stDocName = "qryTotalHours_WorkCenters_Update"
DoCmd.OpenQuery stDocName, acNormal, acEdit

With:

CurrentDb.Execute("qryTotalHours_WorkCenters_Update"), dbFailOnError

Note, the dbFailOnError option is important. If you do not include it and
an error occurs, you will not get an error message.

 

[Tom Wannabe]

I don't think that this is reliable enough for real world usage

I've had problems doing this

 

[Guest]

Which are you objecting to?
If it is the Execute method, your fears are unfounded. It is a much better
and faster solution than any of the others.

 

[Darrell Childress]

I tried the first solution and it works great. I think I will try the
2nd solution and see how that works.
Thanks for the help,
Darrell