Cannot add record(s); join key of table " " not in recordset

[mldr]

Error: "Cannot add record(s); join key of table " --- " not in recordset."

Three tables are involved:

Course (Table 1)
CourseID (Primary Key) This is NOT an auto number
There are many other fields in this table, but none of them appear in or have a relationship with the other two tables mentioned below.

Employees (Table 2)
Emp_id (Primary Key) This IS an Auto Number
EmpLastName
EmpFirstName
Many other fields not in or related to Course or TrainingScheduleActual Tables

TrainingScheduleActual (Table 3)
Emp_id (Primary Key)
CourseID (Primary Key)
Employee (Combo Box) SELECT DISTINCTROW [Employees].[Emp_Id], [empLastName] & ", " & [empFirstName] AS [Employee] FROM [Employees] ORDER BY [empLastName] & ", " & [empFirstName];
These 3 fields are the only ones in the table

The relationship of TrainingScheduleActual to Course and Employees is One-to-Many. An Employee can take many Courses, and a Course can have many Employees

I have a Courses form, with all the fields from the Courses Table

I have a Courses subform with Master & Child fields being Course ID
The Record Source for the subform is:

SELECT DISTINCTROW [TrainingScheduleActual].[Employee], [Employees].[Organization], [Employees].[Location], [Employees].[Role], [TrainingScheduleActual].[CourseID] FROM [Employees] INNER JOIN [TrainingScheduleActual] ON [Employees].[Emp_Id] = [TrainingScheduleActual].[Emp_Id];

The Courses subform contains a list of Employees who participated in that Course. The data there now was copied from another database into the TrainingScheduleActual.

However, I can’t add Employees to the subform. I need to be able to select Employees from a drop down list in the subform, and add them to the TrainingScheduleActual, but am getting "Cannot add record(s); join key of table "TrainingScheduleActual" not in recordset."

I have been able to get the subform to allow new records, but when that works, all of the Employee names that were copied from the other database disappear, although the Location, etc remain. (I did not keep track of exactly what changes I’ve tried.)

Can anyone tell what I’m doing wrong? Sorry if this is too wordy. Thanks in advance for any ideas.

 

[jebry]

Hi!

Just add the EmpId to the recordset:

SELECT DISTINCTROW [TrainingScheduleActual].[Employee], [Employees].[Organization], [Employees].[Location], [Employees].[Role], [TrainingScheduleActual].[CourseID] FROM [Employees] [TrainingScheduleActual].[Imp_Id] INNER JOIN [TrainingScheduleActual] ON [Employees].[Emp_Id] = [TrainingScheduleActual].[Emp_Id];


This seems to be what Access is wanting.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[mldr]

Hi Jeff,
I tried your suggestion, and it is partially fixing the problem. Now there is a new error message though. Here's the new query
SELECT DISTINCTROW [TrainingScheduleActual].[Employee], [Employees].[Organization], [Employees].[Location], [Employees].[Role], [TrainingScheduleActual].[CourseID], [TrainingScheduleActual].[Emp_id] FROM [Employees] INNER JOIN [TrainingScheduleActual] ON [Employees].[Emp_id] = [TrainingScheduleActual].[Emp_id];

I'm now able to select an employee on the subform, but it won't update the record. This error appears.

The Microsoft Jet database engine can't find a record in the table 'Employees' with key matching field(s) 'Emp_id'.

Any ideas? Thanks so much for your time.