Creating Records

[sullyman]

I have one table Employees that contains the EmployeeID (PK), Location & Department fields. All this information is imported in from another Dbase so it comes in like below:

John Doe, 1, 13

As the Location and Department comes in as numbers that are programmed in the other database application i have created another two tables Location and Department which lists the relevant locations and departments and their corresponding codes like below

Locations
1 Ireland
2 UK

Departments
13 Sales
15 Management

I have another table Details where i would like to record data for these employees so i have the PK EmployeeID within Table Employees referencing the FK EmployeeID within Table Details. From this setup, i can create a combo which can select from all the employees.

Is it possible that when a user selects an employee, that on afterupdate, the selection will check what location and department that employee record has from the Employees Table (e.g. 1, 13) and then get the correct name from Departments Table to show the user on the form (e.g. Ireland, Sales) and then record (e.g. 1, 13) in the Details Table. Can i somehow link the Employees Table with the Locations and Departments table so it will bring back the value for the user automatically?

 

[John Big Booty]

If your user is selecting the employee through a ComboBox, simply create a query that collects all the other additional data you wish to add, it is not necessary for this data to be displayed, in the combo at this time, then in the Control Source for your Text Box use;

=ComboName.Column(#) [COLOR="SeaGreen"]'Where # is the an integer representung a column number[/COLOR]

Remember that the column numbers in a Combo Box start at 0 (Zero), so your fourth column will actually be Column(3)

Given that your employee is linked to this information it is only necessary to store the employeeID.

 

[pbaldy]

I would base the combo on a query that joined the tables together so you can include the location and department names as part of the rowsource. Then on your form your textbox can display the names to the user with a control source of:

=ComboName.Column(x)

where x is the column containing the appropriate value. It is zero based, so the first column is 0.