i'm trying to create a simple database to handle sales and stock. basically i have products table, orders table and orderlines table which connects products and orders. there is a field for quantity on hand in the products table. i also have created a form to enter orders (which has the product name and quantity). what i'd like to do is to have the quantity on hand value updated everytime a product is sold. can someone help me please.
Maybe this will help a little...put the code into the LostFocus event of the textbox where Quantity is enetered.
In this case the inventory of a specific product is contained within the products table. The product sold is selected via ComboBox.
You will obviously need to modify this code somewhat to suit your needs....
Dim a As Long, b As Long
'the quantity sold
a = CLng(Me.Quantity)
'the product combobox column which contains the current inventory amount
'of the product selected.
b = CLng(Me.ProductID.Column(4))
'Modify the Inventory amount in the "Products" table. This is [Inventory] subtracted by the [Quantity] purchased.
On Error GoTo ModifyFieldError1
Dim dbs As Database, rst As Recordset
Dim strCriteria As String, NewAmount As Long
' Return reference to current database.
Set dbs = CurrentDb
' Set search criteria.
strCriteria = "[ProductName] = '" & ProductItem & "'"
NewAmount = (b - a) ' [Inventory] subtracted by the [Quantity] purchased.
' Create dynaset-type Recordset object.
Set rst = dbs.OpenRecordset("Products", dbOpenDynaset) 'the products table name is "Products".
' Find first occurrence. There is only one-of-a-kind products therefore the first one found must be the right one.
rst.Edit 'Set the record up to for editing (data change)
rst!Inventory = NewAmount 'Change the field to new data
rst.Update 'Update the table
Set dbs = Nothing
MsgBox "There was an error modifying the Inventory amount in the Products table. You may need to check your inventory after this sale.", vbOKOnly + vbExclamation, "Data Modification Error..."