M2M: Avoiding Duplicate Key Pairs?

  • Thread starter Thread starter dwetmore
  • Start date Start date
D

dwetmore

I have a many-to-many relationship in which I want to trap attempts to
relate a child record to the current parent record more than once. I
think I can do it by loading the child records into a listbox on a
NotInList event and then building the join records, but that seems
more complicated than is necessary.

Is there a better way to do this?

Thanks, Dave
 
Define your many to many. Many to Many relationships do not exist in any
database engine. It requires a Junction table to resolve many to many into
one to many form the parent table to the junction table and a one to many
from the Junction table to the child table. If you do it this way, there
will be no problem with duplicate entries.
 
I have a many-to-many relationship in which I want to trap attempts to
relate a child record to the current parent record more than once. I
think I can do it by loading the child records into a listbox on a
NotInList event and then building the join records, but that seems
more complicated than is necessary.

Is there a better way to do this?

Thanks, Dave
Click to expand...


I haven't grasped your scheme for using the NotInList event (of what?), but
you can prevent duplicates creating a unique index on the two key fields in
the junction table. That is, the two-field combination would have the
unique index, not each individual field. You'd have to deal with the error
that will be thrown when the user attempts to add a duplicate.
 
Taking Dave's (KLATUU) response to the next step. How are you importing this
data, and how are you saving it? When I want to map relationships in a
many-to-many relationship, I generally create a form which contains a combo
box (displays the parent value), and two listboxes (lst_Mapped, and
lst_NotMapped). Where I use a query that selects displays all of the records
that are mapped from the "junction table" in lst_Mapped, and all of the
"children" that are not mapped in lst_NotMapped. Then I add a couple of
command buttons that either add the parent-child relationship (determined by
clicking on a record in one of the lists), or remove that relationship from
the "Junction" table.

There are obviously other ways to do this, but this is a very "visual"
method that my users seem to like.

HTH
Dale
 
Back
Top