docmd.RunSQL Update records in table how can I get rid of the "You are about to update" message

[RC]

In my Access 2002 form, I have a combo box and on the AfterUpdate
event I use

DoCmd.RunSQL ("UPDATE ....
to update records in a table. When it starts to run I get a message
"You are about to update 3 row(s)."
Is there a way to prevent the message from popping up?

[Allen Browne]

You can turn off SetWarnings, and turn it on again later.

Alternatively, Execute your action query like this:
dbEngine(0)(0).Execute "UPDATE ...

That gives you no warning. If you want to stop if there is an error, use:
dbEngine(0)(0).Execute "UPDATE ... ", dbFailOnError
And if you want to be able to roll back the entire thing if there is an
error, use a transaction. Example:
http://members.iinet.net.au/~allenbrowne/ser-37.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"RC" <rc*********@yahoo.com> wrote in message
news:3c**************************@posting.google.c om...

In my Access 2002 form, I have a combo box and on the AfterUpdate
event I use

DoCmd.RunSQL ("UPDATE ....
to update records in a table. When it starts to run I get a message
"You are about to update 3 row(s)."
Is there a way to prevent the message from popping up?

[RC]

Thank you very much Mr. Brown! Not only did you answer my specific
question but you taught me about Transactions which I didn't know
anything about. I will set up the Update so that if there is an
error, the user can roll-back the changes and try again or
proceed/accept the changes with the error and check the database to
make sure the changes are OK.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<41***********************@per-qv1-newsreader-01.iinet.net.au>...

You can turn off SetWarnings, and turn it on again later.

Alternatively, Execute your action query like this:
dbEngine(0)(0).Execute "UPDATE ...

That gives you no warning. If you want to stop if there is an error, use:
dbEngine(0)(0).Execute "UPDATE ... ", dbFailOnError
And if you want to be able to roll back the entire thing if there is an
error, use a transaction. Example:
http://members.iinet.net.au/~allenbrowne/ser-37.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"RC" <rc*********@yahoo.com> wrote in message
news:3c**************************@posting.google.c om...

In my Access 2002 form, I have a combo box and on the AfterUpdate
event I use

DoCmd.RunSQL ("UPDATE ....
to update records in a table. When it starts to run I get a message
"You are about to update 3 row(s)."
Is there a way to prevent the message from popping up?

[RC]

I could not get the dbEngine(0)(0) thing to work.

The following code works:
Private Sub PalletNumberContainerFormComboBox_AfterUpdate()
DoCmd.RunSQL ("UPDATE Products " & _
"SET Products.ContainerNumberProductsTable =
[Forms]![ContainerAssociationForm]![GETContainerNumber] " & _
"WHERE
(([Products.PalletNumberProductsTable]=[Forms]![ContainerAssociationForm]![PalletNumberContainerFormComboBox]));")
End Sub

But when I try using dbEngine like this:

Private Sub PalletNumberContainerFormComboBox_AfterUpdate()
DBEngine(0)(0).Execute ("UPDATE Products SET
Products.ContainerNumberProductsTable =
[Forms]![ContainerAssociationForm]![GETContainerNumber] WHERE
([Products.PalletNumberProductsTable]=[Forms]![ContainerAssociationForm]![PalletNumberContainerFormComboBox]);"),
dbFailOnError
End Sub

I get the error message: "Too few parameters, expected two." I tried
various changes but I could not get it to work.
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<41***********************@per-qv1-newsreader-01.iinet.net.au>...

You can turn off SetWarnings, and turn it on again later.

Alternatively, Execute your action query like this:
dbEngine(0)(0).Execute "UPDATE ...

That gives you no warning. If you want to stop if there is an error, use:
dbEngine(0)(0).Execute "UPDATE ... ", dbFailOnError
And if you want to be able to roll back the entire thing if there is an
error, use a transaction. Example:
http://members.iinet.net.au/~allenbrowne/ser-37.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"RC" <rc*********@yahoo.com> wrote in message
news:3c**************************@posting.google.c om...

In my Access 2002 form, I have a combo box and on the AfterUpdate
event I use

DoCmd.RunSQL ("UPDATE ....
to update records in a table. When it starts to run I get a message
"You are about to update 3 row(s)."
Is there a way to prevent the message from popping up?

[Shane Suebsahakarn]

Change your SQL to read as follows:

DBEngine(0)(0).Execute ("UPDATE Products SET
Products.ContainerNumberProductsTable =" &
[Forms]![ContainerAssociationForm]![GETContainerNumber] & " WHERE
[Products.PalletNumberProductsTable]=" &
[Forms]![ContainerAssociationForm]![PalletNumberContainerFormComboBox],dbFai
lOnError

Since the queries retrieve parameters from forms, you'll need to make those
values part of the SQL string (well, you don't have to, but it's much easier
to do it this way). In the above, I'm assuming both fields are numeric data
types. If they aren't, you'll need to add text delimiters around the
parameters.

--
Shane Suebsahakarn
----
Head of IT
PAN Telecom
Tel: [phone]

"RC" <rc*********@yahoo.com> wrote in message
news:3c**************************@posting.google.c om...

I could not get the dbEngine(0)(0) thing to work.

The following code works:
Private Sub PalletNumberContainerFormComboBox_AfterUpdate()
DoCmd.RunSQL ("UPDATE Products " & _
"SET Products.ContainerNumberProductsTable =
[Forms]![ContainerAssociationForm]![GETContainerNumber] " & _
"WHERE
(([Products.PalletNumberProductsTable]=[Forms]![ContainerAssociationForm]![P
alletNumberContainerFormComboBox]));") End Sub

But when I try using dbEngine like this:

Private Sub PalletNumberContainerFormComboBox_AfterUpdate()
DBEngine(0)(0).Execute ("UPDATE Products SET
Products.ContainerNumberProductsTable =
[Forms]![ContainerAssociationForm]![GETContainerNumber] WHERE
([Products.PalletNumberProductsTable]=[Forms]![ContainerAssociationForm]![Pa
lletNumberContainerFormComboBox]);"), dbFailOnError
End Sub

I get the error message: "Too few parameters, expected two." I tried
various changes but I could not get it to work.
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message

news:<41***********************@per-qv1-newsreader-01.iinet.net.au>...

You can turn off SetWarnings, and turn it on again later.

Alternatively, Execute your action query like this:
dbEngine(0)(0).Execute "UPDATE ...

That gives you no warning. If you want to stop if there is an error, use: dbEngine(0)(0).Execute "UPDATE ... ", dbFailOnError
And if you want to be able to roll back the entire thing if there is an
error, use a transaction. Example:
http://members.iinet.net.au/~allenbrowne/ser-37.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"RC" <rc*********@yahoo.com> wrote in message
news:3c**************************@posting.google.c om...

In my Access 2002 form, I have a combo box and on the AfterUpdate
event I use

DoCmd.RunSQL ("UPDATE ....
to update records in a table. When it starts to run I get a message
"You are about to update 3 row(s)."
Is there a way to prevent the message from popping up?

[Trevor Best]

RC wrote:

I could not get the dbEngine(0)(0) thing to work.

The following code works:
Private Sub PalletNumberContainerFormComboBox_AfterUpdate()
DoCmd.RunSQL ("UPDATE Products " & _
"SET Products.ContainerNumberProductsTable =
[Forms]![ContainerAssociationForm]![GETContainerNumber] " & _
"WHERE
(([Products.PalletNumberProductsTable]=[Forms]![ContainerAssociationForm]![PalletNumberContainerFormComboBox]));")
End Sub

But when I try using dbEngine like this:

Private Sub PalletNumberContainerFormComboBox_AfterUpdate()
DBEngine(0)(0).Execute ("UPDATE Products SET
Products.ContainerNumberProductsTable =
[Forms]![ContainerAssociationForm]![GETContainerNumber] WHERE
([Products.PalletNumberProductsTable]=[Forms]![ContainerAssociationForm]![PalletNumberContainerFormComboBox]);"),
dbFailOnError
End Sub

I get the error message: "Too few parameters, expected two." I tried
various changes but I could not get it to work.

You need to parse the form objects yourself, DAO knows nothing of them

DBEngine(0)(0).Execute "UPDATE Products SET " & _
"Products.ContainerNumberProductsTable = " & _
[Forms]![ContainerAssociationForm]![GETContainerNumber] & _
"WHERE [Products.PalletNumberProductsTable]=" & _

[Forms]![ContainerAssociationForm]![PalletNumberContainerFormComboBox],
dbFailOnError
--

\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\

[Allen Browne]

"Trevor Best" wrote:

\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\

Trevor, you should be torched for that sig! :-)

The way Windows interprets the backslashes is brilliant. Lights on, but
nobody's home.

[RC]

Thank you very much, all of you and Thank you to Allen Browne for
teaching me about Transactions from your website at:
http://members.iinet.net.au/~allenbrowne/ser-37.html

I didn't know anything about Transactions and now I am going to write
that into any applicable areas to prevent any database catastrophes.

I tried various adjustments to the code samples provided and kept
getting syntax errors. The code below (with different table and form
names) works for me.

DBEngine(0)(0).Execute "UPDATE Table1 SET " &
"[Table1]![ContainerNumber] = " & [Forms]![Form1]![Text3] & " WHERE
[Table1]![PalletNumber]= " & [Forms]![Form1]![GetPalletNumberBox],
dbFailOnError

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<41***********************@per-qv1-newsreader-01.iinet.net.au>...

"Trevor Best" wrote:

\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\

Trevor, you should be torched for that sig! :-)

The way Windows interprets the backslashes is brilliant. Lights on, but
nobody's home.

[Enzo's Dad]

Try something like...

DoCmd.SetWarnings = False
DoCmd.RunSQL ("SQLSTRING")
DoCmd.SetWarnings = True

"RC" <rc*********@yahoo.com> wrote in message
news:3c**************************@posting.google.c om...

In my Access 2002 form, I have a combo box and on the AfterUpdate
event I use

DoCmd.RunSQL ("UPDATE ....
to update records in a table. When it starts to run I get a message
"You are about to update 3 row(s)."
Is there a way to prevent the message from popping up?