Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    2

    Red face Unanswered: updating table's value

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

    thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    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....

    Code:
    	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.FindFirst strCriteria
    	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
     
    	rst.Close
    	Set dbs = Nothing
     
    Exit_LostFocus1:	
    Exit Sub
     
    ModifyFieldError1:
    	 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..."
    	 Resume Exit_LostFocus1
    OR

    use just use the SQL INSERT INTO statement.
    Last edited by CyberLynx; 05-13-04 at 02:28.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •