Possible to suppress Access's updating message by delete?

  • Thread starter Thread starter Rolf Rosenquist
  • Start date Start date
R

Rolf Rosenquist

Still working with my datalist - subform. When the user wants to delete a
row, I can ask him if that is OK in the Form_Delete event. If so, I put the
number of parts back into the stock table and the row is deleted. No
problems with that.

But when Access runs the following code in Form_After_DelConfirm:

If Status = acDeleteOK Then
LagerAntal = DLookup("Antal", "Produkter", "[ProdID] = " &
ProduktNr)
LagerAntal = LagerAntal + AntalIorder
sqlText = "UPDATE produkter SET [antal]=" & [LagerAntal] & " WHERE
[ProdID] = " & ProduktNr & ";"
DoCmd.RunSQL sqlText
End If

.... there comes another message that says, if I translate to english,
something like *You are updating 1 row(s)* and there is OK and Cancel
buttons. If the user answers OK everything works fine. But if he hits the
Cancel, there will be an error message that says that the RunSQL was
interrupted and points to the last row above. No matter if I run this code
in After_DelConfirm or Before_DelConfirm.

Is there a way to suppress this message? That would solve my problem, I
think.
If so, I would also be able to suppress the sam message when the user adds a
new row in the datalist. I think they are annoying and demanding too much
inputs from the user.

/ Rolf
 
I believe what you are looking for is under the Tools menu. Look in the
Options menu under the Edit/Find tab. On the right hand side, you can uncheck
to confirm changes... I think the one for Action Queries is going to be the
one you'll want to uncheck. It would keep a message from coming up on an
update/delete query.

HTH
Emma
 
Thanks Emma, I didn't know of that.
But it works only on deletes, not when adding posts. Is there also another
one?
/ Rolf


Emmweb said:
I believe what you are looking for is under the Tools menu. Look in the
Options menu under the Edit/Find tab. On the right hand side, you can uncheck
to confirm changes... I think the one for Action Queries is going to be the
one you'll want to uncheck. It would keep a message from coming up on an
update/delete query.

HTH
Emma

Rolf Rosenquist said:
Still working with my datalist - subform. When the user wants to delete a
row, I can ask him if that is OK in the Form_Delete event. If so, I put the
number of parts back into the stock table and the row is deleted. No
problems with that.

But when Access runs the following code in Form_After_DelConfirm:

If Status = acDeleteOK Then
LagerAntal = DLookup("Antal", "Produkter", "[ProdID] = " &
ProduktNr)
LagerAntal = LagerAntal + AntalIorder
sqlText = "UPDATE produkter SET [antal]=" & [LagerAntal] & " WHERE
[ProdID] = " & ProduktNr & ";"
DoCmd.RunSQL sqlText
End If

.... there comes another message that says, if I translate to english,
something like *You are updating 1 row(s)* and there is OK and Cancel
buttons. If the user answers OK everything works fine. But if he hits the
Cancel, there will be an error message that says that the RunSQL was
interrupted and points to the last row above. No matter if I run this code
in After_DelConfirm or Before_DelConfirm.

Is there a way to suppress this message? That would solve my problem, I
think.
If so, I would also be able to suppress the sam message when the user adds a
new row in the datalist. I think they are annoying and demanding too much
inputs from the user.

/ Rolf
Click to expand...
Click to expand...
 
You need to trap the error message in the form's BeforeDelConfirm event:

Private Sub Form_BeforeDelConfirm(Response As Integer)
Response = acDataErrContinue
End Sub
 
I have already done that. And when I don't, I get another message first that
says that I am going to delete 1 row.
I wanted to suppress the message that says I am about to update a row. That
is the same message as I get when I change a field in a post or even add a
post.

/ Rolf


Ken Snell said:
You need to trap the error message in the form's BeforeDelConfirm event:

Private Sub Form_BeforeDelConfirm(Response As Integer)
Response = acDataErrContinue
End Sub


--

Ken Snell
<MS ACCESS MVP>

Rolf Rosenquist said:
Still working with my datalist - subform. When the user wants to delete a
row, I can ask him if that is OK in the Form_Delete event. If so, I put the
number of parts back into the stock table and the row is deleted. No
problems with that.

But when Access runs the following code in Form_After_DelConfirm:

If Status = acDeleteOK Then
LagerAntal = DLookup("Antal", "Produkter", "[ProdID] = " &
ProduktNr)
LagerAntal = LagerAntal + AntalIorder
sqlText = "UPDATE produkter SET [antal]=" & [LagerAntal] & " WHERE
[ProdID] = " & ProduktNr & ";"
DoCmd.RunSQL sqlText
End If

... there comes another message that says, if I translate to english,
something like *You are updating 1 row(s)* and there is OK and Cancel
buttons. If the user answers OK everything works fine. But if he hits the
Cancel, there will be an error message that says that the RunSQL was
interrupted and points to the last row above. No matter if I run this code
in After_DelConfirm or Before_DelConfirm.

Is there a way to suppress this message? That would solve my problem, I
think.
If so, I would also be able to suppress the sam message when the user
Click to expand...
adds
a
new row in the datalist. I think they are annoying and demanding too much
inputs from the user.

/ Rolf
Click to expand...
Click to expand...
 
My apology. I saw your post about deleting.

You need to turn off the warning dialog before you run the query and then
turn it back on when the query is done. Use the DoCmd.SetWarnings action to
do this:

If Status = acDeleteOK Then
LagerAntal = DLookup("Antal", "Produkter", "[ProdID] = " &
ProduktNr)
LagerAntal = LagerAntal + AntalIorder
sqlText = "UPDATE produkter SET [antal]=" & [LagerAntal] & " WHERE
[ProdID] = " & ProduktNr & ";"
DoCmd.SetWarnings False
DoCmd.RunSQL sqlText
DoCmd.SetWarnings True
End If

Also, you can bypass this entirely if you use the CurrentDb.Execute method
instead:

If Status = acDeleteOK Then
LagerAntal = DLookup("Antal", "Produkter", "[ProdID] = " &
ProduktNr)
LagerAntal = LagerAntal + AntalIorder
sqlText = "UPDATE produkter SET [antal]=" & [LagerAntal] & " WHERE
[ProdID] = " & ProduktNr & ";"
CurrentDb.Execute sqlText, dbFailOnError
End If

--

Ken Snell
<MS ACCESS MVP>

Rolf Rosenquist said:
I have already done that. And when I don't, I get another message first that
says that I am going to delete 1 row.
I wanted to suppress the message that says I am about to update a row. That
is the same message as I get when I change a field in a post or even add a
post.

/ Rolf


Ken Snell said:
You need to trap the error message in the form's BeforeDelConfirm event:

Private Sub Form_BeforeDelConfirm(Response As Integer)
Response = acDataErrContinue
End Sub
Click to expand...
delete
a
row, I can ask him if that is OK in the Form_Delete event. If so, I
Click to expand...
put
the
number of parts back into the stock table and the row is deleted. No
problems with that.

But when Access runs the following code in Form_After_DelConfirm:

If Status = acDeleteOK Then
LagerAntal = DLookup("Antal", "Produkter", "[ProdID] = " &
ProduktNr)
LagerAntal = LagerAntal + AntalIorder
sqlText = "UPDATE produkter SET [antal]=" & [LagerAntal] & " WHERE
[ProdID] = " & ProduktNr & ";"
DoCmd.RunSQL sqlText
End If

... there comes another message that says, if I translate to english,
something like *You are updating 1 row(s)* and there is OK and Cancel
buttons. If the user answers OK everything works fine. But if he hits the
Cancel, there will be an error message that says that the RunSQL was
interrupted and points to the last row above. No matter if I run this code
in After_DelConfirm or Before_DelConfirm.

Is there a way to suppress this message? That would solve my problem, I
think.
If so, I would also be able to suppress the sam message when the user
Click to expand...
adds
a
new row in the datalist. I think they are annoying and demanding too much
inputs from the user.

/ Rolf
Click to expand...
Click to expand...
Click to expand...
 
That was the exact solution I searched for. It solved both my problems.
Thank you very much!

/ Rolf



Ken Snell said:
My apology. I saw your post about deleting.

You need to turn off the warning dialog before you run the query and then
turn it back on when the query is done. Use the DoCmd.SetWarnings action to
do this:

If Status = acDeleteOK Then
LagerAntal = DLookup("Antal", "Produkter", "[ProdID] = " &
ProduktNr)
LagerAntal = LagerAntal + AntalIorder
sqlText = "UPDATE produkter SET [antal]=" & [LagerAntal] & " WHERE
[ProdID] = " & ProduktNr & ";"
DoCmd.SetWarnings False
DoCmd.RunSQL sqlText
DoCmd.SetWarnings True
End If

Also, you can bypass this entirely if you use the CurrentDb.Execute method
instead:

If Status = acDeleteOK Then
LagerAntal = DLookup("Antal", "Produkter", "[ProdID] = " &
ProduktNr)
LagerAntal = LagerAntal + AntalIorder
sqlText = "UPDATE produkter SET [antal]=" & [LagerAntal] & " WHERE
[ProdID] = " & ProduktNr & ";"
CurrentDb.Execute sqlText, dbFailOnError
End If

--

Ken Snell
<MS ACCESS MVP>

Rolf Rosenquist said:
I have already done that. And when I don't, I get another message first that
says that I am going to delete 1 row.
I wanted to suppress the message that says I am about to update a row. That
is the same message as I get when I change a field in a post or even add a
post.

/ Rolf


Ken Snell said:
You need to trap the error message in the form's BeforeDelConfirm event:

Private Sub Form_BeforeDelConfirm(Response As Integer)
Response = acDataErrContinue
End Sub


--

Ken Snell
<MS ACCESS MVP>

Still working with my datalist - subform. When the user wants to
Click to expand...
delete
a
row, I can ask him if that is OK in the Form_Delete event. If so, I put
the
number of parts back into the stock table and the row is deleted. No
problems with that.

But when Access runs the following code in Form_After_DelConfirm:

If Status = acDeleteOK Then
LagerAntal = DLookup("Antal", "Produkter", "[ProdID] = " &
ProduktNr)
LagerAntal = LagerAntal + AntalIorder
sqlText = "UPDATE produkter SET [antal]=" & [LagerAntal] & " WHERE
[ProdID] = " & ProduktNr & ";"
DoCmd.RunSQL sqlText
End If

... there comes another message that says, if I translate to english,
something like *You are updating 1 row(s)* and there is OK and Cancel
buttons. If the user answers OK everything works fine. But if he
Click to expand...
hits
the
Cancel, there will be an error message that says that the RunSQL was
interrupted and points to the last row above. No matter if I run
Click to expand...
this
code
in After_DelConfirm or Before_DelConfirm.

Is there a way to suppress this message? That would solve my
Click to expand...
Click to expand...
problem,
I user
adds
Click to expand...
Click to expand...
 
Back
Top