how to add a calculated field to a form in access

Hi Thomas Sadler

Thank you for reaching out to Microsoft Q&A Forum and sharing your concern about adding calculated fields to a form in Access.

From what you've shared, it sounds like you're looking to perform calculations dynamically based on data entered into one field, then populate other fields on the same form.

To add a calculated field to a form in Access, the recommended approach is to use an unbound control rather than storing the calculated value in a table. Storing calculated values in tables can lead to inconsistencies if the source data changes, whereas unbound controls calculate values dynamically at runtime, ensuring accuracy. This method also reduces redundancy, avoids unnecessary storage, and allows you to easily modify expressions without altering the table structure.

For detailed guidance, please refer to this article on steps to Create a Calculated Control. Additional examples of expressions can be found in this link.

When designing Access forms, it's advisable to handle calculations within the forms or underlying queries rather than storing them directly in tables. Assigning descriptive names to controls can simplify ongoing maintenance, and for scenarios involving complex logic or the need to update other fields dynamically, VBA in form events offers greater flexibility and control. Please refer to this link for further details: Events (Microsoft Forms) | Microsoft Learn.

In cases where forms are bound to large datasets, steer clear of overly intricate calculations in expressions to avoid performance issues; opting to perform these in queries is often more efficient. Should the calculation fail to update as anticipated, verify that the form permits edits and that no filters are impeding refreshes.

I hope this explanation helps clarify the process. Please let me know how you get on, as your feedback is valuable to the community.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

For simple calculations you can do so in an expression as the ControlSource of an unbound text box control.  Say, for instance you have an OrderDetails subform with bound controls cboProductID, UnitPrice, TaxRate, and Quantity. The first thing to note is that the current unit price and tax rate for the product can be returned in the RowSource property of a combo box bound to the ProductID column.  In the combo box's AfterUpDate event procedure the values are then assigned to the UnitPrice and TaxRate controls: 

    Dim ctrl AS Control
    Set ctrl = Me.ActiveControl
   
    Me.UnitPrice = ctrl.Column(1)
    Me.TaxRate = ctrl.Column(2)

By having UnitPrice and TaxRate columns in both the Products and OrderDetails tables this ensures that the values of these attributes for each order remain static regardless of any subsequent changes which take place to them in the Products table. 

To calculate the total price net of tax per order line in another control in the subform an unbound text box could be used, with the following expression as its ControlSource property: 

    = [UnitPrice] * [Quantity] 

I'll come back to the calculation of the tax later. 

An alternative approach is not to do the calculations in the form, but in its RecordSource query.  For an example take a look at InvoicePDF.zip in my Dropbox public databases folder at:

https://www.dropbox.com/scl/fo/0scigd3r48hx5xrev2jrf/AB0-GMdTgMAO5O1cGdr3QW0?rlkey=ib6bs6g9jqcrywwzivur3265t&dl=0

In this little demo file the RecordSource of the invoice details subform in the main invoices form is:

SELECT
    InvoiceDetails.*,
    [InvoiceDetails].[UnitPrice] * [Quantity] AS NetAmount
FROM
    Products
    INNER JOIN InvoiceDetails ON Products.ProductID = InvoiceDetails.ProductID
ORDER BY
    Products.Product;

In this case the NetAmount per invoice line is computed in the query.

The above examples are of calculations based on values within each row in a table.  In other cases calculations will need to be made on the basis of values in multiple rows.  In the above demo you'll see that the invoices form includes another subform in which the amount of Value Added Tax (VAT) payable in the invoice is computed.  This tax is calculated not on each invoice line (except in retail transactions),  but on the invoice total.  This could be done in an expression in the ControlSource property of an unbound control, but the expression would quite a complex one.  It's much easier to do so in the subform's RecordSource query, which is: 

SELECT
    InvoiceDetails.InvoiceNumber,
    InvoiceDetails.TaxRate,
    Sum([Quantity] * [UnitPrice] * [TaxRate]) AS TaxSub,
    Sum([Quantity] * [UnitPrice]) AS NetSub,
    NZ (TotalPayments.TotalAmount, 0) AS TotalPaid
FROM
    InvoiceDetails
    LEFT JOIN (
        SELECT
            InvoiceNumber,
            SUM(Amount) AS TotalAmount
        FROM
            Payments
        GROUP BY
            InvoiceNumber
    ) AS TotalPayments ON InvoiceDetails.InvoiceNumber = TotalPayments.InvoiceNumber
GROUP BY
    InvoiceDetails.InvoiceNumber,
    InvoiceDetails.TaxRate,
    TotalPayments.TotalAmount;

 With the data used in the demo the query returns two rows, because the items in question use two rates of VAT, one the standard rate which applies to the majority of goods or services, the other a zero rate, which applies to certain categories of products, such as books in the demo.  These are then returned as two rows in the subform, in which the total net amounts for each category of goods per VAT rate are also returned.  From the sum of the amounts gross of VAT is subtracted the sum of any payments already made to return the gross amount due.  The gross amount is calculated in the ControlSource property of an unbound text box in the subform's footer, using the following expression: 

=Sum([TaxSub]+[NetSub])-[TotalPaid] 

The three columns used in this expression are returned by the above query.