Auto Populate

[Guest]

I have 3 tables.: tblexpenses, tblemployees, tbldistricts. I have created a
form where someone enters the expense, the employee number (drop down) and
the district (drop down). I would like the district to be auto populated
based on the employee number.

 

[John Vinson]

I have 3 tables.: tblexpenses, tblemployees, tbldistricts. I have created a
form where someone enters the expense, the employee number (drop down) and
the district (drop down). I would like the district to be auto populated
based on the employee number.

Where in your database is there a connection between the employee
number and the district?

If knowing the employee number reliably identifies the district,
should the district even be included in whatever table this form is
based on? Wouldn't it be redundant?

John W. Vinson[MVP]

 

[Guest]

The main reason for the district is to run a reports stating what the expense
are for each district. There are 70 employees and 10 districts. In my form, I
have the adjustername and district IDs as drop downs. I would like the
district id to auto fill based on the adjuster number chosen. Thanks

tblEmployees
EmployeeID (primary)
DistrictID

tblDistricts
DistrictID (primary)
District Name

tblexpenses
adjusterid
districtid
companyid
expense

 

[John Vinson]

The main reason for the district is to run a reports stating what the expense
are for each district. There are 70 employees and 10 districts. In my form, I
have the adjustername and district IDs as drop downs. I would like the
district id to auto fill based on the adjuster number chosen. Thanks

tblEmployees
EmployeeID (primary)
DistrictID

tblDistricts
DistrictID (primary)
District Name

tblexpenses
adjusterid
districtid
companyid
expense

Is the Form based on tblExpenses? Does the EmployeeID have anything to
do with tblExpenses - e.g. is AdjusterID a link to EmployeeID? Can
they vary independently?

You could use the AfterUpdate event of the combo box based on the
employee table to "push" the DistrictID. Include it in the Combo's
Query, and use code like

Private Sub cboEmployee_AfterUpdate()
Me.cboDistrict = Me.cboEmployee.Column(n)
End Sub

where cboEmployee is the name of the employee combo box and n is the
zero based index of the districtID in the combo's rowsource query.

John W. Vinson[MVP]

 

[Guest]

I tried the after update with no success. I put a 2 instead of an "n" since
the employee drop drop is 2 columns and the 2nd column is the district id. Do
I also need to change the rowsource? Thanks again.
Private Sub cboemployee_afterupdate()
Me.cboDistrict = Me.cboEmployee.Column(1)
End Sub

 

[John Vinson]

I tried the after update with no success. I put a 2 instead of an "n" since
the employee drop drop is 2 columns and the 2nd column is the district id.

It's *zero based* as I said. The first column is (0), the second is
(1).

John W. Vinson[MVP]

 

[Guest]

Ok. I have figured out how to auto populate my combo box based on another
combo box. However, two problems, it is not saving the info into the table as
it was when it was not auto populating.

The way I auto populate : controlsourceproperty - =[cboemployees].column(1)
How do I get the table to save this info. I tried putting the name of the
table column in front of "cboemployee" no success. Help!

 

[John Vinson]

Ok. I have figured out how to auto populate my combo box based on another
combo box. However, two problems, it is not saving the info into the table as
it was when it was not auto populating.

The way I auto populate : controlsourceproperty - =[cboemployees].column(1)
How do I get the table to save this info. I tried putting the name of the
table column in front of "cboemployee" no success. Help!

That's just DISPLAYING the value from the second column of
cboemployees. The control source property can either be the name of a
table field (to store and edit the data in that field) or it can be an
expression like this.

If the value of this control can always be derived from the Employees
table, isn't it redundant? Why do you want to store it AT ALL, given
that you can (apparently) just use a combo box, or a Query joining the
tables, or a DLookUp to find it? What is this field; where are you
trying to store it; and WHY?

John W. Vinson[MVP]