How do I get rid of... "You are about to update 1 row(s)"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a button run SQL to update a small local table that holds user
settings. Every time it runs though, it gives me the Yes/No dialogue:

"You are about to update 1 row(s)" "Once you click Yes, you can't use the
Undo command to reverse the changes. Are you sure you want to update these
records?"

Is there any way to bypass this message? Or is there another way to update
a table without SQL, even when the form you're in has nothing to do with the
table that holds the settings?

Thanks!

Nick
 
In the event where you runt the SQL (I assume it is a command button), just
before you run the SQL:
DoCmd.SetWarnings False

Then, after you have run it:
DoCmd.SetWarnings True

(In my applications, I always turn SetWarnings off and never turn them back
on. They seem only to confuse and scare users)
 
Tatakau said:
I have a button run SQL to update a small local table that holds user
settings. Every time it runs though, it gives me the Yes/No dialogue:

"You are about to update 1 row(s)" "Once you click Yes, you can't use
the Undo command to reverse the changes. Are you sure you want to
update these records?"

Is there any way to bypass this message? Or is there another way to
update a table without SQL, even when the form you're in has nothing
to do with the table that holds the settings?

Thanks!

Nick
Click to expand...

' This turns off the warnings
DoCmd.SetWarnings False

DoCmd.RunSQL Your SQL here

'This turns it back on and be sure you do otherwise it will turn off
everywhere.
DoCmd.SetWarnings True
 
Hi, Nick.
Is there any way to bypass this message?
Click to expand...

The most effective way to avoid the unnecessary message is to use the
Execute method of the Database Object, with the dbFailOnError parameter so
that if the operation fails, the user will be alerted. The problems with
using SetWarnings is that if the operation fails, the user doesn't have a
clue that the update never occurred, and the error handler must also set
SetWarnings back to true, which occasionally gets bypassed/forgotten by the
developer, so the system warnings are turned off for all other operations --
until the application is either closed or some code is executed by chance to
turn them back on.

Here's an example:

Private Sub UpdateTblBtn_Click()

On Error GoTo ErrHandler

CurrentDb().Execute "UPDATE TABLE MyTable SET Done = 0;", dbFailOnError

Exit Sub

ErrHandler:

MsgBox "Error in UpdateTblBtn_Click( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub
Or is there another way to update
a table without SQL, even when the form you're in has nothing to do with the
table that holds the settings?
Click to expand...

Use SQL. (You could also create a query for this, but it's straightforward
with SQL in the VBA code, so go with the simpler method.)

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
If you want to remove the messages for all queries, without using the set
warnings, then in the menu bar select
Tools > Options > Edit/Search (Tab) > Confirm Action Queries (remove the
selection)

But you have to know that it will never prompt with the message event when
you are using the queries dirrectly
 
Back
Top