Many to Many / Junction Table / Form & Subform

[M. Harper]

Hi all,

I hope someone can help me, I'm pulling my hair out here. It's been a long
time since I last built an Access DB and I'm struggling with something. I
have three tables thus:

|------------------|
|tblSalesman |
|------------------|
|SalesmanID (PK) |
|Forename |
|Surname |
|etc. |
|------------------|

|------------------|
|tblClient |
|------------------|
|ClientID (PK) |
|Forename |
|Surname |
|etc. |
|------------------|

and, as it's a many to many relationship:

|-----------------------|
|tblLinkClientToSalesman|
|-----------------------|
|SalesmanID |
|ClientID |
|-----------------------|

which are linked: the third table is the junction table. There are 1-to-
many relationships as follows:

tblSalesman.SalesmanID --> tblLinkClientToSalesman.SalesmanID
tblClient.ClientID --> tblLinkClientToSalesman.ClientID



Now, the problem. I have a form based on the Salesman and I want a subform
which will show a datasheet of all the Clients linked to that Salesman.
Either I can't remember the way to do this or I'm getting something wrong.
After browsing previous replies in here, I found Roger Carlson's reply to
gudiya, linking to an example database on his site
(ImplementingM2MRelationship.mdb).

I downloaded this, looked at it, and *thought* the penny had dropped - I
then built a query based on the three linked tables, added the linked
foreign keys from tblLinkClientToSalesman first, then added the fields I
wished to record from tblClient. I then based the subform on this query.

No luck, though. It doesn't seem to link the two together, and what I type
in doesn't reappear when you move off the record and back - it doesn't
seem to be linking them.

Thanks to Roger for the example, but I'm still stuck. Would anyone be kind
enough to give me a process breakdown of how to build the query (if that's
the correct way of doing it) on which to base the subform so it links
meaningfully?

Yours, in anticipation,

M. Harper

 

[John Vinson]

I downloaded this, looked at it, and *thought* the penny had dropped - I
then built a query based on the three linked tables, added the linked
foreign keys from tblLinkClientToSalesman first, then added the fields I
wished to record from tblClient. I then based the subform on this query.

No luck, though. It doesn't seem to link the two together, and what I type
in doesn't reappear when you move off the record and back - it doesn't
seem to be linking them.

I'd suggest a different approach. You *could* base a datasheet subform
on a Query joining tblLinkClientToSalesman to tblClient (*WITHOUT*
including tblSalesman); base the mainform on tblSalesman and the
subform on this query.

But a simpler approach is to use a Continuous Subform instead. (Well,
you can use a Lookup Field in the datasheet, but I really dislike
lookup fields). The continuous Subform would be based (directly)on the
junction table; it would use SalesmanID as the master/child link
field, and would contain (perhaps as its only control) a Combo Box
bound to CLientID. The combo would be based on a query such as

SELECT ClientID, [LastName] & ", " & [FirstName] FROM tblClient ORDER
BY LastName, FirstName;

This can be set to store the ClientID while displaying the client's
name, by setting the ColumnWidth property appropriately.

John W. Vinson[MVP]