0

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]));"

Rynoc
  • 45
  • 1
  • 1
  • 9

1 Answers1

1

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.

Andre
  • 26,751
  • 7
  • 36
  • 80
  • 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]));"` Can this be achieved using `CurrentDB.Execute` ? – Rynoc Sep 03 '15 at 14:13
  • Yes - see edit. It may also work with your original SQL string, i.e. with the parameter *inside* the SQL instead of appended in VBA. Just try it out. – Andre Sep 03 '15 at 14:25
  • Please do not use single letter variables. You will end up with serious problems. – Fionnuala Sep 03 '15 at 14:34
  • 1
    @Fionnuala: Excuse me? I have used single letter variables like S, i, x, y for 20+ years in VBA, and never have had any problems. – Andre Sep 03 '15 at 14:54
  • You must have a very disciplined mind and no common code. I have been answering questions for 20 years and single letter variables are a problem for beginners. – Fionnuala Sep 03 '15 at 15:30
  • 1
    @Fionnuala: Obviously variables like these are *local* variables. – Andre Sep 03 '15 at 15:35