Update table after input in form

[orcaa]

I have a table called "T_Plate" with fields "Plate_Name" and "Checkout_Date" and "Checkout" (this is True/False field).
I have a form "F_update_checkout__Plates" with Textbox called "Text17" and a button called "Command19"

Now the user can fill in a plateName in "Text17" of the form fi "Plate_20" and push the button. The fields "Checkout_Date" should be updated to current date and "Checkout" should the checkbox be updated to False

This is the VB code I tried , but get an errormessage . Please some help.


Private Sub Command19_Click()
If IsNull(Me!Text17) Then


MsgBox "You must input a value to PlateName"
Else
CurrentDb.Execute "UPDATE T_Plate " & _
"SET T_plate.Checkout = False" And "Checkout_Date = Date" where T_Plate.Plate_Name = " &forms![F_update_checkout__Plates]![Text17], dbFailOnError"
End If
End Sub

[jzwp11]

The date function as well as the form reference cannot be contained within double quotes since they are variables. Also, the dbfailonerror is part of the command, so it must not be enclosed in double quotes. Further, if the Plate_Name field is a text data type you must enclose the text value coming from the form in single quotes. Also, dates must be enclosed by # signs. With that, the correct SQL Update query syntax is as follows:

"UPDATE T_Plate SET T_plate.Checkout = False And Checkout_Date =#" & Date() & "# where T_Plate.Plate_Name ='" & forms![F_update_checkout__Plates]![Text17] & "'"

I typically put the query text into a variable and then reference the variable in the CurrentDB.Execute command line

Dim mySQL as string
mySQL= "UPDATE T_Plate SET T_plate.Checkout = False And Checkout_Date =#" & Date() & "# where T_Plate.Plate_Name ='" & forms![F_update_checkout__Plates]![Text17] & "'"

currentDB.execute mySQL, dbfailonerror

If you put the query text into a variable you can print the text to the Immediate window in VBA (using the debug.print statement) to check for errors. Here is an example of where you might place the debug.print statement


Dim mySQL as string
mySQL= "UPDATE T_Plate SET T_plate.Checkout = False And Checkout_Date =#" & Date() & "# where T_Plate.Plate_Name ='" & forms![F_update_checkout__Plates]![Text17] & "'"

debug.print mySQL

currentDB.execute mySQL, dbfailonerror