Suppress Update Confirm

[Abby N]

I'm trying to create a script that appends or updates a table when a report is opened. I've written a script and tied it into the report's 'On Open' event. It works fine except for one thing. When the script goes to update the table I get a popup message that says, "You won't be able to undo the changes this action query is about to make to data in a linked table or tables. Do you wish to continue? {Yes}{No}." I'd like this to run without any dialog. The 'append' portion works fine, no dialog. Anyone know why the 'update' portion still gives me a dialog, and more importantly, how to suppress it? Thank you.

Application.SetOption "Confirm Action Queries", 0
If dtCurrentDate > dtMaxDate Then
DoCmd.OpenQuery "AppendHistoryFile", acViewNormal, acEdit
ElseIf dtCurrentDate <= dtMaxDate Then
DoCmd.OpenQuery "UpdateHistoryFile", acViewNormal, acEdit
End If
Application.SetOption "Confirm Action Queries", -1

~Abby

 

[Travis]

instead of using 'DoCmd.OpenQuery' use 'Currentdb.Execute'

Currentdb.Execute "QueryName/or SQL String"

This will only work for Action Queries.

Hope this helps

 

[Abby N]

That did it. Thank you Travis. Though I still don't understand why the dialog came up with the Update query and not the Append query.

 

[chrismcbride]

You can suppress Access messages with...
DoCmd.SetWarnings False

But you want to watch this carefully as all the system warning (including those that you yourself might find useful) until you use..
DoCmd.SetWarnings True

I have no idea why you wouldn't get warnings about both procedures. If you ever find out, I'd like to know
See Ya
Chris

 

[accesswatch]

Reason for the message
Basically the Update query is warning you that it will be changing data and that if you go ahead you will not be able to recover from these changes.
The Append query does not need to warn you because you are not changing any existing records i.e the Apend query is a recoverable situation whereas the Update query is not. Makes sense when you think about it.

Hope this helps
Trevor from www.accesswatch.co.uk