Combo Box Problem

[David Tilman]

I having trouble getting a combo box to behave properly when dealing with two
related tables. I have two tables setup like this:

Schedule Table:
ScheduleID (PK)
ScheduleDate
EmployeeID (FK to Employees table)

Employees Table:
EmployeeID
Name

I have created a form that has a text box that shows the date field from the
"Schedule" table and a combo box. I want the combo box to list the names from
the employees table, but when a user selects a name I want it to update the
EmployeeID foreign key in the "Schedule" table.

The form's record source looks like this:

SELECT Schedule.ScheduleDate, Employees.Name, Schedule.EmployeeID
FROM Employees INNER JOIN Schedule ON Employees.EmployeeID =
Schedule.EmployeeID
ORDER BY Schedule.ScheduleDate;

How can I get the combo box to just show names, but update the EmployeeID FK?

 

[Brian Bastl]

Hi David,

Your form's recordsource should be based only on your Schedule Table.

SELECT ScheduleID, ScheduleDate, EmployeeID
FROM
[Schedule Table]

The rowsource for your EmployeeID combo would get its values from the
Employees table.

SELECT EmployeeID, [Name]
FROM
[Employees Table]

Bound Column =1
Column Count = 2
Column Widths = 0"; 1.5"

'Name' is a reserved word in Access, and will cause you untold grief. Better
change it to something like EmpName.

HTH,
Brian

I having trouble getting a combo box to behave properly when dealing with two
related tables. I have two tables setup like this:

Schedule Table:
ScheduleID (PK)
ScheduleDate
EmployeeID (FK to Employees table)

Employees Table:
EmployeeID
Name

I have created a form that has a text box that shows the date field from the
"Schedule" table and a combo box. I want the combo box to list the names from
the employees table, but when a user selects a name I want it to update the
EmployeeID foreign key in the "Schedule" table.

The form's record source looks like this:

SELECT Schedule.ScheduleDate, Employees.Name, Schedule.EmployeeID
FROM Employees INNER JOIN Schedule ON Employees.EmployeeID =
Schedule.EmployeeID
ORDER BY Schedule.ScheduleDate;

How can I get the combo box to just show names, but update the EmployeeID

FK?