How to create calculation field on a MS Access form

This Access tutorial will show you how to create a field in an Access form that will store the calculation between two or more fields. Then the result will be saved to the Table automatically. Its a fun when we can have an automated calculation on a field in a form. Here is the example:

add automated calculation on access 2013

The Form_test is a form build from the Table1. This form is used to entry the data into the Table1. There are three columns or fields on this form. Value A, Value B and Result. The idea is whenever user finish entry the data in Value B, the system will automatically calculate Value A + Value B and store it in Result field. You can modify the calculation scenario but the idea should be the same as this example. Do the following to give automated calculation to a field in Access form.

Open the form in Design View. Now, click The result text box and go to the Even on property sheet.

add automated calculation on access 2013 2

Click the After Update on the event and select [Event Procedure], then click [….] button close to it. It will open the visual basic editor. Now type the following formula

Private Sub Result_AfterUpdate()
Me.Result = Me.Value_A + Me.Value_B
End Sub

Save and close it. Now we need to call the above procedure on the Value B field. This procedure above will be executed everytime user edit the field Value B and the result will be displayed on Result field. Do the same way as above to the Value B field and enter this command/procedure

Private Sub Value_B_AfterUpdate()
Call Result_AfterUpdate
End Sub

Done. Now, everytime you enter value on Value A and Value B, it will automatically calculated and stored in Result field.


Leave a Reply

Your email address will not be published. Required fields are marked *