Querying a SubForm with a MANY to MANY relationship with MainForm

[mbrost]

Hi,

I am assuming Access cannot handle Many to Many relationships very well.

I have an Account form ("MainForm") with an embedded SubForm that displays only the Account Address with suite number, street number, street name, street type. Account to Address is a Many to Many relationship. My Account form's source is tblAccount, and the SubForm is built off of a query of 3 tables -- Querying Account, LinkTable, and Address table. The link between the SubForm and the "MainForm" is done via AccountID.

My problem is, when I query for an Address (say entering a street number of 25), I want to get back all the addresses with street number of 25 AND the corresponding Accounts that have street number 25 only

What it does is return the addresses with Street Number 25 and ALL the accounts regardless of what their addresses are!

Is there a way to do this?? Can I design a form without using a SubForm in this case, ie, display the account and its many addresses? Or does Access Forms not support Many to Many Relationships? I'm thinking the link table is causing the problems here.

Please advise -- this "simple" project is becoming quite the headache!

"edit" -- I also notice this problem with a 1 to Many relationship (the "many" portion being the subform)

Thanks for any help,

Mike

 

[Newman]

You cannot use Many-to-Many relations. You've got to use an 'in-between' table.
Exemple:
tblStudents >-----< tblCourses
becomes
tblStudents -----< tblSubscriptions >----- tblCourses

 

[Pat Hartman]

Subforms don't control the contents of mainforms. Mainforms control the contents of subforms with the master/child links.
1. remove the account table from the subform query.
2. use a combo on the subform that allows you to select available addresses. The combo will scroll as you type. So, as you type 25, it will scroll to all the rows that start with 25.

Take a look at my many-to-many sample in the samples forum to see how subforms work.

BTW, you will need to be VERY careful with your approach. You will notice that on my subforms in the example db, the fields from the related table (address in your case) are locked to prevent accidental change. If someone moves, you don't want the current address record to be over typed if anyone else is linking to it. You want to choose a different address or add a new one if necessary. You might want to ask the question- Should this address be changed for all people? For example, our church records would show three people at our address, so if my daughter requests an address change, you need to determine if all three addresses are changing or if she is simply leaving home.

 

[mbrost]

BTW, you will need to be VERY careful with your approach. You will notice that on my subforms in the example db, the fields from the related table (address in your case) are locked to prevent accidental change. If someone moves, you don't want the current address record to be over typed if anyone else is linking to it. You want to choose a different address or add a new one if necessary. You might want to ask the question- Should this address be changed for all people?

Good point Pat, thanks. I actually realized that yesterday, and am changing my Address to one to many -- its only a rare case that an address will have 2 accounts... I'll check your example too and respond if I have any more Q's.

Thanks again,

Mike