Out of Control Combo Boxes

[Dan M]

I organize volunteers for about 10 events every year. Since each volunteer
can attend multiple events and each event has multiple volunteers I started
with a junction table. This junction table has fields; VolunteerID (draws
form the volunteer table) and EventID (from the event table). My problem
comes once I start making a form for the Volunteers.

In order to see what events they've participated in, and add new events, I
put a sub-table in the form. But when someone goes to enter a new event for
a person, they get a list of every event we've ever run. Since we have
information for the past 10 years, and ten events a year, the list they have
to choose form gets quite long. Further, its very repetitive, looking
something like:
Event1 1999
Event1 2000
Event1 2001
Event1 2002
Event2 1999
Event2 2000
Event2 2001 and so on...

Is there a way that, instead of listing all the events like this, I could
have two fields, one with the 10 events names, and another with the year, and
still have it link back to the event table, which will still have separate
entries for each event each year?

 

[Dirk Goldgar]

I organize volunteers for about 10 events every year. Since each volunteer
can attend multiple events and each event has multiple volunteers I
started
with a junction table. This junction table has fields; VolunteerID (draws
form the volunteer table) and EventID (from the event table). My problem
comes once I start making a form for the Volunteers.

In order to see what events they've participated in, and add new events, I
put a sub-table in the form. But when someone goes to enter a new event
for
a person, they get a list of every event we've ever run. Since we have
information for the past 10 years, and ten events a year, the list they
have
to choose form gets quite long. Further, its very repetitive, looking
something like:
Event1 1999
Event1 2000
Event1 2001
Event1 2002
Event2 1999
Event2 2000
Event2 2001 and so on...

Is there a way that, instead of listing all the events like this, I could
have two fields, one with the 10 events names, and another with the year,
and
still have it link back to the event table, which will still have separate
entries for each event each year?

Why not set the rowsource of the combo box to a query that only returns the
events for the current year?