Update Calculated Textbox

Question

On the main form (Invoice), I have a drop down combo box that is populated with counties. On the subform (InvoiceDetailsSubform) I have the following: Qty, Unit Price, Total Price, and Tax. After the County is selected and the Qty and Unit Price are entered, I tab over to the Total Price and it is automatically filled in. The Tax textbox has a If Then Else statement to figure out the tax. When I tab over to that, the tax is automatically filled in. As it is setup now, if someone changes the county, the only way for the tax to recalculate it is to tab through. Is there any way for this to automatically happen after the county is changed?

Answer 1

First, I would not use the code you have. That code is very inefficient. Every time the tax rate changes you have to rewrite your code. What you should do is have a Taxrate field in your County table. Your RowSource for the combo should include the taxrate field. Then your code becomes simple:

SalesTax = TotalPrice * County.column(x)

If you need to check the Dept, then wrap it around that.

Answer 2

It sounds like you are calculating the tax per line item. That what it does based on which Dept is selected.

Some items are taxable and others are not.

What are the controlsources for the Total Price and Tax controls? Are there any code snippets behind those 2 controls?  Total Price is =[Qty]*[UnitPrice]

                   Sales Tax = Sales Tax

I tried "Me.subformname.Refresh " and got the following:

Compile Error: Method or Data Member Not Found.

Here is some more information:

The following is my If Then Else statement:

Dim County

County = Forms("Invoice").County

Dim Tax

If Dept = "Carstops" Or Dept = "Portabases" Or Dept = "Portabase Rental" Or Dept = "Signs" Then

    If County = "Racine" Or County = "Waukesha" Then

        Tax = 0.051

    ElseIf County = "Milwaukee" Or County = "Ozaukee" Or County = "Washington" Then

        Tax = 0.056

    ElseIf County = "Calumet" Or County = "Manitowoc" Or County = "Outagamie" Or County = "Sheboygan" Or County = "Winnebago" Then

        Tax = 0.05

    ElseIf County = "Exempt" Then

        Tax = 0

    Else

        Tax = 0.055

    End If

End If

SalesTax.Value = Format(TotalPrice * Tax, "Currency")

Here are the fields on my Subform:

Qty - Qty_LostFocus has the If Then Else statement. (Figures tax when the quantity is changed)

Dept - Dept_GotFocus has Me.Recalc (Recalculates the Total Price)

UnitPrice - Price per item.

Total Price - (Data - Contol Source)  =[Qty]*[UnitPrice]

                    TotalPrice_GotFocus has the If Then Else statement. (Figures tax when the Unit Price is entered or changed)

SalesTax - (Data - ControlSource) = SalesTax

Hope this helps!!

Answer 3

I tried using "Me.MyTextBox.Requery" and I got the following: Compile Error: Method or Data Member Not Found.

"But what happens if someone changes the Country but not does not update the Unit Price or the Qty ?" Just the County changes. What I want to do is have the Tax recalculated based on that change. After it is changed, without having to tab through all the fields of each line item.

Here is some more information:

The following is my If Then Else statement:

Dim County

County = Forms("Invoice").County

Dim Tax

If Dept = "Carstops" Or Dept = "Portabases" Or Dept = "Portabase Rental" Or Dept = "Signs" Then

    If County = "Racine" Or County = "Waukesha" Then

        Tax = 0.051

    ElseIf County = "Milwaukee" Or County = "Ozaukee" Or County = "Washington" Then

        Tax = 0.056

    ElseIf County = "Calumet" Or County = "Manitowoc" Or County = "Outagamie" Or County = "Sheboygan" Or County = "Winnebago" Then

        Tax = 0.05

    ElseIf County = "Exempt" Then

        Tax = 0

    Else

        Tax = 0.055

    End If

End If

SalesTax.Value = Format(TotalPrice * Tax, "Currency")

Here are the fields on my Subform:

Qty - Qty_LostFocus has the If Then Else statement. (Figures tax when the quantity is changed)

Dept - Dept_GotFocus has Me.Recalc (Recalculates the Total Price)

UnitPrice - Price per item.

Total Price - (Data - Contol Source)  =[Qty]*[UnitPrice]

                    TotalPrice_GotFocus has the If Then Else statement. (Figures tax when the Unit Price is entered or changed)

SalesTax - (Data - ControlSource) = SalesTax

Hope this helps!

Answer 4

It sounds like you are calculating the tax per line item. Is that because some items are not taxable or taxed at different rates? Generally tax is calculated on the entire purchase, not each item.

What are the controlsources for the Total Price and Tax controls? Are there any code snippets behind those 2 controls?

Have you tried a Me.subformname.Refresh in the After Update event of County control?

Answer 5

The If calculation should be set as the control (in the properties box) - it "should" then update as you tab through the form.

Or you can use the AfterUpdate event of the Country combo, like this

Private Sub MyCombo_AfterUpdate()

Me.MyTextBox.Requery

End Sub

(Change the names to what they really are on your form)

This will only work in your situation if the MyCombo is the last "bit" of the calcualtion.

So if you:

Change th Combo

THEN Change Qty

THEN Change the Unit Price

you would need t use the after update event of the Unit Price text box.

But what happens if someone changes the Country but not does not update the Unit Price or the Qty ?

Can you give us some more information on how you form runs (from a user viewpoint) then someone here should be able to offer some advice.