SQL Update From Another Table With ACCESS VBA (1 Viewer)

NT100

Registered User.
Local time
Tomorrow, 01:18
Joined
Jul 29, 2017
Messages
148
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

bit cruncher
Local time
Today, 10:18
Joined
Mar 17, 2004
Messages
8,178
Yeah, subqueries need to be bracketted. Try...
Code:
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:18
Joined
Feb 19, 2002
Messages
42,971
This query doesn't make sense. Do you really want to update every row of tblTutor with the same value?

When you are not familiar with Access SQL, use the QBE to build the query. You can then switch views to get the SQL string if you really must embed the string. OR, you can save the querydef and use that (more efficient). The QBE doesn't support subselects visually except in the latest builds of A2016.
 

NT100

Registered User.
Local time
Tomorrow, 01:18
Joined
Jul 29, 2017
Messages
148
Yeah, subqueries need to be bracketted. Try...
Code:
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

Registered User.
Local time
Tomorrow, 01:18
Joined
Jul 29, 2017
Messages
148
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.
 

Users who are viewing this thread

Top Bottom