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

Can you please post an empty copy (or with some dummy data) of your database to OneDrive or similar, and post the link here, so that I can make appropriate amendments to it. I should warn you, however, that I might not be able to do this immediately as I'm about to be treated in hospital for heart disease.

Answer 2

I'm trying to follow your quote, but writing it down still is missing something. Here is what I have on relationships so far. Can you be more specific in which relationships you see that should be changed?

Answer 3

Your problem is that, where a table has a composite primary key of two columns, and this is referenced by a table in which those two columns have corresponding columns, you are attempting to create two relationships between the tables on the individual columns.  You should be creating a single relationship on the two pairs of columns, referencing the composite primary key of one with the corresponding composite primary key of the other.

If you have downloaded the Relationships.zip demo file to which I gave you a link earlier, you will see that in that there are single relationships on composite keys of up to four columns.  If you do this you should have no difficulty creating enforced relationships.

You should then be able to create subforms successfully.  I note, however, that you still trying to create nested subforms.  While this is possible, it does leave you with a rather clumsy interface in which all subforms bar that at the bottom of the hierarchy are in single form view.  Using correlated subforms in continuous forms view, as in my StudentLog demo, provides a more fluid interface where you are incorporating most of the database in a single form/subforms interface.

Answer 4

And Obviously I am still missing something here. I tried creating the form as you suggested, but I am getting an error when I try to enter data. When I try to enter the date it says "you can not add or change a record because a related record is required in table Lions Meeting". It doesn't allow me to progress beyond this one to the other sub forms.

Answer 5

Ok, first, thank you for taking so much time to help me through this problem!

I tried creating the additonal table you stated. When I use MeetingSpeaker and SpeakerTopic, I can only draw the meeting ID from Meeting Speaker but can lift Speaker and Topic from SpeakerTopic (as long as I do not try to enforce referential integrity). However, they show up as indeterminate joins. Please let me know if this is how you envirioned the fields flowing as I try to get this form created as you described. I am very thankful for your help! You have helped me get much further. 10 Years ago I was an expert in Access, but it has been so long I am finding things have changed and/or I forgot how to do some of these more complex joins. I will reply more after I try getting the form set up on this end.