Create Many-to-Many Records in Microsoft Access with a Junction Table
Question details
The user needs to save multiple country or region relationships for each source record in Microsoft Access.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A main form is bound to a source table, and a subform is bound to a junction table.
- Observed behavior
- IDs selected in a multiselect list box are not being stored correctly in the junction table.
Identify the primary key in the source table and the primary key for each selectable country or region. Confirm that the junction table has matching foreign-key fields and that the main form is bound to the source table.
Use a Continuous Subform to Add Junction Table Records
Replace the multiselect list box with a combo box on each subform row so Access saves one relationship per record without custom selection-handling code.
This approach assumes countries and regions are stored as selectable records in one lookup table. Each junction row links one source record to one lookup record and can also store relationship details such as Status.
Open the junction table in Design View. Add SourceID and LocationID foreign-key fields with data types compatible with their respective primary keys. If the primary keys are AutoNumber fields, use Number fields with Field Size set to Long Integer for the corresponding foreign keys. Add an optional Status field.
Open Database Tools > Relationships, add the source, location, and junction tables, and link each primary key to its corresponding junction-table foreign key. Select Enforce Referential Integrity where the existing data permits it.
Create a form based on the junction table, then add it to the source main form as a subform. In the subform control's Property Sheet, set Link Master Fields to the source primary key and Link Child Fields to SourceID.
Open the subform in Design View and add a combo box bound to LocationID. Set its Row Source to a query containing the location ID and display name, and set Bound Column to the ID column. Hide the ID column with Column Widths if you want users to see only the name.
In the subform's Property Sheet, set Default View to Continuous Forms and Allow Additions to Yes. Open the main form in Form View, choose a source record, and use a new subform row for each country or region. Enter Status on each row if needed.

Work with Exported Access Data in WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for working with office files. Export Access data to Excel when you want to review or share it in WPS Spreadsheets; WPS does not replace Access junction tables or forms.

Frequently Asked Questions
Why does the multiselect list box not create junction records automatically?
A multiselect list box can hold several selections, but those selections do not automatically become separate junction-table rows. Saving them requires code that loops through the selected items and inserts a record for each one.
How does the subform know which source record to use?
Set the subform control's Link Master Fields property to the source primary key and Link Child Fields to the junction table's SourceID. Access then links new subform rows to the current main-form record.
Can I store a status for each selected country or region?
Yes. Add a Status field to the junction table and place a bound control for it on the continuous subform. Each relationship row can then have its own status.
What if countries and regions are in separate tables?
Use foreign keys that match your data model. If each relationship represents a country-and-region combination, add separate CountryID and RegionID fields to the junction table and bind a combo box to each field on the subform.




