<%dim crumb crumb="Inventory Calculations Multi-User" Crumb2="Inventory Calculations Using VBA Recordset Methods" crumb1="Recordset Processing Inventory Calculations Example" %> Multi-User Inventory Calculations
Home  Fees/Services  Access 2010 Template  Access Tutorial  Access Download  Articles  Search  Contact  Privacy  Links

<%if len(crumb2)>2 then response.write crumb2 else response.write ">

Open Email Using SendObject Access Download

User Inactivity Logout VBA Code

Disable Shift Key Demo

Send Email Microsoft Access Tutorial Download (Advanced)

Sequential Counter in Query

Multi Select List Box Query Parameters

Row Level Data Security

Programming MS Access Security Alternative

Single User Inventory Calculations Download

How To Perform Multi-User Inventory Calculations

Make Dependent Combo Box Code MS Access Tutorial

Union Query Example (Simple)

Union Query (Advanced) Access Tutorial

How To Fill Fields From Combo Box

Use Global Variables as Parameters

Continuous Form Dependent Combo Box

How To Program Continuous-Continuous Master/Detail Forms

Access Bar Chart / Bar Graph

Programming Crosstab Query Example

TransferText & OutputTo Microsoft Access

DoCmd.OpenForm & OpenArgs VBA Example

Running Sum Query Method

Choose Command Dynamic SQL Order By

Access Conditional Format

Access Report Banding

Popup Form Control Method

<% gnum="2600800820" %>

Inventory Calculations Multi-User Environment

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

On Error GoTo edit_error
Set rst = db.OpenRecordset("select inv_qty from m_inventory where barcode=" & Me.BarCode)

' 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
    !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
End With
Exit Sub

' error handler follows
    If Err.Number = 3188 Then
        MsgBox "This record is locked by another user. Please try this edit again later."
        Me.Qty_Out = hold_qty ' return qty to original value
    MsgBox "An unexpected error occured while attempting the edit the data record: " & Err.Number & " " & Err.Description
    Me.Qty_Out = hold_qty ' return qty to original value
End If
Resume exit_out
End Sub

Use this link to download our Microsoft Access Multi-user Inventory Database.

Inventory Multi-User Access Download

Microsoft Office:
MS Access 2003
Access 2007
Access 2010
Access 2013
Access 2016
<% dim rnum Randomize rnum = Int(Rnd * 10) + 1 response.write " Updated On " & now-rnum %>

Contact Information

<% Response.write "Copyright 2000-" & year(now) & " Blue Claw Database Design" %>

MS Access 2013, Access 2010 & Microsoft Office 2007