Form design for a many-to-many relationship

[Jac Tremblay]

Hi,
I need advice on the design I should use for my form. The base data are a
Members table and a Years table linked through a many-to-many relationship
(an intermediate table with the idMember and the IdYear as primary key).
In my form, I would like to see, for each member, all the years he had been
a member for. I front of every year present in the Years table (and on the
members form, in descending order), I would like to have a check box that I
could check or uncheck as needed to insert or delete records in the
intermediate table (for a given member and a given year).
I have already developped a form in which I display only the last two years
and it works fine. The problem is that I have duplicated code and procedures
with minor differences and I find that way of doing sort of macramé (not very
elegant).
I tried to insert a subform with the years in it and a check box inside or
outside the subform and it does not work well because one has to select the
year before checking the corresponding box which is prone to error.
Anyone has any idea?
Thanks.

 

[mscertified]

I usually find the best design for a many-to-many linking table is a form
with a right and left pane and right and left facing arrows (buttons) between
them. The left pane shows currently associated records and the right side
unassociated records. The arrow buttons are used to move the records between
panes. In your case, the form might represent the member and the left pane
the member years and the right pane the non-member years.

 

[Jac Tremblay]

Hi mscertified,
Your idea is good but my members form is full of information I want to see
like the complete address, the phone numbers, email, date of birth with the
age calculated, and so on. I also want to add the information about the
membership years in the following form:
cb 2008
cb 2007
cb 2006
and so on.
The cb means check box. With that display, I can see all the member
information in one shot including the years he has been a member for. And I
can correct anyone I wish with a simple click.
I hope this clarifies the problem.
Thanks for your answer.