Enter text in calculated controls

        3 votes: *****     5,257 views      1 comment
by Allen Browne, 18 January 2008    (for Access 95+)

Microsoft Access Tips for Serious Users

Provided by Allen Browne, December 2007

Enter text in calculated controls

In a normalized database, you must not store dependent values (such as Quantity times UnitPrice.) You use queries to give you these calculated fields.

But what if the user wants to enter the calculated total? They cannot type into the calculated control, but neither should they need to reach for a calculator to figure out the unit price.

The solution is to add an unbound text box on your form. The user then enters the total, and the software works out the value to store. The user need not even be aware they used an unbound box. To see the example for yourself, download the Techniques sample database (40kb zipped, Access 2000 or later.)

Query screenshot

The query (above) shows the calculated fields: AmountEx, Tax, and AmountInc.
In the subform (below), the user can enter
AmountEx or AmountInc, and it calculates the PriceEachEx to store.

Form screenshot

The table and query fields

We need these numbers in each row of the form:

  1. Quantity
  2. Price Each, excluding tax
  3. Amount excluding tax (Quantity x Price Each)
  4. Tax (Amount x Tax Rate)
  5. Amount including tax (Amount + Tax), i.e. the row total.

The table (tblTransDetail) stores only the fields that are not dependent, i.e.:

  • Quantity:   Number (Double, so it copes with fractional quantities such as 2.4 kilo)
  • PriceEachEx:  Currency - the price each unit of quantity, excluding tax
  • TaxRate:  Number (Double, formatted Percent) - the tax rate, with a Validation Rule that limits it to values between 0% and 40%.

The remaining fields are calculated in the query (qryTransDetail):

  • AmountEx:  CCur(Nz(Round([tblTransDetail].[Quantity]*[tblTransDetail].[PriceEachEx],2),0))
  • Tax:  CCur(Nz(Round([tblTransDetail].[Quantity]*[tblTransDetail].[PriceEachEx]*[tblTransDetail].[TaxRate],2),0))
  • AmountInc:  [AmountEx]+[Tax]

The expressions explicitly round each row to the nearest cent to avoid rounding errors when summing transactions. They also explicitly typecast the result to Currency so JET treats the numbers correctly. CCur() cannot handle Null, so we use Nz() as well.

The interface

The subform (frmTransDetail) shows the rows of a transaction (sale or purchase.) For Unit, Tax, and the column totals, we changed the background color as a visual clue that you cannot type here. But it looks like you can type in AmountEx or AmountInc. How is this possible?

Since you cannot type in the calculated control, an unbound text box takes focus as soon as the user enters the AmountInc control. If the user changes the value, its AfterUpdate event fires, so we calculate the unit price and assign it to PriceEachEx.

This is very simple to use. It works like this:

  1. The unbound txtAmountIncEntry text box sits behind AmountInc. (Send to Back is on the Format menu, or on the Arrange tab of the Ribbon in Access 2007.)
  2. AmountInc has its Tab Stop property set to No, so the unbound box takes focus as the user tabs (or Shift+tabs) through the controls on the form. This causes the unbound box to jump in front of the bound box.
  3. In the Enter event of AmountInc, we SetFocus to txtAmountIncEntry. So, if the bound box gets focus for any reason (e.g. clicked with the mouse), the unbound box jumps in front:
        Private Sub AmountInc_Enter()
        End Sub
  4. In the Enter event of txtAmountIncEntry, we copy the value from AmountInc. This guarantees the unbound box shows the correct value whenever it gets focus:
        Private Sub txtAmountIncEntry_Enter()
            Me.txtAmountIncEntry = Me.AmountInc
        End Sub
  5. In the AfterUpdate event of txtAmountIncEntry, we calculate the price each (subtract the tax, and divide by the quantity), and assign it to PriceEachEx.
  6. Once the PriceEachEx is assigned, the query instantly calculates the AmountEx, Tax, and AmountInc. The bound boxes therefore show the right amounts.
  7. As soon as focus leaves the unbound box, it automatically returns its correct spot (i.e. behind the bound box.)

The really cool part is that the unbound text box jumps forward only on the current row of the form. Consequently the form continues to show the correct values in other rows, whereas an unbound box would normally show the same value on every row of a continuous form.

The whole operation is transparent to the users. They have no inkling that an unbound box jumped in front and we performed a calculation so as to store in the correctly normalized data, not the total they entered.

The AmountEx works the same way in the sample database.

Why this data structure?

If you are struggling to design your own tables, you might want to hear more about why we chose these particular fields in these tables.

Handling tax

A basic rule of normalization says, "Do not store dependent data." It follows that you will not store the Amount (quantity times price each), or the Amount including tax. But what about the tax? How and where is this stored?

Consider these alternatives:

What to store Usage Problems
Tax-inclusive amounts only Suitable only if you do not collect taxes. You cannot report the tax collected, nor your real profits/margins
Tax-exclusive amounts only Suitable only if you believe your government's promise that, "The tax rate will never change." If you add a fixed rate of tax in your query, you cannot cope with a tax rate change in the future.
Tax Amount Complex calculations (tiered rates or provision for fluid amounts/bribes) Breaks normalization rules (dependant on transaction values, so can be stored wrongly.)
Tax Rate, in the header table Suitable only if all items are at the same tax rate. Does not cope if some items are made wholly or partly tax exempt (e.g. unprocessed food items in Australia.)
Tax Rate, in the detail table Recommended Rounding the tax per row (to avoid summation differences) is undesirable if you have many very small transactions (e.g. 1 pencil @ $0.07.)

So, storing the tax rate (rather than amount) with each line item is the normalized solution that copes with the most flexible range of conditions.

Reusing tables

You may have also noticed that the sample database stores both sales and purchases in the one transaction table (tblTrans.) The TransDirection field contains -1 for sales (reducing stock), and 1 for purchases (adding stock.)

The users think of sales and purchases as completely different, so we give them separate forms: frmSale and frmPurchase. These forms are based on queries that choose only the transactions from one direction (qrySale and qryPurchase.) Both forms have a hidden text box that gives the correct Default Value for the TransDirection field.

This arrangement provides a really simple way to calculate stock levels. By multiplying the Quantity of product by the direction (-1 for outgoing, 1 for incoming) and grouping by product, it can tell you how many more you have purchased than sold. Query qryStockLevel illustrates this basic quantity-in-hand calculation.

In a similar way, the table of clients is reused to handle suppliers, carriers, employees, and so on. You enter all the people and companies in the one table (tblClient), and then indicate any special roles they may have in the related table (tblClientRole.) The advantage is that you can connect one foreign key - tblClient.ClientID - to handle sales, purchases, wages, invoices, receipts, and heaps of other things you may need in the future. For example, tblTrans.ClientID works for sales to any client (in frmSale) as well as purchases from suppliers (in frmPurchase), just by limiting the ClientID combo on frmPurchase to those clients who have the supplier role (using lqClientSupplier as its Row Source.)

HomeIndex of tipsTop

Rate this article:  Your rating: PoorYour rating: Not so goodYour rating: AverageYour rating: GoodYour rating: Excellent

This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Enter text in calculated controls'?


johannes hardmeier says...

10 Aug 2009

Excellent tutorial. Thanks a bunch, Allen.
I tried something similar for hours now and could solve it through your code:

In a continuous form I have two dropdowns, one for person, one for Email (SELECT ID, name from tblPerson; SELECT ID,adr from tblEmail).
When the Email-dropdown is entered I want it to show only the emails of the Person in the record (me.person_ID).

So I did a similar thing:
I use two dropdowns one behind each other. The foremost shows all Emails (email.ID bound to me.Email_ID, on entering it I set focus to the one in the back, and set its rowsource to "SELECT ID, adr from tblEmail where Person_ID=me.Person_ID".
On AfterUpdate I update the Email_ID.
Simple, when you know how...

Have your say...

E-mail (e-mail address will be kept private)

Comments require approval before being displayed on this page (allow 24 hours).