Many to many within Many to Many

[Ruegen]

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.

...there could be a LOT of names over time to have to choose from...

[Ruegen]

Also do you take donations or anything? - this is great help and I do appreciate it

[June7]

True, data does tend to accumulate. I have comboboxes with hundreds of items listed. Users must have some idea of what the data is to enter. A booking request should have this info submitted. Names tend to be tricky because of spelling variations, consistency is important. If teacher name Kate is entered in db as Cate, then might have hard time matching for next booking.

I don't take pay. Like most contributors, am a volunteer participant. The forum site owner, on the other hand, might accept contribution.

[Ruegen]

It works for a while and then I get this.

[Ruegen]

Here's me trying to stuff around with the tables in another database...

https://www.dropbox.com/s/t1effvpr4t62e94/Database2.zip

[June7]

1. RecordSource for frmNewBooking - change to RIGHT JOIN
SELECT tblTeacher.*, tblSchools.SchoolName FROM tblSchools RIGHT JOIN tblTeacher ON tblSchools.NewSchoolsID = tblTeacher.NewSchoolsID;

2. SchoolName textbox should be Locked Yes, TabStop No

3. Cannot enter data into tblJncTeacher and tblBookings with the same form. The subform should be bound to tblJncTeacher with a combobox to select booking record. If desired booking does not show in list then must open another form to enter booking record. Again, this is where the NotInList event of combobox is useful as demonstrated in the example db I posted.

[Ruegen]

1. RecordSource for frmNewBooking - change to RIGHT JOIN
SELECT tblTeacher.*, tblSchools.SchoolName FROM tblSchools RIGHT JOIN tblTeacher ON tblSchools.NewSchoolsID = tblTeacher.NewSchoolsID;

2. SchoolName textbox should be Locked Yes, TabStop No

3. Cannot enter data into tblJncTeacher and tblBookings with the same form. The subform should be bound to tblJncTeacher with a combobox to select booking record. If desired booking does not show in list then must open another form to enter booking record. Again, this is where the NotInList event of combobox is useful as demonstrated in the example db I posted.

ah. I see. So I can't just have the booking record made inside the subform on the fly... I have to make the booking available "first" and then select it. Yes the NotInList is helpful but then it would bring up a new window (which the end users won't appreciate). Ideally it would be nice if the tblJncTeacher could create a record (automatically) as I create the booking within the subform (as though you are just dealing with the booking table/form without the junction.)

[June7]

That's the way it works. Why would users be troubled by another window? I am sure they have interacted with programs that have popups.

How will user know what's available if they don't see list of existing bookings?

[Ruegen]

That's the way it works. Why would users be troubled by another window? I am sure they have interacted with programs that have popups.

How will user know what's available if they don't see list of existing bookings?

It's just efficiency. They don't need to see the bookings in this form - only need to make new ones. I'm making other forms to allow other staff add, view or edit the bookings.

[Ruegen]

What about having a subform within a subform? If that's even possible and would work, have the outer junction sub form hidden and hold the booking one...

[Ruegen]

Also why is it my simple database I uploaded a few posts previously works fine when putting in a name and then selecting bookings in the submenu but my larger database with the same configuration (to my knowledge) does not? Is it because the subform has a qry as a source rather than a table or something like that?

[June7]

Form/subform/subsubform arrangement not appropriate because tblBookings is not a related child table of tblJncBookings. tblBookings is a lookup source for available bookings to select. Its records are not dependent on records in tblJncBookings. tblBookings can have records not yet associated with records in tblJncBookings.

If your db is not working it is quite possibly because of the query. A form can use query, and is often advantageous to do so to manage sort order or to filter dataset when form opens, as RecordSource but the query must be structured properly. If it includes INNER JOIN to other table(s), that might prevent proper functioning.

[Ruegen]

If that's all the case, how about one form to make the teacher, then have a "make bookings button" which opens up booking form and when it closes it relates back to that teacher?

I don't know how or if this is possible because it would just be opening the booking form/table which is unrelated to the teacher table/form.

I'm beginning to think it has nothing to do with the subform - I made a new form using just the bookings table - it won't allow me to add a new record. (also removed the relationship from the relationship table and I get the same error).

[Ruegen]

All that confusion for nothing. Turns out it was a no duplicates on a field I had made with a random number (I wanted each record to have a random number).