VBA: Update via Where clause with autonumber (1 Viewer)

[Monsora83]

I am using a button to retire a product. I am trying to make it so that the SQL will retire the product depending on which one the user is looking at on the form using the autonumber as the primary key.

I recieve a data type mismatch error when I attempt to run it. Any suggestions on what I may have overlooked?

Dim SQL_Query As String

SQL_Query = "Update Tbl_1 SET Tbl_1.[Retired] = TRUE where Tbl_1.[Autonumber] = """ & Me.Autonumber & """ "

DoCmd.RunSQL SQL_Query

I modified the code below (which works fine) to write the different line above with the only change being the where clause.

Dim SQL_Query As String

SQL_Query = "Update Tbl_1 SET Tbl_1.[Retired] = TRUE where Tbl_1.[ID]= """ & Me.[ID] & """ "

DoCmd.RunSQL SQL_Query

 

[RainLover]

Would this work on the Double Click Event of the control.

Me.Retired = TRUE

Or a little bit fancier.

If Me.Retired = TRUE Then

Me.Retired = False Else
Me.Retired = TRUE

End If

 

[Monsora83]

I managed to google the answer.

I had to eliminate two sets of quotation marks. So it went from:

Unworking
SQL_Query = "Update Tbl_1 SET Tbl_1.[Retired] = TRUE where Tbl_1.[Autonumber] = """ & Me.Autonumber & """ "

Working
SQL_Query = "Update Tbl_1 SET Tbl_1.[Retired] = TRUE where Tbl_1.[Autonumber] = " & Me.Autonumber & " "