Unable to complete Junction Table from m-2-m form

[Sc0tt1e]

I have a form that pulls through the customer details from the customer table with a sub form that pulls through a list of addresses from the property table. This is a many to many relationship so there is a junction table set up to facilitate this. When I add a new address in the sub form the property table is updated but it is not pulled through in the sub form the next time I open it as the join table is not completed when this was manually input.

I have spent a long time on google trying to work this out but can't for the life of me find the answer.

How should the form be set up so I can add all the details into either customer or property and have all 3 tables completed?

[June7]

With that setup, would need code to enter record in junction table.

Consider conventional options:

1. main form bound to Customers, subform bound to junction with combobox to select property

2. main form bound to Properties, subform bound to junction with combobox to select customer

3. single form bound to junction with comboboxes to select customer and property


If need to add a new customer or property 'on the fly' during data entry, use combobox NotInList event. Common topic.

[davegri]

Have a look at this:

Many-To-Many, two ways to update Junction table, includes Not In List handling (accessforums.net)

[davegri]

Sc0tt-davegri-v02.zip

This is an update to the db that I provided a month ago in response to your post at that time. It will popup a form to add the new property when an unknown street address is entered.