Query By Form & Many-to-Many Relationship

[ronnroz]

Hi there,

I am not sure how this can be done and so I am at the mercy of all you gurus. Here is he situation:
I have 3 tables, tblPpl, tblOrg, tblMem.
tblPpl - ContactID primary key
tblOrg - OrgID primary key
tblMem - ContactID & OrgID primary key

tblPpl has one-to-many relation with tblMem
tblOrg has one-to-many relation with tblMem

An individual (from tblPpl) may belong to more than one Organization and an Organization may have more than one member, thus the many-to-many relationship.

I have almost everything setup and it works fine. Now I am trying to create a QBF (query by form) where the user can enter(or choose from a list) an organization and City or Province and it would show people living in that City or Province belonging to that organization. Please note that the organization may not be located in that particular City or Province just the people living in that city and province.

I hope I explained it properly.

Any help would be greatly appreciated. Thank you all so much.

 

[Len Boorman]

You are on the right lines

Basically I would use a combo box to select Organisation and a second combo box to select Area.

Use these two values within a query to select your data

The query would contain the three tables you mention

Go for it

len B

 

[ronnroz]

Thank LenB.
But I am not quite sure I know how to do that. I can create a combo box for the organizatons, but why combo box for Area?

If you could please be more specific and give me detailed instructions, I would appreciate that.

Thanks again.

 

[Len Boorman]

I tend to use combo boxes to avoid users having to type because that is an area of potential problem,

If you are going to search on Area then you must have a filed in a table called Area. If you use this as the record source then at least only those areas which exist may be selected and you avoid any typing issues.

Do you know how to use the builder or the syntax for the parameter in a query.

Forms![Form1]![Combo0] is the syntax to put in the query to use the contents of Combo0 which is on Form1.

Use your own names for fields and combo boxes.

So the On Click property of the second combo box has an Event Procedure

DoCmd.Openform "Search Results",acnormal,,,acReadOnly
DoCmd.Maximise

This will open the form that contains your search results that is based on the query that contains references to the two combo boxes

Okay ?

Len B