Format field on continuous subform based on user input

  • Thread starter Thread starter Deb Smith
  • Start date Start date
D

Deb Smith

I have a continuous subform that is used to input items and costs. I want to
create the subform so that the user can EITHER enter a tax rate and then
have the tax amount automatically calculated (ie [taxrate]X[[Cost]/100) OR
if the user prefers, to just enter a total tax amount leaving the tax rate
blank.

I would appreciate suggestions on how I can accomplish this task?

Thanks in advance for any and all suggestions.
 
In the table, you should store only the tax rate, not the tax amount.
On the form, you should use 2 different text boxes: one bound to the taxrate
field, and the other unbound so the user can enter a tax amount, and you can
calculate the tax rate to store.

To do that, create a query that calculates the tax amount, by typing this
expression into a fresh column of the Field row in query design:
TaxAmount: CCur(Nz([taxrate] * [Cost] / 100, 0))

Set the RecordSource of your continuous form to this query. Put a text box
on the form to show the TaxAmount. Naturally this text box is read-only.

Now for the trick: Add another unbound text box to the form. Place it so it
overlaps the TaxAmount text box. Choose Send To Back on the Format menu so
it goes behind. Set its Tab Order (View menu) so it slots between the other
boxes. Set the TabStop property of TaxAmount to No, so the focus does not go
there.

In the Enter event procedure of this text box:
Me.txtEnterTaxAmount = Me.TaxAmount

In the AfterUpdate event procedure of this text box:
Me.taxrate = 100 * Me.txtEnterTaxAmount / Me.Cost

When the user moves through the text boxes on the form, the unbound one
takes focus in its turn. When it does, it jumps in front of the other one.
The tax amount is copied into the unbound box as soon as it takes focus. If
the user alters anything, the result of the calculation is written to the
taxrate field.

The trick relies on the fact that Access only brings the unbound box forward
on the current record, and the other rows still show the correct amounts.
The user has no idea they are not actually typing into the calculated
control.
 
Allen

Thank you so much;

It works just great. I had everything right EXCEPT I didn't know the trick
portion. I guess thats why you are the expert and I am the novice.

After days of frustrating attempts, I truly appreciate your detailed,
excellent instructions and advice. Thanks again.

Deb

Allen Browne said:
In the table, you should store only the tax rate, not the tax amount.
On the form, you should use 2 different text boxes: one bound to the taxrate
field, and the other unbound so the user can enter a tax amount, and you can
calculate the tax rate to store.

To do that, create a query that calculates the tax amount, by typing this
expression into a fresh column of the Field row in query design:
TaxAmount: CCur(Nz([taxrate] * [Cost] / 100, 0))

Set the RecordSource of your continuous form to this query. Put a text box
on the form to show the TaxAmount. Naturally this text box is read-only.

Now for the trick: Add another unbound text box to the form. Place it so it
overlaps the TaxAmount text box. Choose Send To Back on the Format menu so
it goes behind. Set its Tab Order (View menu) so it slots between the other
boxes. Set the TabStop property of TaxAmount to No, so the focus does not go
there.

In the Enter event procedure of this text box:
Me.txtEnterTaxAmount = Me.TaxAmount

In the AfterUpdate event procedure of this text box:
Me.taxrate = 100 * Me.txtEnterTaxAmount / Me.Cost

When the user moves through the text boxes on the form, the unbound one
takes focus in its turn. When it does, it jumps in front of the other one.
The tax amount is copied into the unbound box as soon as it takes focus. If
the user alters anything, the result of the calculation is written to the
taxrate field.

The trick relies on the fact that Access only brings the unbound box forward
on the current record, and the other rows still show the correct amounts.
The user has no idea they are not actually typing into the calculated
control.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Deb Smith said:
I have a continuous subform that is used to input items and costs. I want
to
create the subform so that the user can EITHER enter a tax rate and then
have the tax amount automatically calculated (ie [taxrate]X[[Cost]/100) OR
if the user prefers, to just enter a total tax amount leaving the tax rate
blank.

I would appreciate suggestions on how I can accomplish this task?

Thanks in advance for any and all suggestions.
Click to expand...
Click to expand...
 
Back
Top