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.)

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.

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()
           Me.txtAmountIncEntry.SetFocus
       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:

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.)