Use a Query's Result inside an UPDATE Statement (VBA)

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hi. In VBA I'm executing an UPDATE statement via DoCmd.RunSQL. I need the
WHERE clause of this UPDATE command to get a value from a separate query
named 'qryGetTimeOut'

It looks like this:

DoCmd.RunSQL ("UPDATE EmployeeLog SET TimeOut = 1 WHERE ID =(Execute query
qryGetTimeOut).

How do I do this? Thanks!
 
Hi. In VBA I'm executing an UPDATE statement via DoCmd.RunSQL. I need the
WHERE clause of this UPDATE command to get a value from a separate query
named 'qryGetTimeOut'
Click to expand...

DoCmd.RunSQL ("UPDATE EmployeeLog SET TimeOut = 1 WHERE ID IN (SELECT ID FROM
qryGetTimeOut))".

or, depending on the structure of the query, the likely much faster and more
efficient

DoCmd.RunSQL ("UPDATE EmployeeLog INNER JOIN qryGetTimeOut ON qryGetTimeOut.ID
= EmployeeLog.ID SET EmployeeLog.TimeOut = 1;"


If qryGetTimeOut is a Totals query neither one of these may work - post back
with its SQL if it doesn't.

John W. Vinson [MVP]
 
Thank you so much!!!

John W. Vinson said:
DoCmd.RunSQL ("UPDATE EmployeeLog SET TimeOut = 1 WHERE ID IN (SELECT ID FROM
qryGetTimeOut))".

or, depending on the structure of the query, the likely much faster and more
efficient

DoCmd.RunSQL ("UPDATE EmployeeLog INNER JOIN qryGetTimeOut ON qryGetTimeOut.ID
= EmployeeLog.ID SET EmployeeLog.TimeOut = 1;"


If qryGetTimeOut is a Totals query neither one of these may work - post back
with its SQL if it doesn't.

John W. Vinson [MVP]
Click to expand...
 
Back
Top