Search (if not found: add) in a table on a subform (1 Viewer)

[kstaelens]

Hi!
In short: I've got a database where I put in details of people who followed courses and with some macro's and links to Word, I can send them their certificate automaticly by mail.
To explain the issue I have, I'll give you the necessary details of the "impacted" details:

• Three tables:
  • Courses: details of each course (fields: CourseID and Course)
  • Pupils: details of all participants, unique and no multiple entries (fields: PupilID, First Name, Last Name, DateOfBirth, Birthplace)
  • Participants: Add each participant for each course (fields: ParticipantID, PupilID, CourseID, Certificate)
• I've made a query to combine the three of these tables (CoursesDetails)
• There is a form, "Input" where I fill in the details of each course. I added a subform which is linked to the query I made before.

If I add details for a course; I open the form, first give the details of the course and in the subform I want to add all participants.

My problem: When entering the details of a course; I'd like Access to search for existing details of a pupil when entering a name; if he doesn't find it, I'd be able to add it. Of course the purpose is to keep my Pupils-table clean, with unique entries. I've searched Youtube, googled it and asked ChatGPT, but I'm not able to create the function I want.

I've added a simplified file, to show what it is I want, would be great if I could make it working

 

[MajP]

See solution:
Your subform is not correct. You want a pulldown list and base it on participants. You are selecting students not creating students.

But then you need a way to add a new student to the database.

 

[kstaelens]

See solution:
Your subform is not correct. You want a pulldown list and base it on participants. You are selecting students not creating students.

But then you need a way to add a new student to the database.

View attachment 112011
View attachment 112014

Thanks a lot! You made my day!

 

[MajP]

@kstaelens,
Your subform is a many to many subform. These many to manys are somewhat tricky to do unless you have seen it before. But the technique I show is the same for any many to many. A junction table has two (or more) foreign keys. Table Participants is the junction table with a CourseID foreign key and a Person ID foreign key. The subform is always based on the junction table and linked to the main form by one of the foreign keys. The other foreign key gets a combobox to select from.

With that said you can try to create the alternate view yourself. Make another form based on the same junction table where the main form is a Student. Link the subform to student ID. Now put a combo on Course ID so you can select a course. Now you can enter data either way. Pick a student in the main table and then add their courses. Or the other form allows you to pick a course and add students.

 

[jamesave]

@kstaelens,
Your subform is a many to many subform. These many to manys are somewhat tricky to do unless you have seen it before. But the technique I show is the same for any many to many. A junction table has two (or more) foreign keys. Table Participants is the junction table with a CourseID foreign key and a Person ID foreign key. The subform is always based on the junction table and linked to the main form by one of the foreign keys. The other foreign key gets a combobox to select from.

With that said you can try to create the alternate view yourself. Make another form based on the same junction table where the main form is a Student. Link the subform to student ID. Now put a combo on Course ID so you can select a course. Now you can enter data either way. Pick a student in the main table and then add their courses. Or the other form allows you to pick a course and add students.

Hi MajP,

Appreciate your reply and comments in this thread. I've learned quite a lot from poking inside your example file.

One question that I can't get the answer is: how does the participants subform able to limit the input data to only one specific student only? In other words, it won't allow multiple instances of the same student. I looked at the table design, it's only have participantID as the primary key. Is it because the Row Source of the cmboPupil?

 

[MajP]

In Access you can make a composite index. This is not the same as a composite key.
Go to indexes in the table design view
Give it a name "Student_Course"
Then list the fields next to it that make up the key.
Select the Index and pick "Unique".
Now you cannot add the Same Person and Same Course twice.

Contact Support

 

[jamesave]

Thank you! This is very useful. I have always wondered why in the world of MS access users, they like to create autonumber as the only key.