Access 2010 update table field after form checkbox update

Question

I have an access 2010 database that I'm using to track the installation of equipment.

One table (tblSerial_Numbers) contains all equipment part numbers and serial numbers with a yes/no field type to indicate whether it's been installed or not

The second table (tblInstallation) tracks installation data i.e. date, location, part#, serial# etc...

I have a form based from tblInstallation for the end user to enter all the info needed to populate tblInstallation.

My problem is i want a checkbox on the same form to update the Installed field in tblSerial_numbers.

So for the checkbox i have an After Update event sub with the following code

Private Sub chkInstalled_AfterUpdate()
CurrentDb.Execute "UPDATE tblSerial_Numbers " & _
              "SET Installed = " & Nz(Me.chkInstalled) & _
              " WHERE Serial_Number = " & Nz(Me.cboSerNum)
End Sub

This is supposed to use the serial number specified in the form combo box (cboSerNum) to set "Installed" in tblSerial_Numbers to whatever the checkbox on the form is but it's not working. There are no errors either.

Any Help is appreciated.

UPDATE:

Syntax error has been resolved, updating Installed field in tblSerial_Numbers still not working.

Thanks Nathan That fixed the syntax error... But the update query isn't changing the state of the installed field in tblSerial_Numbers — k1162
do you have a value for both me.chkInstalled and me.cboSerNum? — BobSki

k1162 k1162 · Accepted Answer · 2017-05-17T17:19:49

I've been able to solve this.

The heart of the problem was the SerNum field in tblInstallation. It was a lookup from tblSerial_numbers. The value in cboSerNum was the primary key (autonumber) for the record in tblSerial_Numbers, not the actual serial number of the device.

A Simple modification of the SQL query fixed the updating of the installation field in tblSerial_Numbers.

 Private Sub chkInstalled_AfterUpdate()
 CurrentDb.Execute "UPDATE tblSerial_Numbers " & _
                   "SET Installed = " & Nz(Me.chkInstalled) & _
                   " WHERE ID = " & Nz(Me.cboSerNum)
 End Sub

This is my final code (just made it look more conventional)

 Private Sub chkInstalled_AfterUpdate()

 CurrentDb.Execute "UPDATE tblSerial_Numbers " & _
                   "SET tblSerial_Numbers.[Installed] = " & Me.chkInstalled & _
                   " WHERE tblSerial_Numbers.[ID] = " & Me.cboSerNum & ";"

 End Sub

Access 2010 update table field after form checkbox update

1 Answers