filtering subform based on info on main form

[Jeff @ CI]

In Access 2000, my main form lists data from a table holding info on an event
location to include a city and a state in seperate fields.
I have a subform in which I list reps who live in the area and (in a table)
have cities they will work in support of the event.
I want to filter the subform so that it displays only those reps whose
desired cities match either the EventCity or the EventState.
I have the main form set up to cycle through the events as records. I would
like to display the available reps with each event record automatically.

Any ideas or help would receive eternal thanks immediately!!!

Thanks,

Jeff

 

[Allen Browne]

So you assign "areas" to your reps, and cities to areas.
An event is in a city, and your subform should show the reps for the area
the event city belongs to.

Let's assume you have tables like this:
tblArea:
AreaID PK (primary key)

tblCity:
CityID PK
AreaID Relates to tblArea.AreaID

tblRep:
RepID PK
AreaID Relates to tblArea.AreaID

tblEvent:
EventID PK
CityID Relates to tblCity.CityID

Create a query using tblRep, tblCity, and tblEvent.
Join tblRep to tblCity on AreaID.
Join tblEvent to tblCity on CityID.
Select all fields from tblRep, and EventID from tblEvent.

Save the query, and use it as the source for your subform.
EventID will go in the LinkMasterFields and LinkChildFields.
The subform will now show the reps for the event in the main form.

If you need another approach, it is possible to use a subquery in the Filter
of a form, so you can get fields from other tables that are not in its
RecordSource. If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

 

[Jeff @ CI]

Allen - Thank you so much - I didn't have the "areas" table to make it all
work. You're AWESOME!!!!!!!!!!