Display many to many relationship in continuous form
ms-access
Solution
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.
Problem
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?