UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> SQL Update From Another Table With VBA, Access 2016    
 
   
NT100
post Feb 20 2018, 09:53 AM
Post#1



Posts: 16
Joined: 11-January 18



Hi,
I'm working a VBA to update a table column from another table with a condition as follows:-

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

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

I notice in the immediate windows the following command. The following command is what I need.
UPDATE tblTutor SET tblTutor.FirstAppt = SELECT ApptDtStart FROM tblProcessTNewAppt WHERE tblProcessTNewAppt.ID = 303

Would you share your experience on this.
Go to the top of the page
 
theDBguy
post Feb 20 2018, 10:47 AM
Post#2


Access Wiki and Forums Moderator
Posts: 73,245
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

You could try changing your code to make the query something like this:

UPDATE tblTutor
INNER JOIN tblProcessTNewAppt
ON tblTutor.ID=tblProcessTNewAppt.ID
SET tblTutor.FirstAppt=tblProcessTNewAppt.ApptDtStart

However, I think you're trying to store redundant data in multiple tables. You should be able to grab the information from both table using a query.

Just my 2 cents...

PS. The above query could probably use an ORDER BY clause to get the first date from the other table.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
NT100
post Feb 28 2018, 02:24 AM
Post#3



Posts: 16
Joined: 11-January 18



My query below is to get the information among the tables NOT update action as you suggested. Besides, the query works excellent individually without integrating to Word.


qryTNewNomination_HCAP
SELECT PT.TRef, PT.ApptDtStart, PT.ApptDtEnd, T.UID, T.Title, T.LastName, T.FirstName, T.Alias, T.EMail_1, T.BQual, T.PQual1, T.PQual2, T.PQual3, T.ClinicAdd1, T.ClinicAdd2, T.ClinicAdd3, TR.HDesc, NA.JobPosition, NA.Company, NA.Honorarium, NA.Allowance, NA.FCF_No, NA.budget
FROM ((tblProcessTNewAppt AS PT INNER JOIN tblTutor AS T ON PT.TRef = T.TRef) INNER JOIN tblTNewAppt AS NA ON PT.TRef = NA.TRef) INNER JOIN tblTRank AS TR ON NA.RankCode = TR.ID
WHERE (PT.ReplyToInvitation= "Y") AND (NA.RankCode = 1)
ORDER BY T.LastName, T.FirstName, T.Alias;

Regards.
Go to the top of the page
 
theDBguy
post Feb 28 2018, 10:50 AM
Post#4


Access Wiki and Forums Moderator
Posts: 73,245
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Sorry I'm a little confused. Your original post shows an UPDATE query, so I suggested an UPDATE query. Are we still talking about the same query or is this SELECT query a new issue?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
zaxbat
post Feb 28 2018, 11:43 AM
Post#5



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Yeah, what up with that NT?



But one thought...

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


your final ID may be considered ambiguous to SQL. When I have multiple tables in my SQL that share a field name I always explicitly put the table names in brackets.
This post has been edited by zaxbat: Feb 28 2018, 11:49 AM

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
NT100
post Feb 28 2018, 09:19 PM
Post#6



Posts: 16
Joined: 11-January 18



Forgive me for my careless mistake to mix up old and new discussions.
Go to the top of the page
 
NT100
post Feb 28 2018, 09:26 PM
Post#7



Posts: 16
Joined: 11-January 18



My new question is "Query With Join For VBA Mail Merge" as posted in this forum which is discussing the integration of VBA with join queries to produce word's mail merge. You may advise me as well.

Thank you
Go to the top of the page
 
MadPiet
post Feb 28 2018, 09:57 PM
Post#8



Posts: 2,488
Joined: 27-February 09



As someone else already said, use Albert Kallal's "Super Easy Word Merge".
Available here.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd September 2018 - 12:40 AM