Help with strSQL = "UPDATE

Question

Form customers lists all the orders for one client. On that form I have a hyperlinked field (tblOrderID) which opens the f_OrderDetails form to edit the specific order. Editing involved adding notes and clicking if the order has an error or is okay to process. Am getting a write conflict error when I add notes and click the Order_Error button. Am not getting the write conflict error when I don't add notes and click on the Order_Error button. Am hoping to also save the OrderNotes with the same code when I click the Order_Error button to avoid the conflict.

.

Table Name: tbl_Orders

Table Fields: tblOrderID, OrderReview, OrderNotes

Form Name: f_OrderDetails

Form fields: tblOrderID, OrderReview, OrderNotes

Below is the current code that I have to only update the FileReview. I would like to add code to also update the OrderNotes with the text/data on the form. My attempts at crafting the code have failed.

Private Sub Order_Error_Click()

Dim strSQL As String

strSQL = "UPDATE tbl_Orders SET OrderReview = 'Order Error' WHERE tblOrderID = " & Forms!f_OrderDetails!tblOrderID & ";"

CurrentDb.Execute strSQL, dbFailOnError

DoCmd.Close acForm, "f_OrderDetails", acSavePrompt

End Sub

Answer 1

Try this:

strSQL = "UPDATE tbl_Orders SET OrderReview = 'Order Error', OrderNotes = '" & Forms!f_OrderDetails!OrderNotes & "'WHERE tblOrderID = " & Forms!f_OrderDetails!tblOrderID & ";"

You have to include the reference to the value in the form control you want to add the same way you referred to the control for the WHERE clause, in other words. Make sure to include the text delimiters for the string value from the OrderNotes field.

Answer 2

Thanks!