VBA/Access: Removing warning prompts

0
votes

I've built a Macro that runs 7 queries. The macro works exactly like I want, but because they are update and append queries Access prompts the user to approve of the updates and appendages. I would like to use VBA to turn off the warning messages so that the macro runs from beginning to end without interruption.

I converted the macro to VBA and the code is pretty straightforward. There are 7 DoCmd.OpenQuery commands followed by a "macro name" Exit: and "macro name" Err:. I added this code before the first DoCmd.OpenQuery line

DoCmd.SetWarnings False

Application.DisplayAlerts=False

Then at the end of the code

DoCmd.SetWarnings True

Application.DisplayAlerts=True

This doesn't turn of the messages though when I try it. Any idea what VBA code I can use to turn off all the update, and append query warnings?

edit: My VBA is

Option Compare Database

Function Spec_Maker()
On Error GoTo Spec_Maker_Err
    DoCmd.SetWarnings False
    Application.DisplayAlerts=False
    DoCmd.OpenQuery "Query1", acViewNormal, acEdit
    DoCmd.OpenQuery "Query2", acViewNormal, acEdit
    DoCmd.OpenQuery "Query3", acViewNormal, acEdit
    DoCmd.OpenQuery "Query4", acViewNormal, acEdit
    DoCmd.OpenQuery "Query5", acViewNormal, acEdit
    DoCmd.OpenQuery "Query6", acViewNormal, acEdit
    DoCmd.OpenQuery "Query7", acViewNormal, acEdit
    DoCmd.SetWarnings True
    Application.DisplayAlerts=True

Spec_Maker_Exit:
    Exit Function

spec_Maker_Err:
    MsgBox Error$
    Resume Spec_Maker_Exit

End Function
2
excelms-accessvbams-access-2010
Can you post your entire code block? – mwolfe02
Access doesn't have an Application.DisplayAlerts property; that's an Excel thing. If you're actually asking about Excel VBA code which interacts with an Access database, please clarify that point. Thanks. – HansUp
I'm in Access. The Application.DisplayAlerts was just bad information I found elsewhere that I thought would work – Carl
In that case, add Option Explicit to the Declarations section of your Access code module. Then run Debug->Compile from the VB Editor's main menu. Fix anything the compiler complains about. – HansUp

2 Answers

2
votes

SetWarnings should generally be avoided, since it turns off warnings globally, and you must be very careful to turn them back on, even in the case of errors.

An elegant way to get rid of the warnings is to not use querys to make data changes. Instead, copy the SQL of your INSERT or UPDATE statement to VBA and execute it with CurrentDb.Execute:

CurrentDb.Execute "INSERT INTO ...", dbFailOnError

See also:

2
votes

You have 7 saved queries, Query1 thru Query7, which you want to execute in order. So just execute them without turning SetWarnings off. Heinzi already explained why you should not turn SetWarnings off.

Function Spec_Maker()
    Dim i As Long

On Error GoTo spec_Maker_Err

    For i = 1 To 7
        CurrentDb.Execute "Query" & i, dbFailOnError
    Next

Spec_Maker_Exit:
    Exit Function

spec_Maker_Err:
    MsgBox Error$
    Resume Spec_Maker_Exit

End Function

Always include Option Explicit in the Declarations sections of all your code modules. And run Debug->Compile from the VB Editor's main menu. Fix anything the compiler complains about before proceeding with your troubleshooting efforts. Had you done that, Access would have alerted you "Method or data member not found" at Application.DisplayAlerts because that property is not recognized in Access.