Many to Many implementation question for use in a form (1 Viewer)

[darren726]

Hello all,

I have a scenario where a many to many table is needed but I can't it to work in the way that I need. I currently have to add 10 columns to my main table then use a union query to stack them with the rows primary key to recreate the many to many table I want to use. The 10 columns pull accessories from another table which have different categories. The 10 columns I add to the main table are for each category which pull all the available accessories for that category via a query.

When I add a new primary record with all it's info on the mainform, I want to use a subform within the main form with 10 combo boxes for the user to select the desired accessory for each category and have it add all 10 of those selections with the primary records key as 10 individual rows to the many to many table. I feel like I'm overlooking something obvious but it's just not coming to me. The 10 extra columns in main table work fine for what I'm doing but I'd much rather setup the relationship properly with a many to many table. If I'm mistaken in this thinking, please let me know and thanks for any help/insight you can provide.

Here is a very simplified version of the data scenario I'm trying to accomplish. I'm only using 3 categories/columns for these examples:

Door_Locations - Main form for adding new record

PK_Door	Door Identifier	Room	Door Part No	X Cord	Y Cord	Closer Required	Holdback Required	Lock Required
1	1-1E	Room 1	62B-2	156.25	46.125	YES	YES	YES
2	1-2C	Room 1	45A-6	654	22.625	NO	YES	NO
3	3-1B	Room 3	92M-7	426.185	-146	YES	NO	YES
4	2-1C	Room 2	84A-1	23.625	-82.75	YES	NO	NO

Detail_List - The table that Door_Locations pulls from to get the accessories by the 10 categories I mentioned

PK_Detail	Detail	Detail For	Description
1	N/A	Not Used	N/A
2	1A	CloserDetail	Door Closer 1
3	1B	CloserDetail	Door Closer 2
4	1C	CloserDetail	Door Closer 3
5	2A	HoldbackDetail	Holdback 1
6	2B	HoldbackDetail	Holdback 2
7	2C	HoldbackDetail	Holdback 3
8	3A	LockDetail	Lock 1
9	3B	LockDetail	Lock 2
10	3C	LockDetail	Lock 3
11	4A	InstallationDetail	Install 1
12	4B	InstallationDetail	Install 2
13	4C	InstallationDetail	Install 3

This is what the Many To Many Table would look like

PK_DetailRequired	FK_Door	FK_Detail
1	1	4
2	1	5
3	1	10
4	1	11
5	2	1
6	2	6
7	2	1
8	2	12
9	3	2
10	3	1
11	3	9
12	3	13
13	4	4
14	4	1
15	4	1
16	4	12

This is how I have to setup the main table then use a union query to recreate the Many To Many table I want

PK_Door

Door Identifier

Room

Door Part No

X Cord

Y Cord

Closer Required

Closer Detail

Holdback Required

Holdback Detail

Lock Required

Lock Detail

Installation Detail

1

1-1E

Room 1

62B-2

156.25

46.125

YES

1C

YES

2A

YES

3C

4A

2

1-2C

Room 1

45A-6

654

22.625

NO

N/A

YES

2B

NO

N/A

4B

3

3-1B

Room 3

92M-7

426.185

-146

YES

1A

NO

N/A

YES

3B

4C

4

2-1C

Room 2

84A-1

23.625

-82.75

YES

1C

NO

N/A

NO

N/A

4B

Here is how I want to use the form with above data and the relationship setup between the tables.

Thanks again for any help you can provide.

 

[June7]

Assembly/manufacturing type db is common topic of threads. I am sure a search in forums will present a selection for study.

I believe it is one of the most difficult type to build and consider myself lucky I never have.

 

[namliam]

Common solution is to insert a junction table to resolve the N:M problem.

N:1 and 1:M as a result you have an extra layer to add information to that is pertinent to the particular relationship as well

 

[darren726]

Well I got the insertion method to work with my desired setup. I used the following public function to restrict the recordcount on the subform to the desired qty of dropdown categories I needed. Because of this, my dropdowns are no longer filtered by each individual category. I'm wondering if there is a way I can use the Where clause with this function to set a filter on the combo box for each record count of the subform?

Would I do something like this, use a case statement or something else?
If .recordcount = 1 Then
Select Detail_List.PK_Details, Detail_List.Detail AS Closer_Detail
Where Detail_List.DetailFor = "Closer"
Else If
If .recordcount = 2 Then
Select Detail_List.PK_Details, Detail_List.Detail AS Holdback_Detail
Where Detail_List.DetailFor = "Holdback"
Else If
etc.... for each additional combo

Here's the function I mentioned:

Module
--------------------------
Public Function LimitRecords( _
frm As Access.Form, _
Optional RecLimit As Integer = 1)

' Limit the number of records in the form passed as
' to no more than the number specified by .

With frm.RecordsetClone
If .RecordCount <> 0 Then .MoveLast
frm.AllowAdditions = (.RecordCount < RecLimit)
End With

End Function
-------------------------
Private Sub Form_Current()

LimitRecords Me.subfrmAddDoor2.Form, 4

End Sub
---------------------------

This is how design view of my form looks. As you can see, subform is continuous form.

This is working form now but you can see how the combo boxes now list all details when I wish to separate them by category to align with the text boxes. It's not a big deal in this sample database but the full database has 10 categories and well over 500 details.