Doing Calculations on a Form and Storing the Results

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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

            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

 

subs profile e1563205311409 - Doing Calculations on a Form and Storing the Results
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address