Format field on continuous subform based on user input

[barit]

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.

 

[Trendsetter]

You could use code to detect an entry in the TaxRate field and then invoke code to calculate the final amount... something like:
In the txtTaxCode AfterUpdate event..
If Me.txtTaxCode > 0 Then
Me.FinalCost = (Me.txtTaxCode/100) * Me.txtCost
Me.txtTaxCode = ""
End if
I have cleared the taxcode field because in a continuous form if a field is not bound to a data field then every entry will be given the same value as the entered value.

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[barit]

Thanks for the suggestion. I appreciate the time and effort. In the end this is how I solved the problem.

In my query I created a field called TaxAmount
TaxAmount: CCur(Nz([taxrate] * [Cost] / 100, 0))

As you had suggested I had the TaxRate as a bound field on the form and now also TaxAmount as a bound field.

I added an unbound text box(txtEnterTaxAmount) to the form and placed it so it
overlaps the TaxAmount text box. I chose Send To Back on the Format menu so
the txtEnterTaxAmount box goes behind the taxAmount Box. I also set the tab Order so txtEnterTaxAmount 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 the txtEnterTaxAmount text box:
Me.txtEnterTaxAmount = Me.TaxAmount

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

It works.

Anyway thanks again for all your help.