Design correctly related forms (1 Viewer)

Krayna

Member
Local time
Today, 04:48
Joined
Sep 24, 2020
Messages
452
Hi,
I'm working on a set of forms which follow on one to the next. The first form inputs the basic student information, the second form involves the guardian data. However I am having trouble with the second form - since it must be linked through a junction table. So when I open the second form the form always comes up with an error. Can anyone help resolve this?
Also the drop-down list of existing guardians not working - possibly related to above issue.
Thank you
 

Attachments

  • guardianDEtest.accdb
    1.3 MB · Views: 79

June7

AWF VIP
Local time
Yesterday, 19:48
Joined
Mar 9, 2014
Messages
3,635
Suggest you explore using form/subform arrangement. Main form bound to tblGuardian, subform bound to jtblStudentGuardians with combobox to select student. Use combobox NotInList event to add students 'on the fly' during data entry. All common topics.

Or go the other way around with main form bound to tblStudents, subform bound to jtblStudentGuardians with combobox to select guardian.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:48
Joined
Feb 19, 2002
Messages
32,908
Krayna,
I would suggest starting again. None of your validation code is in the correct events. There is no attempt to connect a student to a guardian. There is no way to edit an existing student or add a second guardian.

I've attached a sample m-m relationship. Mine is students-classes rather than students-guardians but the concept is the same. The example shows the two paths. Classes to employees and employees to classes. Class and Employee are stand alone entities that are related after the entities are created. The two views show two different techniques of handling a m-m. One shows a main form with a subform and the other shows a main form that opens another main form.

Another thing you might want to consider is to not prefix your column names. It just increases the number of characters you need to type before intellisense becomes useful and you will find this annoying sooner rather than later especially since the prefixes add no value. You ALWAYS know when you are referring to a control or to a field in a recordset so having the field name start with "fld" doesn't add anything.

One thing of particular interest is how to use the same validation for two different forms. The form that connects the employees to classes is represented two different ways. This is not common and is done in this application for illustration to show two ways of handling this type of form but I took the opportunity to show how to use common validation code if you actually ever wanted to do something like this. I didn't create a class module since this is too specific an application to justify creating a class and a class module would only confuse the issue when it isn't actually generic. The validation is specific to the controls on this form and the code would never be used in a different situation but this allows me to have one procedure operate on two different forms.
 

Attachments

  • ManyToMany20210414.zip
    1.5 MB · Views: 81

Users who are viewing this thread

Top Bottom