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

[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!

 

[John W. Vinson]

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'

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]

 

[Mike]

Thank you so much!!!

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]