Avatar of pdvsa
pdvsa
Flag for United States of America asked on

Update query

Experts

I need to update tblLetterOfCredit.Amount to tblPricing.[Calc Price] where tblLetterOfCreditID.LetterOfCreditID = me.LCID
I am calculating a price on tblPricing.[Calc Price] and have a button that if clicked will update tblLetterOfCredit.Amount to this value in tblPricing.[Calc Price]

How can I do this?  I am not sure if this is an "Insert into tblLetterOfCredit" code.

If I have missed something let me know.  

Thank you
Microsoft Access

Avatar of undefined
Last Comment
John Tsioumpris

8/22/2022 - Mon
John Tsioumpris


Update tblLetterOfCredit SET Amount =Calc Price WHERE tblLetterOfCreditID.LetterOfCreditID = me.LCID

Open in new window

Well an UPDATE query looks like this..but i am afraid I don't have the Relationships between the tables

pdvsa

ASKER

Hi John,


It seems I get a I compile error (line turns red) and pop up msg "Expected End of Line Statement" and the word "SET" gets highlighted. Let me know what is next....thanks!

John Tsioumpris

Where are you putting the code...this is a sample SQL query...without further knowledge i can only guess.

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
pdvsa

ASKER

I am putting the code on a button on frmPricing that is based on tblPricing.  The form has [Calc Price] that is a calculated field in tblPricing.  I have a button on frmPricing that if clicked will update that Calc Price to the value in tblLetterOfCredit.Amount.  I hope this answers your question.  Let me know I am not making it clear.  

John Tsioumpris

If this the case then

Update tblLetterOfCredit SET Amount =" & Me.[Calc Price]& " WHERE LetterOfCreditID =" &  me.LCID

Open in new window

pdvsa

ASKER

darn...I get the same compile error expected end of statement pop up msg. 

I copy and paste exactly and the line is red.  I thought maybe a space was off and I modify and once I move off the  line the pop up msg compile error pops up and SET is highlighted.   


⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
John Tsioumpris

you execute it like this

Docmd.RunSQL " Update tblLetterOfCredit SET Amount =" & Me.[Calc Price]& " WHERE LetterOfCreditID =" &  me.LCID

Open in new window

pdvsa

ASKER

Hi John, darn...still being a bugger.  I have attached a screen shot. 


John Tsioumpris

Well the code wrapper has some issue

copy paste exactly

Docmd.RunSQL "Update tblLetterOfCredit SET Amount =" & Me.[Calc Price] & " WHERE LetterOfCreditID =" &  me.LCID


Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
pdvsa

ASKER

Are they 2 separate lines?  If so, it doesnt allow me to copy exactly like as so.  Docmd.RunSQL "....will change to Docmd.RunSQL " "...it adds another dq. 

John Tsioumpris

No, 1 line as the screenshot

pdvsa

ASKER

Do I need to add currentdb.execute?   


 CurrentDb.Execute "Update tblLetterOfCredit SET Amount =" & Me.[Calc Price] & " WHERE LetterOfCreditID =" & Me.LCID

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
John Tsioumpris

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
pdvsa

ASKER

Thank you.  It seemed to work if I added the currentdb.execute.   Thanks for the code...appreciate it!

John Tsioumpris

Docmd.RunSQL should also work