Problems with subforms

[Nikki]

Hi,

I have a form with employees Name etc and then a subform with courses they
have attended. I have linked the forms by EmployeeID.

When I enter a new course into the subform it allows me to select the course
from a drop down box but then does not allow me to enter the start date.
Then when I try and navigate anywhere else I get the error message "The
changes you requested to the table were not successful becasue they would
create duplicate values......"

How do I know what I need to change?

 

[Douglas J. Steele]

What are the details of the tables behind the form and subform?

That's what's stopping you.

 

[mattc66 via AccessMonster.com]

Check the EmployeeID feild in your subform table and make sure the indes is
set to allow duplicates.

 

[Nikki]

I have checked and all fields except the primary key in each table are set to
alow duplicates

 

[Nikki]

Underlying Tables

TblEmployees
(PK) EmployeeID
EmployeeFirstName
EmployeeLastName
EmployeeStartDate
EmployeeEndDate
EmployeeTeam
EmployeeBirthday

TblCourses
(PK) CourseID
CourseName
CourseCost
CourseDurationDays
LookupEmployeeID

TblEmployeeCourses
(PK) EmployeeCourseID
CourseStartDate
CourseReport
LookupEmployeeID
LookupCourseID

Main Table is made with TblEmployees

Underlying Query for subform
QryCourses
EmployeesID (TblEmployees)
EmployeeFirstName (TblEmployees)
EmployeeLastName (TblEmployees)
CourseName (Tbl Courses)
CourseCost (Tbl Courses)
CourseDurationDays (Tbl Courses)
CourseStartDate (TblEmployeeCourses)
CourseReport (TblEmployeeCourses)

Subform linked to main form by Employee ID fields.

Any ideas on what I need to change. Or do I need to change the whole
structure?

 

[Douglas J. Steele]

Those tables look correct (although I'd want to have a unique index on the
combination of LookupEmployeeID and LookupCourseID in TblEmployeeCourses.
(or, if employees can take the same course more than once, on the
combination of LookupEmployeeID, LookupCourseID and CourseStartDate)

What is EmployeeCourseID: an AutoNumber, or are you assigning it its value?
If it's not an AutoNumber, are you sure you're assigning new values
correctly?

I know that elsewhere in this thread, you stated that none of the indexes
other than the Primary Keys are unique, but you're not going to get that
error message unless you're inserting data that violates a unique index.