upgrading from access 2003 to access 2010 keep getting update error or add new error

[haleyja]

msgbox reads.... run-time error '3346':
number of query values and destination fields are not the same.

below is except from the code after error message when I click debug ends up highlighting last line below.... running access 2010 ported the database from 2003.

any help would be a headach relief.

thanks
James

txtProduct.SetFocus
strProduct = txtProduct.Text
cboRecoverable.SetFocus
strRecoverable = cboRecoverable.Text
txtComments.SetFocus
strComments = txtComments.Text
cboCity.SetFocus
strCity = cboCity.Text


txtSignetRef.SetFocus
strSignetReference = txtSignetRef.Text
txtADD.SetFocus
strADD = txtADD.Text
txtModifiedBy.SetFocus
strModifiedBy = Environ("USERNAME")
'Declare string to hold the SQL Insert statement (makes testing easier)
'and another to hold the current date as one of the Values in the SQL
'Insert statement.
Dim strSQL As String
Dim strsqla As String
strsqla = strSQL
'If this is an update then the SQL UPDATE statement is executed, else the SQL
'INSERT is executed.
If intSwitch = 1 Then
MsgBox "strsql first"
strSQL = "Update [CAC Software Renewals] " & _
"SET Product='" & strProduct & "', [Recoverable?]='" & strRecoverable & "', " & _
"Vendor='" & strVendor & "', Software_Client='" & strSoftwareClient & "', " & _
"Description='" & strDescription & "', CAC_Resp='" & strCACResp & "', " & _
"ITS_Resp='" & strITSResp & "', Budget_Cost='" & strBudgetCost & "', " & _
"IN_ITS_LOA='" & strInItsLoa & "', Paid='" & strPaid & "', " & _
"Actual_Cost='" & strActualCost & "', Date_Paid='" & strDatePaid & "', " & _
"Contract_End_Date='" & strContractEndDate & "', Comments='" & strComments & "', " & _
"City='" & strCity & "', Signet_Reference='" & strSignetReference & "', " & _
"Vendor_Contact='" & strVendorContact & "', ADD='" & strADD & "', Modified_By='" & strModifiedBy & "' " & _
"WHERE ID=" & intID
Else
'Set the strSQL string equal to the Insert statement to be executed.

strSQL = "INSERT INTO [CAC Software Renewals] " & _
"VALUES(" & intID & ", '" & strProduct & "', '" & strRecoverable & "', " & _
"'" & strVendor & "', '" & strSoftwareClient & "', " & _
"'" & strDescription & "', '" & strCACResp & "' , '" & strITSResp & "', " & _
"'" & strBudgetCost & "', '" & strInItsLoa & "', '" & strPaid & "', '" & strActualCost & "', " & _
"'" & strDatePaid & "', '" & strContractEndDate & "', '" & strComments & "', " & _
"'" & strCity & "', '" & strSignetReference & "', '" & strVendorContact & "', '" & strADD & "', '" & strModifiedBy & "');"
MsgBox "else of strsql"
End If
'Debug.Print strSQL
'Turn off the annoying Access warnings when adding, appending, etc. and then execute
'the strSQL string.
DoCmd.SetWarnings False
MsgBox strSQL
DoCmd.RunSQL strSQL

[davegri]

ADD='" & strADD & "', Modified_By='" & strModifiedBy & "' " & _

Might be as simple as changing a field name. 'ADD' is a reserved word.

[John_G]

Your SQL has INSERT INTO [CAC Software Renewals] VALUES.... You didn't give a list of field names that correspond to the list of values. That's fine, but when you do that, the number of values in the list must be the same as the number of fields in the destination table. There are 20 values in the list - how many fields are in [CAC Software Renewals] ? The error message suggests it is not 20.

[ssanfu]

"Description" is also a reserved word. ("Description='" & strDescription &)
"Description" of what??? Maybe use "SoftwareDesc" or "ProdDesc" or "ProductDesc" or ....



Also should NOT use punctuation or special characters in object names ([Recoverable?])
A better name might be "IsRecoverable".