Doing Calculations on a Form and Storing the Results

Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.


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

 


Want More?

Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.