Access 2016, many-to-many relationships and subforms

[Thirith]

This is a bit frustrating: I know that this is something I myself have managed to do in the past, with the help of this forum - but it's been half a dozen years since I worked with Access, so I can't remember how best to solve this.

I'm currently putting together a database for work that will allow us to keep track of submitted proposals for conferences and the like. One of the things that we want to keep track of (currently we're at the stage of using Excel to do this badly...) is the countries that invitees are from. I've got three tables: one for the proposals (tblProposals), a lookup table (tblCountries) and a junction table (tblProposalsCountries), with one-to-many relationships between the latter and each of the first two. I can add a subform to the main form we use that will show for any given proposal the countries that speakers are from, but I cannot add any entries to that subform.

What I'd ideally need to do is this: have a subform where I can see, add and delete the countries the speakers are from, if possible using the full country names.

The relevant tables and fields I've got are as follows:

tblProposals:
- ID_proposal


tblProposalsCountries:
- ID_proposal
- CountryCode (2 letters)


tblCountries:
- CountryCode
- CountryName

Any help would be much appreciated!

 

[xenou]

Part of me would prefer to have a drop-down list that goes by country names (which are in the lookup table) rather than country codes (which are both in the lookup table and the junction table), the reason being that some of the official country codes aren't particularly intuitive (e.g. GB for United Kingdom, CH for Switzerland). It works as it is, though, even if people might occasionally have to look up the code before they enter it.

That is standard operating procedure for Access forms. You use the country table as a source to populate the drop down so the user can choose a country. You only save the country code alone as the value you are adding/entering/saving.