Cascading combo box using a many to many relationship

[Chris W via AccessMonster.com]

Hi

I have a very large and complex form that is the bases for a search query. I
am trying to get two combo boxes to cascade, one is generic asset description
and the other is type. However my problem is that I need these to fields to
come from a many to many relationship. I can get it to work were it is from a
one to many relationship but to do this in the long term would undermine the
database and result in the database storing the same data several times.

What I cant get it to do is were the combo box has the ID field as the bound
column I cant get it to display both the ID and the Description/Name because
these are in another table if I use the Junction table as the origin of the
combo box, it will work if I use the original table as the basis for the
combo but then it won’t cascade.

I have reviewed all the previous posts and there are none that I can find
which deal with the problem of cascading combo boxes coming from a many to
many relationship.

Any help would be greatly appreciated, thank you.

Cascading combo box using a many to many relationship

 

[Graham R Seach]

Chris,

Since it's a many-to-many relationship, you may have defined a compound PK
in the junction table, in which case you need to return at least two keys.
But in this case, you won't be able to bind the combo to a single field.

If the junction table uses only a single PK, then bind it.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia