How to populate a junction table? Take 2 (1 Viewer)

[Missomissou]

This post got buried in another very long thread, so I have cleaned it up and am giving it another go.

I am trying to populate a junction table, but have run into some trouble.

I created a subform with a field of interest (PolicyReviewID) from a junction table and fields of interest (staff names) from a staff table.

It appears the subform I created is read-only. When I try to input data there (by creating a new record, for example), I get an error message, "Cannot add record(s); join key of table 'TblJnctPolRevIDResIDAllALWRIAuthors' not in recordset" (I realize that table name is very long.). However, in spite of this error message, the new record is added to the Policy Review Table, but not to the junction table.

I'm not sure what to change to get the right setup.

Ideally, for starters, with each new record (both on the parent and child forms), I would want the PolicyReviewID field/s on the subform to autopopulate based on the associated master field; and then the names to be selectable from the dropdown list (which is working and appears with the last name for each row/record on the subform, as it should). If this is the optimal format here, I don't think it wouldn't be too much for our staff to advance to the next line on the subform to enter/select an additional author, rather than checking them all off at once as I'd hoped to allow (much earlier this field was a lookup field in the table, which in retrospect seems a much more elegant format). Here's a screen shot of the current form design.

Note, the query subform has been acting as a display for data that was already entered.

 

[MajP]

The junction table is not used to create new authors, only to select an author. A junction table would look something like this with foreign keys from two or more tables.

TblPlocies_Authors
--Policy_AuthorPK
---PolicyID_FK
---AuthorID_FK

Where there are two foreign keys, to the policy table and the author table.
(Records in the junction table uniquely define a record. They need to be a unique composite index or a composite primary key. This example shows a surrogate PK but the FKs would comprise a composite index)

The subform is linked to the main form by PolicyID to PolicyID_FK
The subform AuthorID_FK has a combobox that displays and Author but stores the AuthorID

My combo would show the last and first name something like

Select AuthorID, [lastName] & ", " & [FirstName] as FullName from tblAuthors Order By LastName
Bound Column: 1
Column Count: 2
Column Widths: 0; 3

The combo shows Smith, John and stores the FK = 1 where 1 is the PK for John Smith.

Your subform does not look like a junction table, but the primary Author table.

If you want to show more author detail than just the name then the subform is a query joining the Junction table to the author table. But anything on the author side is read only (locked). You still need a way to select the author PK and store in the authorID_FK

 

[Missomissou]

The junction table is not used to create new authors, only to select an author. A junction table would look like

TblPlocies_Authors
---PolicyID_FK
---AuthorID_FK

Where there are two foreign keys, to the policy table and the author table.

The subform is linked to the main form by PolicyID to PolicyID_FK
The subform AuthorID_FK has a combobox that displays and Author but stores the AuthorID

My combo would show the last and first name something like

Select AuthorID, [lastName] & ", " & [FirstName] as FullName from tblAuthors Order By LastName
Bound Column: 1
Column Count: 2
Column Widths: 0; 3

The combo shows Smith, John and stores the FK = 1 where 1 is the PK for John Smith.

Your subform does not look like a junction table, but the primary Author table.

If you want to show more author detail than just the name then the subform is a query joining the Junction table to the author table. But anything on the author side is read only (locked). You still need a way to select the author PK and store in the authorID_FK

Thank you--I think my junction table is set up as you explained above, but instead of the two fields being primary keys, there is a separate autonumber ID field. It seems like folks on these forums tend to use one approach or another (I was lightly admonished earlier for having two fields act as PKs and so changed it to a separate field). The subform I showed above above contains fields from the junction table, the PolicyReviewID; and from the ALWRIStaff table, LastName and FirstName. But though I am selecting staff names from the dropdown list, the only data being entered is the ResearcherID in the PolicyReviews table. You ended by saying "You still need a way to select the author PK and store in the authorID_FK"--Do you have any ideas for how I might do that? Thank you again for considering this--I so appreciate your insight.

 

[MajP]

but instead of the two fields being primary keys, there is a separate autonumber ID field. It seems like folks on these forums tend to use one approach or another (I was lightly admonished earlier for having two fields act as PKs and so changed it to a separate field).

That is definately not what I said. I said a junction table holds at least two foreign keys.

he subform I showed above above contains fields from the junction table, the PolicyReviewID; and from the ALWRIStaff table, LastName and FirstName

That is not what your form show. Your form shows a query joing the junction table and the staff table with no way to select a staff member.
Instead of the last first name I should only see a combo box bound to tblJncPRSallAuthors.ResearchID
That combo is formatted as I explained. Get rid of the last name and first name field and build the combo box.

 

[MajP]

See this recent thread. You are basically making the same mistake the OP did, and my solution is the same solution. Download the before and after to get the jist.

Search (if not found: add) in a table on a subform

Hi! In short: I've got a database where I put in details of people who followed courses and with some macro's and links to Word, I can send them their certificate automaticly by mail. To explain the issue I have, I'll give you the necessary details of the "impacted" details: Three tables...

www.access-programmers.co.uk

 

[Pat Hartman]

I think my junction table is set up as you explained above, but instead of the two fields being primary keys, there is a separate autonumber ID field. It seems like folks on these forums tend to use one approach or another (I was lightly admonished earlier for having two fields act as PKs and so changed it to a separate field).

My rule on junction tables is - If the junction table has child tables, I use an autonumber PK and make a unique index on the two FKs. Otherwise, using a two-column PK is fine. So, with your table, in order to ensure that you don't end up with two rows with the same pair of FKs, you need to add a unique index using the indexes dialog. Here's a picture of what multi-field indexes look like. You cannot create them in the table design directly as you can create a multi-field PK.

The reason, the two-column PK is fine for junction tables without children is because you never join to it except to one or the other of the FKs. When the junction has child tables. For example, a StudentClass junction could have an Assignments child table so that each assignment and its grade get entered on a separate row.

It is the multi-column joins that we want to avoid where possible.

 

[MajP]

As I mentioned the many to many subform often has a recordsource that is just the junction table. In your case that is all you need since the only information you have in the staff table is first and last name and your combo box can show that. Normally people will concatenate the first and last.
However there may be cases where you would like to show additional related fields. Lets say the staff table had Date hired, Position, Office, etc
In that case you can make a query with your junction table and the staff table, but the only fields that can be editable are those in the junction table. These other fields are for display purposes and you do not want to be allowed to edit those accidently.

See this updated example. The first example uses only the junction table as a recordsource. The second brings in additional fields using a query, but locks those fields.

 

[Missomissou]

Thank you @MajP and @Pat Hartman, working on this now! @MajP apologies for misquoting you. I misread/understood what you wrote. This DB has been keeping me up at night. I really appreciate all the help!