Filtering Combo Boxes

  • Thread starter Thread starter Melissa
  • Start date Start date
M

Melissa

I have a form (frmRecruitInfo)with two combo boxes. The
first is School. The second is SchoolCoach. (These are
lookup fields from tblRecruits.)I need to directly link
the Recruit with a School and with a School Coach (there
may be more than one coach at each school).

When the user selects a School from the list, and then
clicks on the SchoolCoach dropdown button, I want only
the coaches from that school appearing as options to
select from. I thought I had it working but have run into
2 problems.

1. The user has to select a School (even if one is
already displayed) in order for data to appear in the
SchoolCoach list. (It carries forward the SchoolCoach
list from the previous record even if the School is
different, unless I select the School again.)

2. The only SchoolCoach displayed on any of the records
is the one just selected. I can go through and select
different Schools with the appropriate SchoolCoach. But
when I scroll back through the Recruit records, the info
on SchoolCoach has disappeared.

I have code running on the School After Update:
Me!SchoolCoach.Requery

The row source for SchoolCoach is:
SELECT qryLookupSchoolCoachAlphaList.CSchoolCoachLF,
qryLookupSchoolCoachAlphaList.SchoolName,
qryLookupSchoolCoachAlphaList.CSchoolCSZ,
qryLookupSchoolCoachAlphaList.SchoolCoachID,
qryLookupSchoolCoachAlphaList.SchoolID FROM
qryLookupSchoolCoachAlphaList WHERE
(((qryLookupSchoolCoachAlphaList.SchoolID)=Forms!
frmRecruitComprehensiveInformation!School));

The bound column for the SchoolCoach is Column 4.

Any ideas would be much appreciated!
 
Hi Melissa,

You also requery me.schoolcoach in the Current event of the form. Your data
is probably correct, just the recordset for the combo is wrong so the combo
shows as empty.
 
Back
Top