Problem with multiple nesting subforms

Question

I am trying to create what I thought would be a simple data entry form, but I am obviously missing something here. I am trying to create a database of articles about club meetings. For each article I have variables:

Article ID

Source

Title

Author

Related meeting

For meetings I have:

Meeting Date

Place

Speaker M

Source Article

Each meeting could have multiple speakers and speakers could have multiple topics, so I have the following two tables:

Speaker

Meeting Attended

Topic

Suptopic

Speaker T

The relationships are as follows:

An article could relate to a single meeting, or no meeting. One to one/none (Article ID - Source Article)

Each meeting could have multiple articles. One to Many (Source Article - Article ID)

Each meeting could have multiple speakers, and speakers could talk at multiple meetings. Many to many (Speaker M - Speaker)

Each speaker could have multiple topics. One to many (Speaker - Speaker T)

However, when I try to create forms with subforms, it is as if all relationships become 1:1 for data entry. I can not seem to find how to put multiple speakers in a meeting or multiple topics for a speaker. I know I am missing something simple here but I can not find it in the online videos. Please help!

Answer 1

You are missing a MeetingTopics table, with the following foreign key columns:

MeetingID

Speaker

Topic

The primary key is a composite o all three columns.

This table would reference the composite primary keys of the MeetingSpeaker and SpeakerTopics tables.  The table would model the many-to-many relationship type between MeetingSpeaker and SpeakerTopics.  These tables themselves model relationship types, of course, but a relationship type is really just a special king of entity type, so can be a party to other relationship types in the same way as a table modelling any entity type.

The interface for this model would be a parent form based on Article, within which would be a subform based on MeetingsArticle.  This could be in continuous forms view.  You cannot have a subform within a form in continuous forms view.  You can use correlated subforms, however.  In this case these would be used for inserting data into the MeetingSpeaker and MeetingTopics tables.

Subforms are correlated by including a parameter in the referencing subform which references the key of the referenced subform's table.  The referencing subform is requeried in the Current event procedure of the referenced subform.  For examples of the use of correlated subforms in this way take a look at StudentLog.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have trouble downloading a specific file, clicking on the 'Download' button at the top of the page while no files are selected should download a zip file of all files in the folder, from which you should then be able to unpack the relevant file.

To insert data into the LionsMeetings table you could use the NotInList event procedure of a combo box bound to the MeetingID column in the MeetingsArticle subform.  This would open a dialogue form bound to the LionsMeetings table at an empty new record.  The dialogue form would include a subform based on the MeetingSpeaker table.  For an example see NotInList.zip in the OneDrive folder to which I gave you a link above.

Similarly data would be inserted into the Speakers and Topics tables via the NotInList event procedures of combo boxes in the MeetingTopics subform.  The dialogue forms opened by these event procedures could include subforms based on the MeetingSpeaker and SpeakerTopics tables.

Answer 2

Ok, your descrition of the relationships and the file you recommended were a great help. I think I have the database relationships straightened out. However, I am still struggling trying to create a form that works with these relationships. Any suggestions would be greatly appreciated.

Answer 3

The relationship type between Meetings and Articles is a many-to-many relationship type, so should be modelled by a MeetingArticles table or similar which resolves the relationship type into two one-to-many relationship types, i.e. it should have MeetingID and ArticleID foreign key columns referencing the primary keys of Meetings and Articles respectively.

Similarly the relationship type between Meetings and Speakers is a many-to-many relationship type, so again should be modelled in the same way by a MeetingSpeakers table or similar which resolves the relationship type into two one-to-many relationship types.

The relationship type between Topics, Speakers, and Meetings is less obvious.  Firstly you say that the relationship type between Speakers and Topics is one-to-many, but it's unclear whether this is per meeting or universal.  If the latter this would be modelled by a SpeakerID foreign key in Topics.  If the former then a SpeakerTopics table would model the many-to-many relationship type between Speakers and Topics,  and the composite primary key of this table would be referenced by a composite foreign key  of SpeakerID/TopicD in a MeetingTopics table, whose primary key would be a composite of this composite foreign key and MeetingID.  MeetingID and SpeakerID would also be a composite foreign key referencing the composite primary key of MeetingSpeakers.

You might like to take a look at Relationships.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have trouble downloading a specific file, clicking on the 'Download' button at the top of the page while no files are selected should download a zip file of all files in the folder, from which you should then be able to unpack the relevant file.

This little demo file illustrates how relationships are built up across a database to achieve the final model, using a simplified medical prescriptions database as its example, and how these are reflected in the user interface of forms and subforms.

Answer 4

Thanks for your reply! Maybe this description would help... I am pulling together data from newspapers about the 100 yr history of the club. The way I am looking at it now, the main item in the database is the articles. The article may or may not be about a specific meeting (most are, but some are about fund raisers, obituaries, etc). If it is about a meeting, the article usually has the meeting location and date. Better articles mention who is speaking, and the topic(s). Some articles go further into more detail, but that free text is beyond what I am including in the database. As each article may or may not be about a meeting, I have added a variable for article type. Only those about meeting need to feed into the further tables. However, when I try to create a table with all of these nested together from the "article" table, I am unable to get the nesting to work to allow multiple entries for speakers and topics in the sub folders. If I want only one per article it is no problem, but I have been unable to get a form created that allows me to enter multiple speakers per meeting or multiple topics per speaker.

Answer 5

The way main form/sub forms work is that the main form is bound to the single table (or query based on that single table) on the "one" side of a one to many relationship. If that form has an embedded subform, it would be bound to the single table on the "many" side of that relationship, a query based on that many-side table. If the table bound to that subform, in turn has a one to many relationship to another table, then it can have its own embedded subform, bound to that third table.

If you are trying a different approach, we'll need more explicit details to help sort out the problems.