Doing Calculations on a Form and Storing the Results
How to do calculations on a form and store the results.
Q: Rob Stoker writes: I have a table called “tblPartHistory” where I store the quantity of parts used in the field “quantity”. When I enter the quantity used into the form I want it to subtract the amount used from the table “tblPart” with a field of “quantitystock”. For example:
A Part has 30 in stock (recorded in the tblPart table)
I use 10 A Part and enter the quantity into the tblPartHistory.
I want it to take the 10 used and subtract it from stock so the value in tblPart is now 20.
Should I be using a SQL command or another command? Should I be using an UPDATE command?
A: I would use code on the AfterUpdate event of an unbound textbox (the control where the amount to be used is entered). I am assuming that the form is bound to tblPart, and that PartID is a unique ID identifying parts in both tables (a primary key in tblPart, and a foreign key in tblPartHistory).
Private Sub txtUsed_AfterUpdate()
On Error GoTo ErrorHandler
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngPartID As Long
Dim lngInStock As Long
Dim lngUsed As Long
Dim lngNewInStock As Long
Dim strPrompt As String
Dim strTitle As String
lngPartID = Nz(Me![PartID])
If lngPartID = 0 Then
GoTo ErrorHandlerExit
Else
‘Update records in tables
lngInStock = Nz(Me![InStock])
lngUsed = Nz(Me![txtUsed].Value)
If lngUsed = 0 Or lngInStock = 0 Then
Me![txtUsed].Value = Null
GoTo ErrorHandlerExit
Else
lngNewInStock = lngInStock – lngUsed
If lngNewInStock < 0 Then
strTitle = “Entry error”
strPrompt = “You can’t use more than is in stock”
MsgBox strPrompt, vbExclamation + vbOKOnly, strTitle
Me![txtUsed].Value = Null
GoTo ErrorHandlerExit
Else
Me![InStock] = lngNewInStock
Me.Refresh
‘Add a record to history table
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(“tblPartHistory”, dbOpenDynaset)
rst.AddNew
rst![PartID] = lngPartID
rst![Quantity] = lngUsed
rst.Update
rst.Close
Me![txtUsed].Value = Null
End If
End If
End If
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & _
Err.Description
Resume ErrorHandlerExit
End Sub