As Einstein said about explaining the theory of general relativity, it can be
made as easy as possible but no more so. You need to put code in the
AfterUpdate event procedure of the form (or subform) in question. Hopefully
you won't have to amend the following code too much. Lets firstly assume:
1. The table is called Inventory and has a primary key column PartID and a
column QuantityOnhand, both number data types.
2. The form's underlying table has a foreign key column PartID which
references the primary key of Inventory.
3. The form's underlying table also has a column Quantity. Note that each
row in the form's underlying table should relate to one PartID only. For
multiple parts (per order say) you can use a subform embedded in a main form.
Do not under any circumstances have multiple columns in one row for multiple
parts per order.
4. Separate forms or subforms are used for parts received and parts
despatched. The code for each would differ only marginally, so I'll give
that for parts despatched first, requiring the Quantity to be subtracted from
the QuantityOnHand value in Inventory:
Dim cmd As ADODB.Command
Dim strSQL As String
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = strSQL
' build SQL string to subtract quantity from QuantityOnHand
' for form's current part
strSQL = "UPDATE Inventory " & _
"SET QuantityOnHand = QuantityOnHand – " & _
Me.Quantity & " WHERE PartID = " & Me.PartID
' execute the command
cmd.CommandText = strSQL
cmd.Execute
For parts received, where you want to add the quantity to QunatityOnHand
merely requires the SQL string to be amended slightly to:
' build SQL string to add quantity to QuantityOnHand
' for form's current part
strSQL = "UPDATE Inventory " & _
"SET QuantityOnHand = QuantityOnHand + " & _
Me.Quantity & " WHERE PartID = " & Me.PartID
This does assume that for parts received ther is already a row in Inventory,
even if the QuantityOnHand is zero.
You could if you wished include some validation in the parts despatched code
to ensure that the quantity does not exceed the quantity on hand. If you do
this it would be better to use the form's BeforeUpdate event procedure as
this accepts a cancel argument which would prevent the record being saved if
the quantity cannot be met from inventory. The user could then reduce the
quantity and try again if necessary:
' execute the command if quantity on hand
' is sufficient
cmd.CommandText = strSQL
If DLookup("QuantityOnHand", "Inventory", "PartID = " & Me.PartID) >=
Me.Quantity Then
cmd.Execute
Else
MsgBox "Insufficient quantity on hand.", vbExclamation, "Warning"
Cancel = True
End If
BTW watch out for code breaking over more than one line in your news reader
when it should be a single line.
Ken Sheridan
Stafford, England
