Many to many within Many to Many

[Ruegen]

Ok

I have created this ERD


I have made two forms.



One of teachers with the record source as the tblTeachers

One of the bookings with the record source as the tblBookings

Made the Bookings form have a subform link to the teachers form, have the BookingsID in the tblBookings link to the BookingsID in the tblTeachers and it all seems to work with no strange disappearances/deletions, I can add new schools as I go and can add a new booking record while filling in details.

So far it looks good.

[June7]

Don't understand having BookingsID in tblTeachers. Records in tblTeachers should be unique for each teacher. How can a teacher book more than one show?

Why two ID fields in tblSchools?

[Ruegen]

Don't understand having BookingsID in tblTeachers. Records in tblTeachers should be unique for each teacher. How can a teacher book more than one show?

Why two ID fields in tblSchools?

It's not really the teacher that is booking but the school, it's just that each time the school rings in the teacher might be different for each booking.

[Ruegen]

I simplified this...

[Ruegen]

What I had in mind is to get multiple teacher forms within the booking form - which isn't working

https://www.dropbox.com/s/5is3ootgpdwb336/TestRun.zip

I added a file if you care to explain through a document...

[Ruegen]

Attachment 13243

And the end result would hopefully be something like this - except work

I need to attach name fields to each of those schools.

[June7]

The db has only one form - frmTeacher. No need to include table School in the form RecordSource. This form will allow you to add new teachers to Teacher table. You can have a subform bound to Junction with a combobox for selecting bookings. A different approach is to have main form bound to Booking and subform still bound to Junction with a combobox for selecting teacher. You can have both arrangements in the db and enter records according to the situation. The first will allow you to enter multiple bookings for one teacher, the second will allow you to enter multiple teachers for the same booking.

[Ruegen]

The db has only one form - frmTeacher. No need to include table School in the form RecordSource. This form will allow you to add new teachers to Teacher table. You can have a subform bound to Junction with a combobox for selecting bookings. A different approach is to have main form bound to Booking and subform still bound to Junction with a combobox for selecting teacher. You can have both arrangements in the db and enter records according to the situation. The first will allow you to enter multiple bookings for one teacher, the second will allow you to enter multiple teachers for the same booking.

Yeah I had that set up - except the combo box for the teachers (because each teacher might be from a different school) was on the frmTeacher. It got to a point where I went to use the form and make a new booking and it would say I have a unique id problem or can't enter data etc.

I only made one frm on that small database because I wasn't sure which way was the best way to go.

[Ruegen]

I seems to be working thus far but can you see any problems with this method?

https://www.dropbox.com/s/5is3ootgpdwb336/TestRun.zip

[June7]

That's one of the form/subform options. Here is other.

Why is date field allowing non-date entries? Needs to be changed to Date/Time type in table. I didn't notice that before uploading my demo.

Date is a reserved word. Should not use reserved words as names. Also, avoid spaces and special characters/punctuation (underscore is exception).

[Ruegen]

That's one of the form/subform options. Here is other:

How do I choose which school the teachers go to? I see only names....

[Ruegen]

Why is date field allowing non-date entries? Needs to be changed Date/Time type in table. I didn't notice that before uploading my demo.

Date is a reserved word. Should not use reserved words as names. Also, avoid spaces and special characters/punctuation (underscore is exception).

I did not know date was reserved, I knew Name was (and I am guessing Time). I didn't change they type because I wasn't concerned with the example.

[June7]

Start typing school name in combobox or select from dropdown list.

Same for teacher combobox.

[Ruegen]

Yeah, it's what I feared - I have to create the name first before I can start typing it in.

Edit: oh wait, the other form pops up and it's blank - how'd you do that?

Edit again: oh wait it's only for names that are already in the list.

[June7]

No, teacher names do not have to already be in the table. This is what I described way back in post 6. Uses NotInList event to pop up the teacher form to enter new record then teacher is available for the booking record.

The same can be done for school combobox.

I used VBA code.