ms access - Display many to many relationship in continuous form

Question

I can't figure out how to display a column that has many value for the same record in a continuous form

I got 3 tables

SalesCall
SalesCallId | etc..

Mill
MillId | name...

SalesCallMills    <------ Junction table 
Id | SalesCallId | MillID 

the basic design for a many to many relationship.

When it's a simple form, I'm used to do a list and change the control source for the current ID with a SQL query.

What's the common practice to display this in a continuous form?

This was the form before when only 1 mill was possible.

I thought I could concat the mills, but it will be hard to read and it will be way to long.

So I thought about a list but I don't think it's possible to change the control source for each record.

Also, good to mention that this is read-only. It's not for adding or editing. The form to enter data is already made. And I think that one mill per record is not an option cause it would really confuse the user.

What's the proper way to display a multi value column with my database design?

Answer 1

This is a form with correlated subforms.

The two forms are synchronized via the link child and master fields:

Link Master Fields: Forms!Form7![SalesCall Subform].Form.SaleID
Link Child Fields: SalesCallId

And a little code in the Current Event of subform #1

Private Sub Form_Current()
Me.Parent.[SalesCallMills subform].Form.Requery
End Sub

Selecting a line in subform #1 displays the relevant detail lines in subform #2.

It should not be difficult to add suitable information on such things as mill name to the general outline.