Adding multiple records in one go to a table from a form

Creating and using forms

Adding multiple records in one go to a table from a form

Postby MHCom » Wed Dec 18, 2019 1:10 pm

Hi,

I have a training database with a table in it called Training Needs. When someone new joins the company, they get added to this database - their general details go in a main table, then this second table Training Needs needs to be updated too. It's a table of training courses that each individual needs to complete for their role. There are 14 potential courses that could be required.

The table is very simple - just a primary ID, person's name and course title. I've created a basic entry form for the table, with list boxes linked to name and course title tables and that all works fine. However in it's current state it means that the person updating the database could have to do this form up to 14 times for each new person!

What I would really like to do is have a form where all the relevant courses are selected and then the form submitted just once, and the Training Needs table is updated with up to 14 new rows from that one form submission.

Is this possible?

Thanks!
OpenOffice 4.1.5 on Windows 7 Professional
MHCom
 
Posts: 16
Joined: Fri Nov 08, 2019 4:55 pm

Re: Adding multiple records in one go to a table from a form

Postby UnklDonald418 » Thu Dec 19, 2019 9:39 pm

Because a person can have multiple training needs and each course can have multiple persons needing it, you have a many to many relationship between persons and courses. In relational databases that requires an intersection table (there are a number of other names that are also used, but functionally they are all the same), which has at minimum 2 foreign keys relating to the two tables. Those foreign keys also serve as a compound Primary Key, which precludes entering duplicates.
I downloaded a sample database with an intersection table PersonXClasses
There are 2 forms showing slightly different approaches, one with a simple selection list, and a second that uses a filter table to select the person.
Attachments
Demo45_StudentsClasses.odb
(24.75 KiB) Downloaded 33 times
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1322
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA


Return to Forms

Who is online

Users browsing this forum: No registered users and 2 guests