We help IT Professionals succeed at work.

Update query

pdvsa
pdvsa asked
on
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
Comment
Watch Question

John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:


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

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

pdvsaProject finance

Author

Commented:

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 TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:

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

pdvsaProject finance

Author

Commented:

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 TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:

If this the case then

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

Author

Commented:

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.   


John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:

you execute it like this

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

Author

Commented:

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


John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:

Well the code wrapper has some issue

copy paste exactly

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


pdvsaProject finance

Author

Commented:

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 TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:

No, 1 line as the screenshot

pdvsaProject finance

Author

Commented:

Do I need to add currentdb.execute?   


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

Software & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:

Its another way...almost the same...take your pick

pdvsaProject finance

Author

Commented:

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

John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:

Docmd.RunSQL should also work