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.



Blue Claw Database Design is your premier source of Microsoft Access programming and database support. Both small business and department-level databases. Desktop installation as well as cloud/internet based solutions.

With our new partnership with Jay McCormick we have extensive programming support in SQL/Server database, MS Access software, Power BI applications, .Net web development, PHP, Excel & Outlook customization.


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
    .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

' error handler follows
edit_error:
    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
    Else
    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
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:

Lease Contract Admin Software
 










A Blue Claw Database Design Article:

Access Form Design Help
 









Blue Claw Database Design Downloadable Tutorial:
Access Report Banding  









Here is a randomly selected programming tutorial:

Concatenate Records
 


Microsoft Office:
MS Access 2003
Access 2007
Access 2010
Access 2013
Access 2016
Updated On 5/22/2017 8:59:39 PM

Contact Information

Copyright 2000-2017 Blue Claw Database Design

MS Access 2013, Access 2010 & Microsoft Office 2007