runtime error when trying to update table based on form input

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using a listbox with the extended miltiselect property. The list box
populates it's 2 columns correctly, but when I atttempt to update the table
records for the selected items I'm getting a runtime error 424 on the
"strSQL=" statement (see pasted code below). The msgbox (prior to the
"strSQL=" statement is being used to verify the values during testing and is
working fine.

The intent is to update a date field (CAPOPEnd) in the table
CostAccountNumber with the date value in the second column of the listbox,
where the CANumber in the CostAccountNumber table matches the value in the
1st column of the listbox.
Thanks in advance....

The code I'm running is:

------------------------------------------------
Dim intCurrentRow As Integer
Dim strSQL As String

For intCurrentRow = 0 To Me.ExtendChargeNumber.ListCount - 1
If Forms!ApplyCNExtensions!.ExtendChargeNumber.Selected(intCurrentRow) Then
MsgBox (Me.CACRSelection & " " & Me.ExtendChargeNumber.Column(0,
intCurrentRow) & " " & Me.ExtendChargeNumber.Column(1, intCurrentRow))
strSQL = "UPDATE CostAccountNumber SET CAPOPEnd = """ &
Me.ExtendChargeNumber.Column(1, intCurrentRow) & """ where """ &
Me.ExtendChargeNumber.Column(0, intCurrentRow) & """ = """ &
CostAccountNumber.CANumber & """);"
DBEngine(0)(0).Execute strSQL, dbFailOnError
End If
Next intCurrentRow
MsgBox ("The POP extensions have been completed for the selected charge
numbers")
DoCmd.Close
----------------------------------------------
 
Here's an update, I've restructured the strSQL statement to read:

strSQL = "UPDATE CostAccountNumber SET CAPOPEnd = " &
Me.ExtendChargeNumber.Column(1, intCurrentRow) & " where
(CostAccountNumber.CANumber = " &
Forms!ApplyCNExtensions!ExtendChargeNumber.Column(0, intCurrentRow) & ");"

I've also added a display of the value of strSQL after the above statmenet
and the resultant sql update command looks good, however, I am now getting
the following:

run-time error '3075': syntax error (missing operand) in query expression
'(CostAccountNumber.CANumber = 06003401F34711)'.

The above expression is the resultant "where" clause in the update statement.

Thanks again for any assistance....
Dan
 
Back
Top