Brain Freeze - Viewing name instead of ID in a form

[jlancton]

I have a data entry form where Employee details are entered, such as name, and Department. Department is selected from a combo box which gets its row source from a table of departments. The combo box displays the name rather than the ID number.

There is also a form which displays the selected employee, with their details, and has list boxes for in-services available and taken.

The problem I have is, when displaying the employee details, the department field displays the ID number, not the name. I know it must be easy, but I can't remember how to get the name from the departments table.

Any help is greatly appreciated.

Thanks!!

-Jeff

 

[SteveR77]

Jeff,

If you are storing the ID numbers and not the names for the department you will need to set up a relationship that will join the ID for the depart in the employee Table to the ID in the department table that has the department name.

Once that is done in your query, report, etc you can add the field for the department name.

 

[jlancton]

I created the relationship as you described, but it still populates the field with the id number instead of the name. I think it's because the form is based on the table that stores only the ID, not the name, so I don't know how to add the departments table to the form so that I can pull the name field from it.

-Jeff

 

[JediBMC]

The simplest way around this is to base the table off of a query that links these two tables. To do this, simply change the record source of the form to look something like this:

SELECT Employee.*, Department.* FROM Employee Right JOIN Department ON Employee.DeptID = Department.DeptID;

This will expose all of the fields from both tables and still allow you to edit the entries in the Employee table.

If you do not wish to have the form based off of a query, you can also make the department field a combo box (as you did on the original form). Be sure to include both the DeptID field and the DeptName field in the row source for the combo box. Also be sure to set the column width for the DeptID column to 0". The properties for the combo box should look something like this:

Control Source: DeptID

Row Source Type: Table/Query

Row Source: SELECT Employee.DeptID, Department.DeptName FROM Department LEFT JOIN Employee LEFT JOIN Department ON Employee.DeptID = Department.DeptID;

Column Count: 2

Column Widths: 0";1.5"

I hope this helps!

-Brian-
I'm not an actor, but I play one on TV.