SQLRun - Silent update of field in another table? How?

T

Tec92407

I have the following code in an after_update event and it works fine.

strSQL = "UPDATE PONumbers " & _
"SET PONumbers.Used = True " & _
"WHERE PONumbers.PONumber = " & Me.PRPONumber

If Not IsNull(Me.PRPONumber) Then
DoCmd.RunSQL strSQL
End If

However, when the Docmd command runs, the user is prompted with a Yes/no
MsgBox to update the table. Is there a way I can do this silently so the
user insn't prompted?

Thanks
 
S

SusanV

Previous to your DoCmd.RunSQL line, put
DoCmd.SetWarnings False

After it, put:
DoCmd.SetWarnings True to turn the prompt back on.
 
K

Klatuu

There is another way and it executes faster:
If Not IsNull(Me.PRPONumber) Then
CurrentDb.Execute("strSQL"), dbFailOnError
End If

The Execute method does not go through the Access UI, so it is not seen be
the Warnings setting. That also makes it faster.
 
S

SusanV

Thanks Klatuu - I haven't gotten into that habit yet - but you're right it
is a much better method.

<note to self: db.Execute, not DoCmd.RunSQL>
;-D

SusanV
 
Top