SQL Not Updating

[DS]

This statement is not updating. All of the criteria is there, These are all
number fields.
Thanks
DS

Dim QTYSQL As String
'DoCmd.SetWarnings False
QTYSQL = "UPDATE tblCheckDetailsTMP SET tblCheckDetailsTMP.CDQuantity =
Forms!frmFXDiscountItem!TxtQTY " & _
"WHERE tblCheckDetailsTMP.CDCheckID = Forms!frmFXDiscountSelect!TxtCheckID "
& _
"AND tblCheckDetailsTMP.CDGroupID = Forms!frmFXDiscountItem!TxtGroupID " & _
"AND tblCheckDetailsTMP.CDSubGroupID =
Forms!frmFXDiscountItem!TxtSubGroupID;"
DoCmd.RunSQL (QTYSQL)
'DoCmd.SetWarnings True

 

[Steve Sanford]

Comment out the line
DoCmd.RunSQL (QTYSQL)

and add this line

Debug.Print QTYSQL

then look at the immediate window. You won't see the values form the
controls on the form because the reference to the controls are inside the
quotes. SQL doesn't know that it should use the values from the controls. For
the first field, the SQL is trying to put the text,
"Forms!frmFXDiscountItem!TxtQTY", into a numeric field. Same for the other
fields - the SQL is trying to put text onto a numeric field.

TRY this:


Dim QTYSQL As String

'DoCmd.SetWarnings False

'*** each line below should begin with QTYSQL ***
QTYSQL = "UPDATE tblCheckDetailsTMP"
QTYSQL = QTYSQL & " SET CDQuantity = " & Forms!frmFXDiscountItem!TxtQTY
QTYSQL = QTYSQL & " WHERE CDCheckID = " & Forms!frmFXDiscountSelect!TxtCheckID
QTYSQL = QTYSQL & " AND CDGroupID = " & Forms!frmFXDiscountItem!TxtGroupID
QTYSQL = QTYSQL & " AND CDSubGroupID = " &
Forms!frmFXDiscountItem!TxtSubGroupID
QTYSQL = QTYSQL & ";"


'=== for testing====
'comment out or delete after testing
Debug.Print QTYSQL
'=== for testing====

'this is faster than DoCmd.RunSQL()
CurrentDb.Execute strSQL, dbFailOnError

'DoCmd.RunSQL (QTYSQL)
'DoCmd.SetWarnings True



HTH

 

[DS]

Thanks that helped. I had a field named wrong.
Thanks
DS