Creating forms filtered on two criteria 1

[pauldt123]

I would greatly appreciate any advice on the following:

I have a 3 table layout;
Teams, Answers, Project.

Teams has primary key of TeamID.
Project has primary key of ProjectID.
Answers has composite key of TeamID and ProjectID.
Teams and Project are linked to Answers but not to each other.
There is no referential integrity on any join.

Essentially, I need it so a member of a team can view their team's questions/answers related to a specific project. I'm having troubles creating the forms to show the required data.

 

[PHV]

You may consider a mainform based on Teams and a continuous linked (on TeamID) subform based on Answers.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pauldt123]

Thanks for your swift reply PHV,

I really need visibility of all activity by project. How would I go about keeping Project as the mainform, and having individual command buttons for each team, linked to their own form which would display the relevant questions for that team and that project only? I'm struggling with selecting records by both TeamID and ProjectID if that makes sense.

Thanks again for your help.

 

[PHV]

Perhaps a mainform based on Project with an unbound combo to choose the TeamID.
Then the subform is linked on TeamID and ProjectID.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pauldt123]

I've just set up the unbound combo for team selection, and already have a combo for project selection on the same form. The record source for the mainform is the Project table, when I come to link the subform I can't link the master/child on both Project ID and Team ID because Team ID doesn't appear in the Project table. How can I configure the subform to link on both Project ID and Team ID? Apologies if this is an obvious question, and thanks again.

 

[PHV]

Link the subform's TeamID to the mainform Teams combo.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pauldt123]

Thanks for your help, it's working perfectly now.