SQL Update From Another Table With ACCESS VBA

[NT100]

I'm working a ACCESS VBA to update a table column from another table with a condition.

sSQL = "UPDATE tblTutor SET tblTutor.FirstAppt = SELECT ApptDtStart FROM tblProcessTNewAppt WHERE tblProcessTNewAppt.ID = " & !ID
CurrentDb.Execute sSQL

However, I got the syntax error msg of "Run-time error '3075':
Syntax error in query expression 'SELECT ApptDtStart FROM tblProcessTNewAppt'."

The following command displays in the immediate windows.
UPDATE tblTutor SET tblTutor.FirstAppt = SELECT ApptDtStart FROM tblProcessTNewAppt WHERE tblProcessTNewAppt.ID = 303.

Obviously, it's syntax error. Does anybody have suggestion on this with vba code.

Thank you for sharing.

 

[MarkK]

Yeah, subqueries need to be bracketted. Try...

sSQL = _
   "UPDATE tblTutor " & _
   "SET tblTutor.FirstAppt = " & _
      "( " & _
      "SELECT ApptDtStart " & _
      "FROM tblProcessTNewAppt " & _
      "WHERE tblProcessTNewAppt.ID = " & !ID & " " & _
      ")"

See what's going on there? Also, the indent style is optional, but I think it makes your intent clearer.
hth
Mark

 

[NT100]

Yeah, subqueries need to be bracketted. Try...

sSQL = _
   "UPDATE tblTutor " & _
   "SET tblTutor.FirstAppt = " & _
      "( " & _
      "SELECT ApptDtStart " & _
      "FROM tblProcessTNewAppt " & _
      "WHERE tblProcessTNewAppt.ID = " & !ID & " " & _
      ")"

See what's going on there? Also, the indent style is optional, but I think it makes your intent clearer.
hth
Mark

With the construct above, it runs into the following error

Run-time error '3073'
Operation must use an updateable query

Any further suggestions

Thanks in advance.

 

[NT100]

With the construct above, it runs into the following error

Run-time error '3073'
Operation must use an updateable query

Any further suggestions

Thanks in advance.

I surrender. I use inner join instead. It works perfectly.

Thank you.