Subtracting and adding inventory on form

[Pat Rideout]

I can't seem to figure out how to get a form to subtract
information from a table or query. I'm trying to set up
a form for customer contacts that will take the items
ordered out of inventory and add the items purchased into
inventory.

Can anyone help me? I'm using the Order Entry and
Inventory Control databases.

Thanks for any help you can give me.
Pat

 

[Ayelet]

Hi,
I'm not sure how to do this if you have two seperate DB's,
but I used something along these lines:
'Code Start
Dim DB As DAO.Database
Dim rsOrder, rsInventory As DAO.Recordset

Set DB= CurrentDB()
Set rsOrder = DB.OpenRecordset(<the order tbl at the base
of the form>)
Set rsInventory = DB.OpenRecordset("InventoryTbl")

If rsOrder.BOF Then 'check that the order is not empty
MsgBox "This Order Is Empty!!"
Exit Sub
End If

Do Until rsOrder.EOF
'go over items in the order, and substract from
inventory
rsInventory.FindFirst("[ItemID] = " & rsOrder![ItemID])
If rsInventtory.NoMatch Then
MsgBox "Item Not Found! Skipping..."
Else
rsInventory.Edit
rsInventory![Amount] = rsInventory![Amount] -
rsOrder![Amount]
rsInventory.Update
End If
rsOrder.MoveNext
Loop
'Code Ends

if you base the form on the orders table, that part should
update automatically, and the code above should do the
trick.
Hope this helps,
Ayelet