Filtering a Drop Down List/CBO

M

Melissa

I have a main Recruit form with a subform for
evaluations. Each eval record contains a date and a site.
The site is a drop down box whose source is a query built
on a tournament site table.

The results are that each recruit is "linked" to certain
tournaments.

My question:

As new evaluations are entered, I only want to the drop
down box of tournament sites to list list current sites
(i.e. tournaments being held this year). However, I need
the previous evaluations sites for a recruit to remain
visible in the previous evaluations.

Any suggestions?

Thanks in advance!

Melissa
 
G

Graham Mandeno

Hi Melissa

Make two queries - one to list all the sites and another filtered to show
just the sites for the current year. Now you can make the decision in the
Form_Current event about which query to use. For example:

If Me.NewRecord or Year(Nz(EvalDate)) = Year(Date) then
cboSite.RowSource = "qryListCurrentSites"
Else
cboSite.RowSource = "qryListAllSites"
End If
 
M

Melissa

Graham:

Worked like a charm. THANKS!

Melissa
-----Original Message-----
Hi Melissa

Make two queries - one to list all the sites and another filtered to show
just the sites for the current year. Now you can make the decision in the
Form_Current event about which query to use. For example:

If Me.NewRecord or Year(Nz(EvalDate)) = Year(Date) then
cboSite.RowSource = "qryListCurrentSites"
Else
cboSite.RowSource = "qryListAllSites"
End If

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a main Recruit form with a subform for
evaluations. Each eval record contains a date and a site.
The site is a drop down box whose source is a query built
on a tournament site table.

The results are that each recruit is "linked" to certain
tournaments.

My question:

As new evaluations are entered, I only want to the drop
down box of tournament sites to list list current sites
(i.e. tournaments being held this year). However, I need
the previous evaluations sites for a recruit to remain
visible in the previous evaluations.

Any suggestions?

Thanks in advance!

Melissa
Click to expand...


.
Click to expand...
 
M

Melissa

Graham:

I was mistaken about it working like a charm. Although
the combo box pulls the right lists (for existing
evaluation records, the cboEvalSite box lists events from
all years and for new evaluation, the cboEvalSite box
lists events only from this current year), the existing
evaluation records no longer display the cboEvalSite that
had been selected for that specific evaluation in the
past. That field is now blank.

Any ideas?

Thanks!

Melissa
-----Original Message-----
Hi Melissa

Make two queries - one to list all the sites and another filtered to show
just the sites for the current year. Now you can make the decision in the
Form_Current event about which query to use. For example:

If Me.NewRecord or Year(Nz(EvalDate)) = Year(Date) then
cboSite.RowSource = "qryListCurrentSites"
Else
cboSite.RowSource = "qryListAllSites"
End If

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a main Recruit form with a subform for
evaluations. Each eval record contains a date and a site.
The site is a drop down box whose source is a query built
on a tournament site table.

The results are that each recruit is "linked" to certain
tournaments.

My question:

As new evaluations are entered, I only want to the drop
down box of tournament sites to list list current sites
(i.e. tournaments being held this year). However, I need
the previous evaluations sites for a recruit to remain
visible in the previous evaluations.

Any suggestions?

Thanks in advance!

Melissa
Click to expand...


.
Click to expand...
 
G

Graham Mandeno

Hi Melissa

There are two reasons I can think of for a bound combobox being blank. One
is that the bound ControlSource field is null, and the other (more likely)
is that the value in the bound field does not occur in the BoundColumn of
the combo's RowSource.

Are you sure that both your queries return the same columns in the same
order?

If you drop down the combo for a past record with a known EvalSite, does the
correct site appear in the list?

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Melissa said:
Graham:

I was mistaken about it working like a charm. Although
the combo box pulls the right lists (for existing
evaluation records, the cboEvalSite box lists events from
all years and for new evaluation, the cboEvalSite box
lists events only from this current year), the existing
evaluation records no longer display the cboEvalSite that
had been selected for that specific evaluation in the
past. That field is now blank.

Any ideas?

Thanks!

Melissa
-----Original Message-----
Hi Melissa

Make two queries - one to list all the sites and another filtered to show
just the sites for the current year. Now you can make the decision in the
Form_Current event about which query to use. For example:

If Me.NewRecord or Year(Nz(EvalDate)) = Year(Date) then
cboSite.RowSource = "qryListCurrentSites"
Else
cboSite.RowSource = "qryListAllSites"
End If

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a main Recruit form with a subform for
evaluations. Each eval record contains a date and a site.
The site is a drop down box whose source is a query built
on a tournament site table.

The results are that each recruit is "linked" to certain
tournaments.

My question:

As new evaluations are entered, I only want to the drop
down box of tournament sites to list list current sites
(i.e. tournaments being held this year). However, I need
the previous evaluations sites for a recruit to remain
visible in the previous evaluations.

Any suggestions?

Thanks in advance!

Melissa
Click to expand...


.
Click to expand...
Click to expand...
 
Top