runtime error when trying to update table based on form input

D

dfeigen115

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
----------------------------------------------
 
D

dfeigen115

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top