Can't get association table to update through data entry form

[jswiss]

Hi All:



I am creating a database for my organization to keep track of members, but I must admit I'm a bit rusty with Access, not that I was terribly skilled to begin with. I have numerous many-to-many relationships where a table and a static list are joined with an association table. All of the association tables contain a PK and two FKs from each related table. All of the FKs have related select queries linked to the tables containing static lists.

We are hiring two data entry clerks on short-term contracts at the end of the month to process all our membership registration forms, so I am trying to create a form with a tab control subform to help them. Everything works fine, except when I enter data for any of the association tables. I have the lists accessible via list boxes on tabs with extended multi-entry enabled. When I do test runs none of the association tables fill in. Right now I only have two sub-forms on the main form (form START HERE) experiencing this problem (both in the "Type/Sector" tab, although I tried one with the same results in the "Crosscutting" tab.

I attached some screenshots for reference (the database was too large to upload).

Best,

Josh

[aytee111]

I'm not sure I understand what you are trying to do. You would need a form/subform for each table:
- to enter NGO's
- to enter sectors
- to enter types
- to enter type/NGO link
- to enter sector/NGO link

I don't know where memberships come in?

[jswiss]

Sorry for not being clearer. I have a main form that functions well that keeps all NGO members' basic information (name, acronym, website, etc.). Within that main form are many subforms, some for one-to-many relationships (i.e. NGO contacts) and some for many-to-many relationships (i.e. NGO sectors--an NGO can be categorized in multiple sectors, each sector can have more than one NGO).

I'm using the forms for data entry purposes. When I run a test, my tables fill in for all of the one-to-many relationships correctly. However, none of the association tables for the many-to-many relationships receive data. I've read on other forums that I need to build a query for the many-to-many relationship and base the subform off the query, but I don't understand why I would need to do that.

Hope that explains it a bit better.

Josh

[aytee111]

You're right, that query idea is incorrect.

OK, you will need two subforms for each - in the first the user will select which NGO they are dealing with, then the subform will allow them to add all the applicable sectors. The second subform will be where the user selects the sector and adds all the NGO's associated with it. Do the same for the types. You are trying to do it all in one but using the KISS method and separating them out and dealing with them one by one will be far simpler.