Bulk Course Registration Form (1 Viewer)

[ebuckner]

Hi all,
First time post so please stay with me if I don’t make that much sense!

I work in the IT team of a school, another department who run training courses for teachers from across the area recently approached me about creating a small Db for them as their information is currently stored in some crazy spreadsheet that only one person understands. Each teacher has to take part in a set amount of courses across a year to gain a qualification. Currently the spreadsheet just lists all of the courses available as columns and they are coloured in when taken, this system is ridiculously hard to follow as you are looking at maybe 200 or 300 teachers, hence the need for the Db.

I have managed to create most of the Db with my limited knowledge and it looks ok, however when I took it down to the department they asked if it was possible to add a bulk course register feature instead of registering each teacher onto a course one at one time. I immediately said yes thinking that can’t be hard I’ve seen it done in multiple programmes and software packages over the years, however I seem to have either hit a mental block or I’m just not clever enough to work it out!

I have this idea that you would pick the course you want from a drop down and then have a listbox with all teachers and a listbox of the current course members and be able to add and take away as you see fit on a single or multiple choice basis. Obviously this is just an idea but if anyone has a better or easier solution please shout me down.

So for reference I have three tables that are relevant to this, Contact (stupid name should have been student or teacher I realise), Course and Enrolment

Contact has ID as its primary key and then several other fields like forename and surname.

Course has Course Name as its primary key. This is the only field in the table, there isn’t much more that needs to be stored about the course as of yet this may change but the name will always be unique.

Enrolment has Enrolment ID as its primary key. It has ID (link to contact), Course Name (link to course) and Course Complete (yes / No tick box).

Hope this gives enough information. Any help would be appreciated.

Cheers in advance

Elliott

 

[CJ_London]

you might find this multi select form useful

https://www.access-programmers.co.uk/forums/showthread.php?t=289116

the recordsource would be your contacts and your filter button can have code behind it to insert the required records in your enrolment table

 

[Ranman256]

if its any help, look at my classroom schema and take anything needed,

 

[ebuckner]

Thank you both, brilliant replies!

 

[Lightwave]

Hi E

The way I would tackle this would be firstly to determine how I can get the cartesian product of the teachers on courses that generates the enrolement records and then secondly to update the enrolement table with this cartesian product to seal the enrolement of teachers on courses. If you haven't come across the term Cartesian Product before its a simple concept that is complicated to explain. (Similar to trying to describe a wheel without referring to wheel). A normal deck of cards has four suits (hearts, diamonds, spades and clubs) and there are 3 face and 10 pip cards ( for our purposes ignore joker) As every suit has one of each face and pip card there are 52 cards. If we were to add a further 4 suits a deck would consist of 104 cards. The cartesian product is that set of records defined by the combination of suits and 3 face 10 pip cards.

In MS Access cartesian products can be obtained by joining fields between tables that are non-unique. Often people create them accidentally you might have done this before yourself. So with the table of teachers if you were to add a field and call it cartesian join and then give all teachers a value of 1 for that field and then you could do the same for the courses. Then create a query and linking the cartesian join field in the teachers to the cartesian join field in the courses table when you run the query if you have 2 teachers on 4 courses you should get 8 records returned. This is 2 * 4. Similarly if you had 16 on 16 courses you should get 256. This should be your bulk insert of enrolements.

Ensure you have the appropriate information in that query as once you are happy with it you can use it as the source of an update query or recordsource to insert and update your actual enrolement table.

Updating a table with multiple records is usually done either with recordsets through vba or alternatively by triggering an update query (which again can be called from vba)

Requires a bit of fiddling about as you will need to join two queries which are filtered dynamically from the form which users use to choose the teachers and courses but the principle is the same.

 

[The_Doc_Man]

Lightwave offers suggestions for two methods. I concur with both choices as being viable. Since you would probably add teachers to your registration one COURSE at a time, I might choose a method where you have your multi-select combo box to select teachers and, once you have selected both teachers and course (singular), have a button that says REGISTER.

The OnClick code would contain a loop that steps through the rows of the combo box to get the teacher info you need and would then either create an INSERT INTO query string with the teacher ID, course ID, and any other info needed for registration; OR using a recordset opened before the loop, do an .AddNew on the registration table, fill in the blanks, do an .Update, and keep on looping through the combo box as necessary (and of course, close the recordset when done.)

For combo boxes it is easy to write a loop. The number of rows in the list is cbo.ListCount and you can write a FOR loop using 0 to .ListCount - 1. (It is zero-based; that is, the first row is row 0.) If cbo.Selected(rownumber) is True, that row is selected. If False, it is not.

The contents of the fields of the combo box is cbo.Column(fieldnumber, rownumber). So if you have a multi-field combobox holding the info you need to synthesize the INSERT INTO or to fill in the recordset's blanks for teacher ID, and if another single-select combo is available similarly for the course number, you might have everything you need right there inside the loop. Just takes a little bit of forethought, but it is eminently possible.

 

[ebuckner]

Cheers all, I knew it was possible I think I just needed some prompting/pushing in the right direction. Thanks again.