Skip to content

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


      ‘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


         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


            Me![InStock] = lngNewInStock



            ‘Add a record to history table

            Set dbs = CurrentDb

            Set rst = dbs.OpenRecordset(“tblPartHistory”, dbOpenDynaset)


            rst![PartID] = lngPartID

            rst![Quantity] = lngUsed



            Me![txtUsed].Value = Null

         End If

      End If

   End If



   Exit Sub



   MsgBox “Error No: ” & Err.Number & “; Description: ” & _


   Resume ErrorHandlerExit


End Sub


About this author