Data entry into many-to-many-related tables

[nexshark]

Good morning. I wonder if anyone can help me understand this. I have now read most of the 1600 page Access Inside Out, all of Access for Dummies, All of Access Plain and Simple, and have read a good part of this forum, and relationships still confuse me (just ask my wife).

I am building a database with multiple many-to-many relationships. I have a main table, linked with junction tables to three other tables.
I need to link all of the tables (conditionally, as explained a little below) in a form for data entry (whether tab control, form/subforms, or however I am unsure). Simply stated, I envision the form having document info entered, then a command box opening people form if clicked to add one or many related people (the author, an expert discussing it, etc.) , then if there are other "exhibits" to the documents (one or many 'sub-documents') that Exhibits form can be opened to enter information about that/those.

I can get two or three tables together onto a form, but the junction tables are not being updated/populated as I enter new data into the forms... I don't know if the junction tables just have to exist back at the ranch, or have to be brought into the form in some manner...and how specifically to enforce the links between tables to update and populate as each new document with details is entered.

I am attaching the database as it currently stands. Any suggestions on this would be GREATLY appreciated.

 

[Banana]

Don't have Access with me right now.

But if I understand you right, you want a form that will let you "connect" a given document to the many-related such as people and sub-document, correct?

If so, then the answer is a subform. Make the query of junction table the subform's recordsource. You also can create a combobox to list all relevent entries in the other tables to select to add to the junction table.

Now, if you need to create a new record for another many-related table, that is where it get tricky. There's two ways to do it:

1) You can require the users to fill in all records before connecting them together. This can be done by arranging the forms in logical order. Say, a tab control with the document as first tab, and the people as second tab, and associated document as third tab. This is simple to do, but can be time consuming, as you need to input some same data to different tables.

2) Assuming you use a combobox, use NotInList event to open a new form which allows you to add the new record needed to complete the junction table on the fly. Search the board about this to get idea of how it is accomplished. This requires VBA and data validation, so it will take some effort, but your user will like the UI better.

HTH.

 

[nexshark]

I understand subforms. I believe I have the appropriate junction tables created. I don't know what it means to "Make the query of junction table the subform's recordsource". I know record sources...I don't have any queries created as of yet in my database.

Also, I understand tab controls and the like, and have tested data entry which populates the form and subform, but does not populate the junction table (which I understand from prior posts is the key to being able to later query accross these tables)

 

[Banana]

You know that the recordsource can be set to a query, not just tables? This is preferable, as it helps with performance, gives you the means to sort the recordset and apply criteria, especially when it get large.

But that's not what you were asking about.

You said you have form that has a subform, and those populates the tables, but not the junction table, correct? I need some more information before I can make some definite suggestions, but here's my wild off-the-top-of-head guess:

If you are using your document table as recordsource for the form and the people or exhibits as recordsource for subform, then this is probably why junction table isn't being populated. You need to have a form bound to the junction table itself.

The problem of bounding the form to a junction table, however, means that you must have already existing records in both document and people tables in order to select the appropriate record from each table (presumably by using comboboxes or listbox with the table as rowsource) to populate the junction table with the combination.

Which is why I said earlier that you need to come up with a system to ensure that there are two records already in the corresponding tables before you can bring them to the junction table.

Did that muddle the water?

 

[nexshark]

Let me see if I understand this logic.
I have a data entry form called Documents. A data entry person types in information pertaining to the new document to be linked to the database. They click a box indicating they now want to add two people to database, both linked to this particular document (one the author and one an expert called to discuss the document). Up pops the subform (People). They type in the persons info, click to add another related person, and type in the second person's info.
Now I have information in both Document and People tables. The question then is, how do I ensure the link between these people and this document is established.

You say only at this point where related data exists in each table can I cause Access to populate the junction table. I don't know how to do that. My simple mind says if I include a field or two from the junction table in the subform of People, and set them to invisible, Access should see them there, recognise the relationship and auto-populate the junction table. I have tried and this appears to be wrong logic.

Can you shed some light on this??

 

[Banana]

Okay.

Relationship <> Auto-Population.

Relationship simply describes to Access how the tables should behave with other. Whenever you make a new record in single table, you have an autonumbering primary key that automatically increment as soon as you start a new record. But if it's related in another table as a foreign key, meaning it can have children, it is up to you to populate the another table with the key from first table.

Subform does that automatically for you; it wasn't because of relationship, but because Mircosoft gave you subform to make the auto-populating of foreign keys easier.

But it can't handle junction tables as well because as I said, you have to make sure there are records in each corresponding table before you can put together the keys. Furthermore, subforms can auto-populate just one foreign keys, but junction table has two keys.

Therefore, if you want to use a sub-form you wouldn't bind it to People table, but rather to the junction table instead. Even better, use a query that joins together the junction table to the People table, with the foreign key (e.g. PersonID from junction table, not PersonID from Person table) with the necessary fields, and make that the subform's recordsource. Make sure the subform's link is set up thus:

Master field: DocumentID from Document Table
Child field: DocumentID from Junction table

This is off the top of my head as I still don't have Access at this computer, but this should be updateable and thus suitable for recordsource of subform. You'll need to test the query (make a random record and see if you can save it) before setting it to the recordsource of form.

So to summarize:

You need to bind the subform to junction table, not person table.
This means you must ensure there is an existing record in person table before you can make the selection.

Even better, use query that joins together junction table and person table, giving you the keys from junction table but not from person table, AND all fields from person table except for PersonID or whatever its primary key is.

Link the subform to form using DocumentID or whatever the primary key for Document table is with DocumentID from junction table.

Did that help?

 

[nexshark]

I cannot thank you enough for this. You have just helped me tie together enough strands of knowledge to do this...After over a month of frustration.

I will now proceed to augment the form by the additional many to many subforms, and create the conditional openings and closings of those.

Again my sincere thanks.