Update for inner join syntax

[Annoscia85]

Hi, I hope somebody could help..
Im curreently joining my tables together which work fine, but i want to use an update sql statement for my update button.
this is the code i have..


Private Sub Update_Click()

CurrentDb.Execute "UPDATE Material_Cost INNER JOIN ((Costing_Main INNER JOIN Costing_Sub ON Costing_Main.Enquiry_No = Costing_Sub.Enquiry_No) " _
& "INNER JOIN Machine_Charge_Out ON (Costing_Main.ID = Machine_Charge_Out.ID) AND (Costing_Sub.Machine_Type = Machine_Charge_Out.Machine_Type)) " _
& "ON (Costing_Main.ID = Material_Cost.ID) AND (Material_Cost.ID = Machine_Charge_Out.ID) SET " _
& "Costing_Main.Part_No = Costing_Main.Part_No, Costing_Main.Enquiry_No = Costing_Main.Enquiry_No, Costing_Main.Description = Costing_Main.Description, " _
& "Costing_Main.Neida_Part_No = Costing_Main.Neida_Part_No, Costing_Main.Current_Date = Costing_Main.Current_Date, Costing_Main.Revision = Costing_Main.Revision, " _
& "Costing_Sub.Qty_Price_Break = Costing_Sub.Qty_Price_Break, Costing_Sub.Enquiry_No = Costing_Sub.Enquiry_No, Costing_Sub.Machine_Type = Costing_Sub.Machine_Type, " _
& "Costing_Sub.Rate_Hr = Costing_Sub.Rate_Hr, Costing_Sub.Cycle_ppm = Costing_Sub.Cycle_ppm, Costing_Sub.Setting_Time = Costing_Sub.Setting_Time, " _
& "Costing_Sub.Tool_Cost1 = Costing_Sub.Tool_Cost1, Costing_Sub.Tool_Cost2 = Costing_Sub.Tool_Cost2, Costing_Sub.Revision = Costing_Sub.Revision, " _
& "Machine_Charge_Out.Machine_Type = Machine_Charge_Out.Machine_Type, Machine_Charge_Out.Hourly_Rate = Machine_Charge_Out.Hourly_Rate, " _
& "Material_Cost.Part_No_length = Material_Cost.Part_No_length, Material_Cost.Weight_Per1000 = Material_Cost.Weight_Per1000, " _
& "Material_Cost.Finished_Weight_kg = Material_Cost.Finished_Weight_kg, Material_Cost.Scrap_Rate_kg = Material_Cost.Scrap_Rate_kg, " _
& "Material_Cost.Costing_Time = Material_Cost.Costing_Time, Material_Cost.Percentage = Material_Cost.Percentage, " _
& "Material_Cost.Scrap_Value = Material_Cost.Scrap_Value " _
& "WHERE Costing_Main.Part_No = Costing_Main.Part_No;", dbFailOnError

End Sub

There is are no error's whilst debugging, it just doesnt update my table data.
Does anybody have any idea's?
Many thanks in advance

 

[jdraw]

You are telling Access to UPDATE Material_Cost but you are SETting
Costing_Main.Part_No = Costing_Main.Part_No
, Costing_Main.Enquiry_No = Costing_Main.Enquiry_No
, Costing_Main.Description = Costing_Main.Description ......

The Table to be Updated is identified first in the UPDATE statement and fields in that table are SET

You can only update fields in one table in one Update statement.

 

[Annoscia85]

Hey jdraw,

I thought you could possibly update more than one tables in the query,

I'll try this now and let you know how i get on

many thanks for your help.

 

[Annoscia85]

Hi jdraw,

Ive managed to code the update statements, 3 out of the 4 work. This is the one that has the syntax error, but I cant seem to find what it is, maybe because I've been staring at it for an hour or so, i need a fresh pair of eyes. The single quotations are there because they are numeric data types.

CurrentDb.Execute "UPDATE Material_Cost SET Part_No_length = "Part_No_length", Weight_Per1000 = "Weight_Per1000", Finished_Weight_kg = "Finished_Weight_kg", " _
& "Scrap_Rate_kg = "Scrap_Rate_kg", Costing_Time = "Costing_Time", Percentage = "Percentage", Scrap_Value = "Scrap_Value" " _
& "WHERE Weight_Per1000 = "Weight_Per1000";"

can you spot the error?

 

[jdraw]

You have a syntax issue with the quotes. But what are you trying to update, from what value to what value. From your syntax it appears that you are setting some fields to text strings?????

 

[Annoscia85]

Hey Jdraw,

Im trying to update numeric data types, i read somewhere that quotes around the textbox name updates numeric values i.e. Costing_Time = "Costing_Time" or am i completely wrong?

Im trying to update the numeric values from the imputted values of the textbox.

Many thanks.

 

[jdraw]

Yes you are completely wrong.

You are working with a form.
Let's say you have a textbox called txtBox1. You enter data 123.
The value of Me.txtbox1 is "123" --- Me represents the current form and
txtbox1 is the textbox control involved.

Tell us more about the form and the controls.

 

[Annoscia85]

ok,
this is my complete code for all the updates, the top 3 work perfectly fine, its the last one that's causing me problems. The form i created uses source control text boxes, i use dlookup's to populate all of the textboxes with the table data from all tables by entering the Part_No from Costing_Main (this works), and when this populates the whole form i want to allow the user to update certain text fields on that form.

Private Sub Update_Click()

CurrentDb.Execute "UPDATE Costing_Main SET Part_No = '" & Part_No & "', Description = '" & Description & "', Neida_Part_No = '" & Neida_Part_No & "', " _
& "Current_Date = '" & Current_Date & "', Revision = '" & Revision & "' " _
& "WHERE Part_No = '" & Part_No & "';"

CurrentDb.Execute "UPDATE Costing_Sub SET Enquiry_No = '" & Enquiry_No & "', Qty_Price_Break = '" & Qty_Price_Break & "', Machine_Type = '" & Machine_Type & "', Rate_Hr = '" & Rate_Hr & "', " _
& "Cycle_ppm = '" & Cycle_ppm & "', Setting_Time = '" & Setting_Time & "', Tool_Cost1 = '" & Tool_Cost1 & "', Tool_Cost2 = '" & Tool_Cost2 & "', " _
& "Revision = '" & Revision & "' " _
& "WHERE Enquiry_No = '" & Enquiry_No & "';"

CurrentDb.Execute "UPDATE Machine_Charge_Out SET Machine_Type = '" & Machine_Type & "', Hourly_Rate = '" & Hourly_Rate & "' " _
& "WHERE Machine_Type = '" & Machine_Type & "';"

CurrentDb.Execute "UPDATE Material_Cost SET Part_No_length = 'Part_No_length', Weight_Per1000 = 'Weight_Per1000', Finished_Weight_kg = 'Finished_Weight_kg', " _
& "Scrap_Rate_kg = 'Scrap_Rate_kg', Costing_Time = 'Costing_Time', Percentage = 'Percentage', Scrap_Value = 'Scrap_Value' " _
& "WHERE Part_No_length = 'Part_No_length';"

End Sub

so if for example i change Weight_Per1000 = 'Weight_Per1000' to Weight_Per1000 = Me.Weight_Per1000, would this work?

 

[jdraw]

No.
Look at your 3rd query

CurrentDb.Execute "UPDATE Machine_Charge_Out SET Machine_Type = '" & Machine_Type & "', Hourly_Rate = '" & Hourly_Rate & "' " _
& "WHERE Machine_Type = '" & Machine_Type & "';"

The terms in dark red are values from controls on your form. Notice the & and
single quote(') marks. The & is used to concatenate strings, and the ' is used to surround string/text values.

This line

UPDATE Machine_Charge_Out SET Machine_Type = '" & Machine_Type & "', Hourly_Rate = '"

is telling Access

UPDATE table Machine_Charge_Out
Set the value of the field Machine_Type in the Table
with the value of the control Machine_Type on the form. The single quotes indicate that the value of the control Machine_Type is a text string.

Hope this helps.

 

[Annoscia85]

Hi,

thanks for all of your replies throughout, i have managed to get this working using the following code...

CurrentDb.Execute "UPDATE Material_Cost SET Part_No_Length = '" & Part_No_length & "', Weight_Per1000 = '" & Weight_Per1000 & "', Finished_Weight_kg = '" & Finished_Weight_kg & "', " _
& "Scrap_Rate_kg = '" & Scrap_Rate_kg & "', Costing_Time = '" & Costing_Time & "', Percentage = '" & Percentage & "', Scrap_Value = '" & Scrap_Value & "' " _
& "WHERE Finished_Weight_kg = " & Finished_Weight_kg & ";", dbFailOnError