Sub form using Many to Many relationship

[Sc0tt1e]

I am extremely new to Access and am trying to build myself a project to help me with my mortgage business.

Attached is a sample book (massively redacted to fit max size requirements) within it I have a customer table and a customer properties folder with a join table to facilitate the many to many (Mr & Mrs can have multiple properties within their portfolio together and some individually). I cannot for the life of me get a form with the customer details and a sub form with the customer property details to work and was wondering if someone could help me understand what I need to do to create this within a form (I understand inner, left right joins from SQL but cannot adapt it for this purpose).

I have watched a ton of Youtube and read multiple chapters of my Access for Dummies (9 books in 1) bit can't get it, please help me get my head around this

[June7]

Conventional approach would be main form bound to Customers, subform bound to junction table with combobox to select property.

Or

Main form bound to Properties, subform bound to junction table with combobox to select customer.

[davegri]

Here's a simple working example using your tables. Added a form and subform. I did it opposite June7 suggestion. Customer is main form with property selection on subform.

Sc0tt-davegri-v01.zip

[Sc0tt1e]

Thanks Dave this is almost what I am looking for. I need the full address from the TProperties table to show in the subform, not make the join field a single address line and link them that way.

This is just one step in a much more complex design. A broker will open a tab that finds all their customers, when they open the customers form they can look through each custmer and see all the properties they own and I will then build a way of selecting a property for it to then show the loan that sits behind it.

I would really appreciate if you could explain how you link them together so I can understand the process and replicate it. Currently when I try and add the subform to the main form I get an error message on the defining field links table of the wizard and the addresses do not 'import' correctly to the right customer

[davegri]

Below video basically shows how I did it. Skip the confusing junk between time 1:30 and 2:45 in the video. Pay particular attention to changing the textbox to a combobox in the subform.
I didn't make the subform a datasheet because of your requirement to see all the property data there. I used DLookups to show the property fields in the unbound subform textboxes.
In my DB, you can't update or add records to the property table itself. You'll need to add a form to do just that.

https://www.bing.com/videos/search?q=microsoft+access+%22many+to+many%22+%2bsu bform&&view=detail&mid=FC6FF46BC74858159DAFFC6FF46 BC74858159DAF&&FORM=VRDGAR&ru=%2Fvideos%2Fsearch%3 Fq%3Dmicrosoft%2520access%2520%2522many%2520to%252 0many%2522%2520%252Bsubform%26qs%3Dn%26form%3DQBVR %26sp%3D-1%26pq%3Dmicrosoft%2520access%2520%2522many%2520to %2520many%2522%2520%252Bsubform%26sc%3D0-40%26sk%3D%26cvid%3D8B317E26E9AB4367945DB0DB65DAC6 F2

[June7]

Instead of DLookups, suggest including additional info as columns in combobox then expressions in textboxes reference columns of combobox. =combobox.Column(1)