updating tables using a form

[newbie87]

Hi All,

I'm currently using an update query for a form for users and I'm running into problems.

the form consists of text fields that allow users to enter their details, once completed they then click a save button which allows them to choose one of two options, add new or modify existing.

If the user only fills half of their details in and clicks add new, these values insert into the relevant tables allowing them to come back another time to fill in the rest of their details

using DLookups the user can populate the form with their exisiting details that they have already inserted, so that they can see and carry on modifying their data, but when they click modify existing, the original textfields that were left blank do not update, but the fields that were filled in originally do update.

So for example if the user saves their details as below:
Name.text = peter
address.text = left blank

if i modified the name.text to david and address.text to 123road, the name.text updates to david but the address still stays blank

The section of code im having trouble with is below.

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

any help would be amazing.

 

[spikepl]

When you ask for help, "trouble" or " it doesn't work" is an information-free message conveying only that you are unhappy.

State what you want/intend with the code, what does the code do or not, as it may be, any error messages and where, and what did you expect it to do instead.

 

[newbie87]

Hi,

State what you want/intend with the code, what does the code do or not, as it may be, any error messages and where, and what did you expect it to do instead.

I need it to update the tables with the new modified data that the user has entered. But where there are currently empty values in the table the update doesn't work and doesnt update the new data. I dont get any run-time error messages.

frmEnquiryDetails is the form where the user enters their details and this for example '" & Forms!frmEnquiryDetails!Part_No_length & "' is refering to the Part_No_length textbox on that form. I use this reference as the save button opens a new modal form with two options Add Record and Modify existing.

I have changed the current update sql statement to the following but this updates all of the rows in the Material_Cost table to the same data.

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

 

[spikepl]

What are the data types of all the fields listed in your query? Updating a field holding a number does not require single quotes in the query. Updating a text field does.

You have a built-in syntax checker. Copy the SQL statement, paste it into the query designer (in SQL view), fix the query so it does what you want, and the paste the sql back into your code.

 

[spikepl]

An one other thing: put the SQL in a string and debug.write that string. You can see it in the Immediate window, and inspect it for obvious goofs

 

[newbie87]

Hi,

I have this working now, I needed to criteria to be the ID and i needed to add an Id field to my form, and turn the visibility to false, the sorted it straight away, many thanks for the help

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