Combo Box Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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

David Tilman said:
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
Click to expand...
FK?
 
Back
Top