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]

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

tblProposals:
- ID_proposal


tblProposalsCountries:
- ID_proposal
- CountryCode (2 letters)


tblCountries:
- CountryCode
- CountryName

If a proposal is from a country, then you only need a foreign key in the tblProposals table, which is consistent with your description of this as a one-to-many relationship. Junction tables are only needed to resolve many-to-many relationships.

So all you really need is:
tblProposals:
- ID_proposal
- CountryCode


tblCountries:
- CountryCode
- CountryName


If a proposal has multiple countries (1 or more countries for a proposal, one or more proposals for a country), then the junction table structure looks correct.

If you can't add entries to a subform, then check that it is allows additions and deletions, and that you have the correct primary key definitions (it should be proposal AND country code, as a two part, composite key). You probably would want the proposal id to match the main form, since you probably will be adding, changing, and updating proposals more than you would do so for countries.

 

[Thirith]

Thanks. It's indeed a case of many-to-many, as each proposal can have invitees from any number of countries, and in turn those countries can turn up on any number of proposals. I'll look into the things you've mentioned.

 

[Thirith]

Okay, quick update:

I've managed to create a subform that lists the country codes. When I click on the country code field, a dropdown menu is opened that lists all the country codes in column 1 and all the country names in column 2. I can edit this subform.

However, as soon as I try to add anything from another table (in this case tblCountries), the subform is blocked for additions/edits. We can use it like this, but it's not particularly handy. At the very least I'd want to add a field to the subform that shows the country name for each country code in the list - but the moment I add a field from tblCountries, I again can't edit.To illustrate, this is what the subform looks like at the moment (the captions are in German):

 

[xenou]

I don't see a picture but your description is confusing. you say you have the country names, then you say you want to add the country names. Why do you want to add something that is already there?

a dropdown menu is opened that lists all the country codes in column 1 and all the country names in column 2

However, as soon as I try to add anything from another table (in this case tblCountries), the subform is blocked for additions/edits. We can use it like this, but it's not particularly handy. At the very least I'd want to add a field to the subform that shows the country name for each country code in the list

 

[Thirith]

The country names are shown in the dropdown list, but they weren't shown in the datasheet view; i.e. I'd have a datasheet view along the lines of "GB, US, DE, IT..." (only the codes, not the names), and if I clicked on any of the entries, I'd get a dropdown box that lists "AU | Australia, AT | Austria, BE | Belgium...".

However, I've since solved that problem by adding a text box that used a DLookup expression as its control source. Took me forever to get all the quotation marks right, and there may be better, more elegant ways of doing it, but it works.

 

[xenou]

Are you linking the subform directly to the table? It sounds like that is a lookup inside a table field.

 

[Thirith]

The subform is currently based on the junction table. The moment I tried to include fields from other tables (e.g. by using a query that would get the country codes from the junction table and the country names from the lookup table) I was no longer able to make edits in the subform.

 

[xenou]

If you are using DLookup then that should work I guess. Not sure if you still have a problem or not. Something used to add data to the table probably should not be based on such a query as you describe here - you are simply adding data, not querying for country names. The country names typically would appear as a second column in a dropdown list (as you described earlier) and is there only for the convenience of the user to see the full name of the country codes they are choosing.

I tend to create separate forms for data entry and data viewing - you might start there just for clarity (i.e., first create a form just for adding data, then see if you can make that work for both viewing and adding data if you want to be able to do both).

 

[Thirith]

Thanks, at the moment I'm reasonably happy with how it works. 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.