Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-20-2018, 07:10 AM   #1
NT100
Newly Registered User
 
Join Date: Jul 2017
Posts: 106
Thanks: 7
Thanked 0 Times in 0 Posts
NT100 is on a distinguished road
SQL Update From Another Table With ACCESS VBA

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.

NT100 is offline   Reply With Quote
Old 02-20-2018, 07:15 AM   #2
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,666
Thanks: 10
Thanked 1,265 Times in 1,204 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
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
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 02-20-2018, 08:44 AM   #3
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,246
Thanks: 13
Thanked 1,381 Times in 1,316 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: SQL Update From Another Table With ACCESS VBA

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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 02-20-2018, 04:46 PM   #4
NT100
Newly Registered User
 
Join Date: Jul 2017
Posts: 106
Thanks: 7
Thanked 0 Times in 0 Posts
NT100 is on a distinguished road
Re: SQL Update From Another Table With ACCESS VBA

Quote:
Originally Posted by MarkK View Post
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 is offline   Reply With Quote
Old 02-20-2018, 09:10 PM   #5
NT100
Newly Registered User
 
Join Date: Jul 2017
Posts: 106
Thanks: 7
Thanked 0 Times in 0 Posts
NT100 is on a distinguished road
Re: SQL Update From Another Table With ACCESS VBA

Quote:
Originally Posted by NT100 View Post
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.

NT100 is offline   Reply With Quote
Reply

Tags
sql , update from table , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Access form to lookup sql table and update another table jtp607 Forms 0 07-24-2014 08:34 AM
How to update data from one table to another table using form. access 2010 prabhus Forms 1 12-16-2013 05:45 PM
Access VBA to update Access table from SQL server table source najorth Modules & VBA 4 06-19-2013 01:26 AM
Convert Measurement in Access VBA and update the same Access table WuJu Modules & VBA 3 12-01-2010 08:21 AM
possible to update a field in a SQL table from an MS Access table wmaxw Queries 2 09-12-2004 10:56 AM




All times are GMT -8. The time now is 06:38 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World