Ms Office Tune Up

Your forms aren’t restricted to just the fields in the underlying data source. Access also enables you to create form controls that perform calculations. For example, you can add a control that shows the current date or time. On a more complex level, given a UnitPrice field and a ListPrice field in a Products table, you can create a control that calculates the gross margin for each product.

You set up a calculated form control by adding a text box to the form and then setting its Control Source property to the formula you want to calculate. This formula can use any of the Access operators, operands, and functions, and it can use the values in both bound and unbound controls. When you switch to Form view, the text box displays the results of the formula for each record.

To add a text box to your form, follow these steps:

1. Choose the Design tab.
2. In the Controls group, click Text Box.
3. On the form, click and drag a rectangle to draw the text box in the size and shape you need. When you release the mouse button, Access adds the text box and an associated label.
4. Edit the label text, as necessary.
5. Click the text box to select it.
6. Choose Design, Property Sheet to display the control’s property sheet. (You can also press Alt+Enter or double-click the control.)
7. Choose the Data tab.
8. Type the formula in the Control Source property. (You can also click … to create your expression using the Expression Builder.)
9. Apply other text box properties, as required.
10. Choose the Format tab.
11. Use the Format property to define the format used to display the result. You can either select a predefined format (such as General Date or Percent) or type a format string.
12. Use the Decimal Places property to define the number of decimal places to display in the result.
13. Close the property sheet.

For example, Figure 1 shows two calculated text box controls. In the Form Header sec­tion, you see a text box that contains the expression =Date(). In the Detail section, the Gross Margin text box has the following formula as its Control Source property:

=([ListPrice] – [UnitPrice]) / [UnitPrice]

In the Form view in Figure 2, you can see that the Form Header text box displays the current date. (Note that I entered the format string mmmm d”, “yyyy in the text box’s Format property to get the date format shown in Figure 2.) In the Detail, the Gross Margin text box shows the results of the formula (I configured this control with the Percent numeric format.)

NOTE

Users can’t edit the text in a calculated text box.They can move the insertion point cursor within the control and then select and copy the control text, but if they try to edit the text, Access beeps the PC’s internal speaker and displays the following in the status bar (where expressionis the expression in the Control Sourceproperty):Control can’t be edited; it’s bound to the expression ‘expres­sion’.

Figure 1 To create a calculated control, enter an expres­sion in a text box’s

Figure 2 In the Form view, the text boxes display the results of the calcula­tions.

Technorati Tags: calculated form control, access 2007

Popularity: 4% [?]