Insert Statment - Is it too big?

[todmac]

Hi All,
I have a database I created, when I originally made it I only had a few fields in it and had written an Insert Statement so that when the user fills out a claim form (partially or full) it saves it as a record and can later be called up to Edit it.
I had to expand on the database and add many more fields. I added all the fields to the Insert Statement today but alas I cannot get it to run


I realize it's turned into a really long statement now..... please, can someone help me?

Public Sub Add_New_Claim()
Dim StrSql As String
StrSql = "INSERT into CLAIMS (Claim_Number, Vendor, Bus_Type, Location, Odometer, Date_Claim_Started, Date_of_Failure, Description, Unit_Number, Date_Submitted, Amount_Claimed_Before_Taxes, Amount_Claimed_After_Taxes, Amount_Paid, Reason_for_Short_Pay, Claim_Status, Claimed_By, Parts_to_be_returned, Date_of_Parts_Shipping, LabourSubtotal,LabourGST, LabourPST,PartsSubTotal,PartsGST,PartsPST,TowingSubtotal,TowingGST,TowingPST,RTSWorkOrderNumber,VendorWorkOrderNumber,CoreCreditAmount,AdditionalInfo,RMA,PrimaryPartNumber,SecondaryPartNumber,DeliverySlipNumber,Courier,CourierAccountNumber,BU)" _
& "values ('" & Nz(Forms!New_Claim.Claim_Number, " ") & "', '" & Nz(Forms!New_Claim.Vendor, " ") & "', '" & Nz(Forms!New_Claim.Bus_Type, " ") & "', '" & Nz(Forms!New_Claim.Location, " ") & "', '" & Nz(Forms!New_Claim.Odometer, " ") & "', #" & Date & "#, #" & Nz(Forms!New_Claim.Date_of_Failure, "01-01-2001") & "#, '" & Nz(Forms!New_Claim.Description, " ") & "', '" & Nz(Forms!New_Claim.Unit_Number, " ") & "', #" & Nz(Forms!New_Claim.Date_Submitted, "01-01-2001") & "#, '" & Nz(Forms!New_Claim.Amount_Claimed_Before_Taxes, " ") & "','" & Nz(Forms!New_Claim.Amount_Claimed_After_Taxes, " ") & "', '" & Nz(Forms!New_Claim.Amount_Paid, " ") & "' " _
& " ,'" & Nz(Forms!New_Claim.Reason_for_Short_Pay, " ") & "', '" & Nz(Forms!New_Claim.Status, " ") & "' , '" & Currentuser & "', '" & Nz(Forms!New_Claim.Parts_to_be_returned, "0") & "', #" & Nz(Forms!New_Claim.Date_of_Parts_Shipping, "01-01-2001") & "#, '" & Nz(Forms!New_Claim.LabourSubTotal, " ") & "', '" & Nz(Forms!New_Claim.LabourGST, "0") & "', '" & Nz(Forms!New_Claim.LabourPST, "0") & "', '" & Nz(Forms!New_Claim.PartsSubTotal, "0") & "','" & Nz(Forms!New_Claim.PartsGST.Value, "0") & "','" & Nz(Forms!New_Claim.PartsPST.Value, "0") & "',' " & Nz(Forms!New_Claim.TowingSubTotal, "0") & "',' " & Nz(Forms!New_Claim.TowingGST.Value, "0") & "','" & Nz(Forms!New_Claim.TowingPST.Value, "0") & "','" & Nz(Forms!New_Claim.RTSWorkOrderNumber, " ") & "' " _
& " ,'" & Nz(Forms!New_Claim.VendorWorkOrderNumber, " ") & "','" & Nz(Forms!New_Claim.CoreCreditAmount, "0") & "','" & Nz(Forms!New_Claim.AdditionalInfo, " ") & "','" & Nz(Forms!New_Claim.RMA, " ") & "','" & Nz(Forms!New_Claim.PrimaryPartNumber, " ") & "','" & Nz(Forms!New_Claim.SecondaryPartNumber, " ") & "','" & Nz(Forms!New_Claim.DeliverySlipNumber, " ") & "','" & Nz(Forms!New_Claim.Courier, " ") & "','" & Nz(Forms!New_Claim.CourierAccountNumber, " ") & "','" & Nz(Forms!New_Claim.BU, " ") & "')"
 
CurrentDb.Execute (StrSql)
MsgBox ("Claim Number " & Forms!New_Claim.Claim_Number & " has been successfully added.")

[ssanfu]

My first question would be "Why aren't you using a bound form?".




Except for the date fields, are all of the rest of the fields text fields? for example, you have

& "','" & Nz(Forms!New_Claim.PartsGST.Value, "0") & "','" &

Is Forms!New_Claim.PartsGST a number or text type field? You have text delimiters (single quotes) enclosing the zero.
(BTW, ".Value" is not needed because Value is the default property.)


You could add

Debug.Print sSQL

before the line

CurrentDb.Execute (StrSql)

then copy line in the immediate window and paste it into a new query to test the syntax.

Or you could provide the dB for analysis....

[John_G]

When you say "you cannot get it to run", what is happening? Are you getting an error message, wrong data, what?

put a debug.print strsql before your currentdb.execute to see what the SQL looks like.

You could also use currentdb.execute strsql, dbfailonerror

which might give you a more meaningful error message.