# Doing Calculations on a Form and Storing the Results

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
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 < 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![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

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