Filtered displays on subform tabs (1 Viewer)

[brucemc777]

I hope that title I gave this doesn't completely confuse everyone.

I have a form based on a query. On that main form, it has a combobox so the user can select between ten different historical events (club meetings). In the first tab it shows all the people who were at the event in a datasheet display.

In the next tab, which is where I get totally lost, I want it to show only the people that were at that even AND were those who brought food, and the next tab brought equipment, and the third tab brought drinks (Yes, there is a field called txtAssignment and in it is entered Food, equipment, drinks...).

I noticed a couple examples similar that rebuilt sql strings to achieve this on opening each subform. Is the best way to proceed to use that method?

If so, do I take my first condition from my combobox that selected the event and then add on an "and" to the conditions? If so, I won't say that this is over my head, but if it isn't, I can say that it is darned close; any pointers are greatly appreciated.

I am relearning all of this after being away for about 10 years, and it is very frustrating to find how much I forgot. And embarrassing.

Thank-you.

 

[georgedwilkinson]

Table: Events
ID
Date
Description
Etc

Table: People
ID
NameStuff
Etc.

Table: Attendance
ID
PersonID (FK from People)
EventID (FK from Events)
Comments/Etc

Table: StuffThatCanBeBrought (a loose categorization just to demonstrate the concept)
ID
Description
Category
Etc

Table: EventSuppliers (or some such verbiage)
ID
PersonID (FK from People)
EventID (FK from Events)
StuffThatCanBeBroughtID (FK from StuffThatCanBeBrought)
CommentsEtc

Main form based on Events should be clear and easy to implement.

Tabbed pages for things related to the event with a tab for each thing you think is relevant. For instance, people who brought drinks. The query for the subform on this tab would look something like:

select ID, EventID, PersonID, StuffThatCanBeBroughtID, CommentsEtc
from EventSuppliers 
INNER JOIN StuffThatCanBeBrought 
ON EventSuppliers.StuffThatCanBeBroughtID = StuffThatCanBeBrought.ID
where StuffThatCanBeBrought.Category = 'Drinks';

(Your job to fix the code to work)

In this subform, set up the "ID" fields as combo-boxes and display the non-ID columns from the related tables in the combo-box (ask if you need help).

That should be plenty to get you started.