Search code examples
vbams-accessjet

Auto-Answer UpdateQuery dialog Access07

I have an update query that is run via VBA everytime a form loads. Whenever the query is run it asks you if you want to update the records. Is there a way to automatically answer yes?

I forgot to mention that this is achieved via DoCmd.RunSQL with a where clause which looks like "UPDATE ItemList SET ItemList.Billed = 1 WHERE (((ItemList.ShipRef)=[Forms]![ItemList1]![SRCB]));"

Solution

  • The best solution: Use DB.Execute, e.g.

    Dim S As String

S = "UPDATE ItemList SET Billed = 1 WHERE ShipRef = " & [Forms]![ItemList1]![SRCB]
' or if ShipRef is Text:
S = "UPDATE ItemList SET Billed = 1 WHERE ShipRef = '" & [Forms]![ItemList1]![SRCB] & "'"

CurrentDb.Execute S

    This won't ask for confirmation.

    See Run Microsoft Access Action Queries in VBA and Hide Warnings without Using DoCmd.SetWarnings for information about DB.Execute vs. DoCmd.SetWarnings False and DoCmd.RunSQL.