Basic Inventory Setup with Access

Question

So I'm a Novice to access and need help finishing my Inventory Database.

Currently I have the Following Tables and the field in them: (Table: Fields, first field is primary key)

Items: Item#, Item Desc, Item Spec Reorder Level, Qty to Reorder, Qty on Hand

Materials_Used: ID(just an Autonumber field), Project, Item#, Amount

Projects: Project

Purchase_Orders: PONum, Received Date

Receiving_Amount: ID(again autonumber), Item#, Amount, PONum

I'm having issues in 2 area's:

1. Tying in PONum with Items Receiving_Amount: Currently I have a Purchase_Orders form which asks the user to input the date and POnum and a sub form of the Receiving amount in datasheet form. Thing is after I enter a date and PONum, and move down to entering the Items received I get and Enter Parameter Value box Purchase_Orders.ID and Purchase_Orders.PONumber ------ This issues is solved thanks to Wayne!

2. Having the Qty on Hand for the Items table to automatically update once an item is Received through the PO form (adding to it) and Materials Used form (Subtracting)

Database Relationship

Answer 1

Since you will be working in a multi-user update environment, you will need to make sure there are no conflicts with other users. The safest approach is to use a TRANSACTION.

Next, you need to decide how and when to make the updates to your two tables. Let's go with 'Option 1' which is to have a button the user clicks when they are finished. You would need to call the following subroutine when they click the button. You should also keep track if the user made any changes to the subform, but forgot to click the 'Save' button.

Then I strongly suggest keeping track of the changes you have made. For example, user enters qty of 5, saves changes. Tomorrow, looks at the data, sees the 5 and wants to change it to 6... that will corrupt the true inventory. One method to prevent is to have a 'flag' for the PO line items indicating they have been processed and preventing updates again.

The following code is just a sample... I coded what I think should be the input and output recordsets, but you will need to insure the correct input rows are selected to be processed and then select the output row(s) for the tables.

Look for the comments with ## in them.... fix the code to use YOUR control names.

Let me know if you need more of an explanation.

Option Compare Database
Option Explicit

'   BeginTrans, CommitTrans, Rollback Methods Example
'   After the BeginTrans method starts a transaction that isolates all the changes made,
'   the CommitTrans method saves the changes.
'   Notice that you can use the Rollback method to undo changes that you saved using
'   the Update method. Furthermore, the main transaction is nested within another transaction
'   that automatically rolls back any changes made by the user during this example.

'   One or more table pages remain locked while the user decides whether or not to accept the changes.
'   For this reason, make sure you only execute the transaction on some event - don't allow a user
'   to be interactive, else he may go to lunch and may lock pages someone else needs!

'   Add to: Receiving_Amount: ID(again autonumber), Item#, Amount, PONum
'   Subtract from: Materials_Used: ID(just an Autonumber field), Project, Item#, Amount

Sub BeginTransX_Update_Inventory()
    On Error GoTo Error_trap

    Dim wrkDefault      As DAO.Workspace
    Dim dbs             As DAO.Database
    Dim tblInput        As DAO.recordSet
    Dim tblItems        As DAO.recordSet
    'Dim tblMaterials    As DAO.recordSet

    ' Get default Workspace.
    Set wrkDefault = DBEngine.Workspaces(0)
    Set dbs = CurrentDb

    ' ## Change the following line to use the name of the form control that has your PONum
    Set tblInput = dbs.OpenRecordset("select * from MaterialsRec where PONum = " & Me.txtPONum & ";")            '<<< This will be the source of your changes. Can use a query to filter exact rows.

    ' Start transaction.
    wrkDefault.BeginTrans

    Do While Not tblInput.EOF

        Set tblItems = dbs.OpenRecordset("select * from [Items] where [Item#] = " & tblInput![item] & ";")    ' <<< This will be where the updates are applied.

        ' Increase Qty on Hand
        tblItems.Edit
        tblItems![Qty on Hand] = tblItems![Qty on Hand] + tblInput!Amount
        tblItems.Update

        '## Add a text field named 'ProcStatus' to table MaterialsRec, or delete the following update ... your choice...
        tblInput.Edit
        tblInput!ProcStatus = "updated"
        tblInput.Update

        tblInput.MoveNext
    Loop

    ' You can remove the following code if desired...
    ' Ask if the user wants to commit to all the changes made above.
    If MsgBox("Save changes?", vbYesNo) = vbYes Then
        wrkDefault.CommitTrans
    Else
        wrkDefault.Rollback
    End If

    tblInput.Close
    tblItems.Close
    'tblMaterials.Close
    Set tblItems = Nothing
    'Set tblMaterials = Nothing
    Set dbs = Nothing
    Set wrkDefault = Nothing
    Exit Sub

Error_trap:
    wrkDefault.Rollback
    MsgBox "An error was encountered, but all changes were rolled back." & vbCrLf & _
            "Err: " & Err.Number & vbCrLf & _
            "Desc: " & Err.Description
End Sub