Access SQL Update Not Working

[dalaister]

I have been trying to solve this and getting no where.

I have a table called tblEquipment and another table called tblEquipmentSignout, one to many relationship. EqmtID is the primary key in tblEquipment
I have a form that records the equipment that are signed out to users. After I select "Checked" or "Returned" it runs SQL to update the Equipment Qty to 0 when "Checked out" and back to 1 when checked in. It runs through the SQL and completes just fine but when I go check the tblEquipment it didn't update the Qty at all.
To make it easier I attached the database. If anyone can please help me out, I would greatly appreciate it. Thank you

Edit:
Forgot to mention, there is another event code on the "Update" button that runs the SQL. So I have one after selecting "Checked/Returned" and one for "Update" button just to test out which one will work. The "Update" button will sometimes update other equipment when it's only suppose to update the one that it's currently on.

 

[dhookom]

I'm not seeing where you are setting the value of strSerial. Learn to debug with the debug.print statement.

Private Sub RentStatus_AfterUpdate()
        
    Dim sqlCheckOut As String
    Dim sqlReturned As String
    Dim strSerial As String
    
    sqlCheckOut = "UPDATE tblEquipment INNER JOIN tblEquipmentSignOut ON tblEquipment.EqmtID = tblEquipmentSignOut.EqpmtID SET " & _
    "tblEquipment.EqmtQty = 0 " & _
    "WHERE (((tblEquipment.EqmtSerial) = ' * " & [highlight #AD7FA8]strSerial[/highlight] & " * '))"

    sqlReturned = "UPDATE tblEquipment INNER JOIN tblEquipmentSignOut ON tblEquipment.EqmtID = tblEquipmentSignOut.EqpmtID SET " & _
    "tblEquipment.EqmtQty = 1 " & _
    "WHERE (((tblEquipment.EqmtSerial) = ' * " & [highlight #AD7FA8]strSerial[/highlight] & " * '))"

[highlight #FCE94F]    Debug.Print "sqlCheckOut: " & sqlCheckOut
    Debug.Print "sqlReturned: " & sqlReturned[/highlight]

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016