MS Access Table>Query>Form Design Issue

Question

I have two tables, a query, and a form and I can't get it to behave like I want. There is a relationship between Table 1 and Table 2 formed between Employee ID (Table 1) and Employee (Table 2)

Employees (Table 1)

• Employee ID (Key)
• Employee Name
• Status

Attendance (Table 2)

• Attendance ID (Key)
• Employee
• Date
• Note

Employee Query

• Returns Employee ID and Employee Name for any employee with Status="Active"

Attendance Form (This form is used to enter records into Table 2)

• Attendance ID (Table 2)
• Employee (Drop down list from Employee Query)
• Date
• Note

Essentially what I want is for the form to work like so:

• Dropdown to display Employee names that are "Active"
• Save Employee ID back to Table 2
• Display Employee name rather than ID in the form

The issue that I'm having is that the form wants to display the Employee ID rather than the name. This isn't practical for data entry but I still want the form to send the Employee ID to the table so we can run queries and reports.

Answer 1

Advise not to use spaces in naming convention.

Set combobox properties like:

ControlSource: Employee
ColumnCount: 2
ColumnWidths: 0";1"
BoundColumn: 1
RowSource: SELECT EmployeeID, EmployeeName AS FullName FROM Employees ORDER BY EmployeeName WHERE Status = 'Active';

This will display name but save id.

Employee name parts really should be in separate fields: FirstName, MiddleName, LastName. Then RowSource like:
SELECT EmployeeID, LastName & ", " & FirstName & " " & MiddleName AS FullName FROM Employees WHERE Status='Active';