Creating Forms  «Prev  Next»
Lesson 14 Create calculated controls
Objective Create a calculated control on a form.

Create calculated Controls in Access

As you worked through the lesson on calculations, you may have thought that the calculation you really wanted to create was a sum of hours worked on one project for the Projects form. Unfortunately, displaying aggregate calculations on a form is rather complicated. However you may find that the ease of creating aggregate calculations on reports with the Report Wizard makes up for that. You might want to try working around the aggregate calculation problem by creating a query that includes all the data you need on your form as well as a new field that calculates the result you want to display on the form.
  • Aggregates, Unique Values, and Top Values:
    Unlike Access 2010 web databases, web apps do support aggregate queries, with the familiar aggregate functions you have in client queries. Not only are they much faster to build and view than equivalent data macros, but you can also use them as sources for data macros. As you may expect, you still can specify whether a query should return only distinct values or restrict the numbers of rows returned.

Table 4-14 Rules for Updating Queries
Type of Query or Field Updateable Comments
Many-to-one-to-many relationship No Can update data in a form or data access page if Record Type = Recordset
Two or more tables with no join line No Must have a join to determine updateability
Crosstab No Creates a snapshot of the data
Totals query (Sum, Avg, and so on) No Works with grouped data creating a snapshot
Unique Value property is Yes No Shows unique records only in a snapshot
SQL-specific queries No Union and pass-through work with ODBC data
Calculated fields No Will recalculate automatically
Read-only fields No If opened read-only or on read-only drive (CD-ROM)
Permissions denied No Insert, replace, or delete not granted in older MDB databases that use user-level security
ODBC tables with no unique identifier No Unique identifier must exist
Paradox table with no primary key No Primary key file must exist
Locked by another user No Can’t be updated while a field is locked by another

Overcoming Query Limitations

Table 4-14 shows that there are times when queries and fi elds in tables are not updateable. As a general rule, any query that performs aggregate operations or uses an ODBC data source is not updateable; most other queries can be updated. When your query has more than one table and some of the tables have a one-to-many relationship, some fields might not be updateable (depending on the design of the query).
  • Updating unique index (primary key):
    If a query uses two tables involved in a one-to-many relationship, the query must include the primary key from the one table. Access must have the primary key value so that they can find the related records in the two tables.
  • Create a Calculated Control on Report:
    You can create a calculated control on a report, but in general the place to create calculations is in a query or in a report form. The result of a calculation in a query can easily be displayed in a form if the query is used as the basis for the form. To create a calculated control, you need to do two things:
    1. Create the new control.
    2. Type in the expression for the calculation.

The following series of images takes you through the process.

The tabular form is actually a subform to a form that shows project data. I want to add a column that shows how much I can bill for each date. To do so, I have to display the form in design view.
1) The tabular form is actually a subform to a form that shows project data. I want to add a column that shows how much I can bill for each date. To do so, I have to display the form in design view.
Once the form is in design view, I need to add a new text box by using the Text Box button in the toolbox. I click the Text Box button and draw the new control on the form.
2) Once the form is in design view, I need to add a new text box by using the Text Box button in the toolbox. I click the Text Box button and draw the new control on the form.
I get a text box and an accompanying label. I will clean it up a bit before adding the expression by moving the label into the form header and making the size and position of the new controls match those already on the form. I will also change the text in the label from the Text6 to Billable.
3) I get a text box and an accompanying label. I will clean it up a bit before adding the expression by moving the label into the form header and making the size and position of the new controls match those already on the form. I will also change the text in the label from the Text6 to Billable.
Now I am ready to put the expression in the text box. The billable amount is the hours worked times the $50 hourly rate, so the expression is :<br> =[Hours worked] * 50. The expression must start with an equals sign, and field names need to be in square brackets. Remember the rules for query expressions
4) Now I am ready to put the expression in the text box. The billable amount is the hours worked times the $50 hourly rate, so the expression is :
=[Hours worked] * 50. The expression must start with an equals sign, and field names need to be in square brackets. Remember the rules for query expressions
The text box does not need to be large enough to display the whole expression as long as it is all in there. Now I am ready to see the result.
5) The text box does not need to be large enough to display the whole expression as long as it is all in there. Now I am ready to see the result.
The result is show in the form view.
6) The result is show in the form view.

Add Calculated Control in Access

You can use calculated controls in Access databases to display the results of a calculation. For example, if you have a report that displays the number of items sold and the price of each unit, you can add a calculated text box that multiplies those two fields to display the total price. The Control Source property of the calculated text box contains an expression that multiplies two fields (the number of items times the unit price) to obtain the result.
This procedure helps you create a calculated control without using a control wizard.
  1. Right-click the form or report in the Navigation Pane, and then click Design View Button image.
  2. On the Design tab, in the Controls group, click the tool for the type of control you want to create.
    Calc Control
    Access Calculation Contorl

    For a list of control types that can be used as calculated controls, see the section Learn which types of controls can be used as calculated controls.
  3. Position the pointer where you want the control to be placed on the form or report, and then click on the form or report to insert the control.
  4. If a control wizard starts, click Cancel to close it.
  5. Select the control, press F4 to display the property sheet, and then type an expression in the Control Source property box. To use the Expression Builder to create the expression, click Builder button next to the Control Source property box.
  6. Switch to Form view or Report view and verify that the calculated control works as you expect.

The module wrap-up will review terms and concepts used in this module.

Create Calculated Controls - Exercise

Add a calculated control to the Hours Subform in this exercise.
Create Calculated Controls - Exercise

SEMrush Software