Inventory software data management in a multi-user Microsoft
Access environment must be able to take into account locking of data records.
Recordset edits should use error handling to prevent the loss of data when
updating records that may be locked by other users of the database.
In this database example we use the Recordset Edit and After Update
commands combined with error handling to check for general errors and also check
for error number 3188, locked record. If the program encounters any error
then the edits are undone and the user is warned of the problem.

Access does not perform true record locking. Instead
Access locks a page of data on this disk. The page contains approximately
2000 bytes of data. An efficiently designed inventory table may have as
many as 200 records locked at one time - this could represent a significant
portion of a table depending on the amount of inventory records.

Here is the afterupdate event code we use:

Private Sub Qty_Out_AfterUpdate()
Dim db As DAO.database ' note that we always use DAO database and recordset
operations because we always use Microsoft Jet databases.
Dim rst As DAO.Recordset ' DAO is simpler and faster so there is no reason for
us to use ADO in Access. We use ADO in active server pages though.
Set db = CurrentDb

' note about locks on editted records: Access with lock 2000 bytes of the file.
In our inventory table (M_Inventory) 2000 bytes could represent 10 to 20
records.
' so that another user does not have to be editting this same record but may be
editing a record nearby and still cause our specific record to be locked.
With rst
.Edit
!Inv_Qty = !Inv_Qty + Nz(hold_qty, 0) ' in case this is a
change to
'
an existing order quantity we back out the prior value.
!Inv_Qty = !Inv_Qty - Nz(Me.Qty_Out, 0) ' decrement the
inventory quantity based on the new qty value
.Update
End With
Me.QOH.Requery
Exit Sub