Home  Fees/Services  Access 2010 Template  Access Tutorial  Access Download  Articles  Search  Contact  Privacy  Links

Inventory Calculations Using VBA Recordset Methods

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

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.

15 years ago we were putting Access database on the web using Active Server Page (ASP) programming language for the user interface. See examples of Access on the Internet.

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

A Blue Claw Software Design Template:

Interior Design Project Project Management

A Blue Claw Database Design Article:

Repair Corrupted Access Table

Blue Claw Database Design Downloadable Tutorial:
Running Sum Query Method  

Here is a randomly selected programming tutorial:

Email via Gmail

Microsoft Office:
MS Access 2003
Access 2007
Access 2010
Access 2013
Access 2016
Updated On 2/16/2018 2:53:46 AM

Contact Information

Copyright 2000-2018 Blue Claw Database Design

MS Access 2013, Access 2010 & Microsoft Office 2007