Combo Box Fills In Calculated Text Box

[Maurita]

Hi all, hope someone can help me with a problem which I have been
researching and have not found an answer to. I am working on a Salary
and Tax form which pulls salary and tax info from a table with the same
name. I have a combo box (Combo35) that pulls info from the Employee
Table (EmployeeID, LastName, FirstName, County, Wage). This
information I place in various text boxes (=Combo35.Column(?)) which
works great. The problem I am having is that I need to pull the
"County" from Combo35 and place it in the CountyTax text box which
calculates a value based on which County was selected in Combo35. I
have written the following code under the After Update event in the
HoursWorked text box. When the user types in the hours the total is
automatically calculated in a TotalPay field. I thought that having
the following code in the same area would be where it was needed.
Unfortunately, the code for the CountyTax isn't working. Can someone
please help me figure out what I am missing. I also need to round to
two decimal points but have not worked on this because I want to get
the code to work first. Thank you in advance for any help you can
give. Maurita Searcy

County Criteria: Lawrence County Tax = 0.0100
Morgan County Tax = 0.0127

If Me!Combo35.Column(4) = "Lawrence" Then
CountyTax = TotalPay / 0.0100
ElseIf Me!Combo35.Column(4) = "Morgan" Then
CountyTax = Total Pay / 0.0127

 

[Al Campagna]

Maurita,
First, the CountyTax Rate should be stored along with the CountyName in your table. If
a county changes it's tax rate, you only need to update the County table, and all
calculations from then on will use that rate. If you "hard-wire" the numbers, as you have
in your code below, you'll have to recode that expression whenever any County Tax changes.
example...

tblCounties
CountyName CountyPayrollTaxRate
Lawrence .0100
Morgan .0127
Davis .0130 etc... etc...

Once you've done that, include that field in a column of Combo35 (should be named
something like cboCounty rather than combo35)
Let's say for example that cboCounty has 2 columns... County Name (in Col 0) and
CountyPayrollTaxRate (in Col 1)
then...

CountyTax = Total Pay * cboCounty.Column(1))

will always calculate the County Tax according to cboCounty selection.

 

[Maurita]

Al, thank you so much for your help. I tried your suggestion and it
works, but it's not working as it needs to. The County table does have
the county name and tax rate. Within the Employee table, there is a
field titled County which holds the employee's county of residence.
Within the "Employee Salary and Tax Form", when the combo box pulls in
the employee's name, it also pulls in the employee's county of
residence. I need to reference the county tax rate in the "CountyTax"
field with the appropriate county tax rate because I must reference the
employee's county of residence on the "Employee Salary and Tax Form".
The way you referenced I create the code worked, but in order to know
what county the employee lives in, I had to reference it in a text box
from a combo box. Then, I had to choose from a list the county again,
to make your code work.

Is there a way I can use code that pulls the employee county (text box
code is: =Combo35.Column(4)) and calculate into text box "CountyTax" as
referenced in my original topic?

Thank you.

Maurita

 

[Al Campagna]

Maurita,
First, your combo should be selecting an EmployeeID. Using EmpName may be unique
enough now, but later it could/may cause problems.

You certainly can use more than one table as the Source for your combo (ex. cboEmpID)
Try this setup for the query behind your EmpID combo...

tblEmployees tblCounties
EmpID | ------- County
EmpName | Tax Rate
EmpCounty ---link---|

cboEmpID is bound to EmpID field
Combo Cols = EmpID, EmpName, County, TaxRate
Col Count = 4
Col Widths = 0" ; .5" ; 1.25", .5"
(first col must be 0, adjust other widths to suit)

This setup allows users to select by name, displays that name in the combo, but
actually stores the EmpID in the EmpID field.
Your form will need to capture the Tax Rate (ex. EmpCountyTaxRate) every time you fill
in a new payroll record... because TaxRate may change in the future.
So... using the AfterUpdate event of cboEmpID...
EmpCountyTaxRate = cboEmpID.Column(3)

Note: If you are involved with more than one state, just the County name might not be
unique enough to ralte to the right TaxRate. Different states can have duplicate County
names... In that case, connect the two tables as above, but also add a link between
tblEmployees/EmpState, and tblCounties/State. That should prevent dupes, and keep the
realtionship unique.
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions