Recommended way to create n:m-relations in a form?

[Boris]

Is there any recommended way to offer users the possibility to create
n:m-relations between two tables in Microsoft Access? Subforms are nice to
create 1:m-relations but is there any built-in support in Access for
n:m-relations without developing VBA routines (something as easy as
subforms)? I think I miss a n:m-wizard for forms?

Boris

 

[stefan hoffmann]

hi,

Is there any recommended way to offer users the possibility to create
n:m-relations between two tables in Microsoft Access?

Recommend? Don't know, but the only solution is obvious to me. You have
following tables to represent your n:m relation:

Table1(1)->(n)Table2(m)<-(1)Table3

A normal Form:Subform shows Table1->Table2+Joined Data or
Table3->Table2+Joined Data.

The only way to show n:m is to show a form showing table2+Joined Data.
You can add two subforms for each side, but i don't think it is
transparent to normal users.

--> stefan <--

 

[Boris]

[...]
Recommend? Don't know, but the only solution is obvious to me. You
have following tables to represent your n:m relation:

Table1(1)->(n)Table2(m)<-(1)Table3

A normal Form:Subform shows Table1->Table2+Joined Data or
Table3->Table2+Joined Data.

The only way to show n:m is to show a form showing table2+Joined Data.
You can add two subforms for each side, but i don't think it is
transparent to normal users.

Thank you for your fast answer, Stefan!

I used the 2-subform-solution but had to create quite a lot of VBA routines
(to add and remove records to Table2, to update the record source of the
subforms after any changes etc.). However as I have quite a lot of
n:m-relations and think I am not the only one in the Access world using
n:m-relations I wonder why there is no wizard or any other built-in solution
in Access? I would prefer such a built-in solution (even if it is not
perfect) instead of re-inventing my forms, subforms and VBA routines all the
time.

Boris

 

[stefan hoffmann]

hi,

I used the 2-subform-solution but had to create quite a lot of VBA routines
(to add and remove records to Table2, to update the record source of the
subforms after any changes etc.).

Synchronizing is always a mess in VBA, cause its imho not build for such
tasks. Besides the event model is not that clean, try synchronizing form
and subforms in the OnLoad event, aghhh...

--> stefan <--

 

[Boris]

Synchronizing is always a mess in VBA, cause its imho not build for
such tasks. Besides the event model is not that clean, try

That's why I am asking for a built-in solution in Access. I don't want to
make my hands dirty.
But I really wonder why there is no built-in solution as n:m-relations are
quite common?

Boris