How turn turn off warnings

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Darrell Childress - 27 Jun 2007 14:27 GMT

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

Reply to this Message

Douglas J. Steele - 27 Jun 2007 14:47 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>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
[quoted text clipped - 24 lines]
> Thanks much,
> Darrell

Reply to this Message

Klatuu - 27 Jun 2007 14:58 GMT

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.

Signature

Dave Hargis, Microsoft Access MVP

> 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
[quoted text clipped - 25 lines]
> Thanks much,
> Darrell

Reply to this Message

Tom Wannabe - 27 Jun 2007 15:10 GMT

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

I've had problems doing this

> There are two solutions to the problem. One is to use SetWarnings to turn
> them on and off.
[quoted text clipped - 49 lines]
>> Thanks much,
>> Darrell

Reply to this Message

Klatuu - 27 Jun 2007 15:44 GMT

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.

Signature

Dave Hargis, Microsoft Access MVP

> I don't think that this is reliable enough for real world usage
>
[quoted text clipped - 53 lines]
> >> Thanks much,
> >> Darrell

Reply to this Message

Darrell Childress - 02 Jul 2007 15:11 GMT

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

> 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.

Reply to this Message