Solved new records not added through the sub form (1 Viewer)

rik59

New member
Local time
Today, 12:03
Joined
Jun 17, 2014
Messages
16
Hi, I'm going round in circles with this problem and I need some help, please. I am creating a many to many relationship in my Staff --- Courses database. I have a Staff table with StaffID as the Primary key, a courses table with CourseID as the Primary key and a Junction table with its own ID as the Primary key and StaffID and CourseID as the two foreign keys. I have created a main form with Staff names etc from the Staff table and a sub form from the courses table and Junction table. The courses titles (only 6 of them) are in the form of a combo box. When I cycle through the form with existing records it all works fine but whenever I try to add a new record it will allow me to add a new staff name but the course form won't allow me to choose an existing course and assign it to the new staff member. The Parent/Child links seem OK - at least it generates a member of staff with up to 6 courses assigned to them as an existing record. It just won't accept any new records in through entry in the sub form.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:03
Joined
Oct 29, 2018
Messages
21,357
Hi. Is your form/subform setup like this?

The main form is bound to the Staff table
The subform is bound to the Junction table
The combobox Row Source is based on the Courses table
 

rik59

New member
Local time
Today, 12:03
Joined
Jun 17, 2014
Messages
16
Hi. Is your form/subform setup like this?

The main form is bound to the Staff table
The subform is bound to the Junction table
The combobox Row Source is based on the Courses table
Thanks for a prompt reply. The main form is bound to the staff table, the subform is bound to a staff courses query and the combo box is based on the courses table
 

rik59

New member
Local time
Today, 12:03
Joined
Jun 17, 2014
Messages
16
Thanks for a prompt reply. The main form is bound to the staff table, the subform is bound to a staff courses query and the combo box is based on the courses table
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:03
Joined
Feb 19, 2013
Messages
16,553
what values do you have in the subform linkchild/master properties?
 

rik59

New member
Local time
Today, 12:03
Joined
Jun 17, 2014
Messages
16
Link Master Field = Staff ID
Link Child Field = Staff ID
using the Staff course Query
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:03
Joined
Feb 19, 2013
Messages
16,553
a sub form from the courses table and Junction table.
Should only need the junction table - the combo will provide for the courses table
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:03
Joined
Oct 29, 2018
Messages
21,357
Thanks for a prompt reply. The main form is bound to the staff table, the subform is bound to a staff courses query and the combo box is based on the courses table
Can you post the subform query's SQL statement?
 

rik59

New member
Local time
Today, 12:03
Joined
Jun 17, 2014
Messages
16
Hi Great idea, and I have modified my design but although I am still getting all the existing records showing up as they should now when I go to assign a course to a new staff member I get the course ID number instead of the course title but at least I can add a course
 

rik59

New member
Local time
Today, 12:03
Joined
Jun 17, 2014
Messages
16
SQL statement: Sorry for the delay wasn't 100% sure what to look for but here it is:

SELECT StaffTable.StaffID AS StaffTable_StaffID, StaffTable.FirstName, StaffTable.Surname, StaffTable.Status, StaffTable.Role, StaffTable.Department, StaffTable.LineManager, StaffTable.[Start/LeavingDate], StaffTable.OnAstute, StaffTable.Notes, JoiningTable.ID, JoiningTable.StaffID AS JoiningTable_StaffID, JoiningTable.CourseID, JoiningTable.CompletionDate, JoiningTable.RenewalDate
FROM StaffTable INNER JOIN JoiningTable ON StaffTable.[StaffID] = JoiningTable.[StaffID];
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:03
Joined
Feb 19, 2013
Messages
16,553
I get the course ID number instead of the course title
that is down to your combo setup - typically would expect something like

controlsource: courseID
rowsource: SELECT CourseID, CourseName FROM tblCourses ORDER BY CourseName
bound column: 1
column count: 2
column widths: 0
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:03
Joined
Oct 29, 2018
Messages
21,357
SQL statement: Sorry for the delay wasn't 100% sure what to look for but here it is:

SELECT StaffTable.StaffID AS StaffTable_StaffID, StaffTable.FirstName, StaffTable.Surname, StaffTable.Status, StaffTable.Role, StaffTable.Department, StaffTable.LineManager, StaffTable.[Start/LeavingDate], StaffTable.OnAstute, StaffTable.Notes, JoiningTable.ID, JoiningTable.StaffID AS JoiningTable_StaffID, JoiningTable.CourseID, JoiningTable.CompletionDate, JoiningTable.RenewalDate
FROM StaffTable INNER JOIN JoiningTable ON StaffTable.[StaffID] = JoiningTable.[StaffID];
Hi. Thanks for posting this, but I thought you said you already changed your design where the subform is now simply based on the junction table?
 

rik59

New member
Local time
Today, 12:03
Joined
Jun 17, 2014
Messages
16
Hmmm... Looking at the subform now, it looks like it is based on the Course table and the Junction table as it has the fields Course ID and Course Title (from the Course table) and the ID, Staff ID and Course ID as well as Completion Date and Renewal Date (all from the Junction table)
 

Users who are viewing this thread

Top Bottom