SQL Syntax Error - UPDATE command

[RichUE]

I have a problem with the following routine. It's an Event Procedure that
runs when the value in the textbox ANB_Qty on frm_Multipliers is changed. I
want the Quantity field in a specified record in tbl_Multipliers to be
updated using the textbox value.

At the moment there is a syntax error in the strSQL statement and I've spent
too long trying to find it. Can you help?

Private Sub ANB_Qty_Change()
' Update value to Quantity in tbl_Multipliers
Dim dbs As Database
Dim tbl_Multipliers As Recordset
Dim a, b, c, strSQL As String
Dim d As Integer

' Return reference to current database.
Set dbs = CurrentDb
' Create SQL string.
' strSQL = "UPDATE tbl_Multipliers SET tbl_Multipliers!Quantity =
'Forms!frm_Multipliers!ANB_Qty.Text'" _
& "WHERE tbl_Multipliers!Parent = 'CoSE001' AND
tbl_Multipliers!Component = 'CoSE003'"
strSQL = "UPDATE tbl_Multipliers SET QUANTITY =
frm_Multipliers!ANB_Qty.Text " _
& "WHERE PARENT = 'CoSE001' AND COMPONENT = 'CoSE003'"
dbs.Execute strSQL
' b = "SELECT tbl_Multipliers!PARENT' FROM tbl_Multipliers WHERE "
' c = "tbl_Multipliers!COMPONENT"

' Run action query.
MsgBox ("ANB_Qty.Text = " & d)
' Return number of records updated.
Debug.Print dbs.RecordsAffected
Set dbs = Nothing
End Sub

 

[Michel Walsh]

Using a database object, CurrentDb or, in your case, dbs, CANNOT solve, for
you, the parameter value using the syntax: FORMS!formName!ControlName.

You have to explicitly append it to the string, with the required
delimiters, though (ie. ' for litteral, # for dates, etc).

strSQL = "UPDATE tbl_Multipliers SET tbl_Multipliers!Quantity = " &
Nz(Forms!frm_Multipliers!ANB_Qty.Value, 0) &
" WHERE tbl_Multipliers!Parent = 'CoSE001' " &
" AND tbl_Multipliers!Component = 'CoSE003'"


as example.



Note: I assumed the field accept integers, not string.
Note: Use Value, NOT Text. To the countraty of VB6, in Access, the Text
property means "what contains the control as the user is typing in it" and
the Value property means "what has been validated for the control". There is
also the OldValue property meaning "what is stored in the database for the
field associated to that control.




Vanderghast, Access MVP