Filter Combo Box on a Form

[Robert T]

We have a One to Many form that displays all of our classes in the one
portion and every student registered for each class in the subform.

There is a combo box on the form that works very well, but contains too many
choices for my supervisor. When the user clicks on the combo box, it runs a
query that displays all scheduled classes in the future, plus all classes
that were completed within the last 90 days. In other words, all classes
greater than 90 days ago.

That's too many classes for my supervisor who wants the ability to enter a
starting/ending date so she can restrict the combo box results to let's say
February 1, 2008 to March 3, 2008.

Please note that we must keep the ability to go back 90 days to edit class
information. Very often instructors forget to update their classes and must
go back at a later date.

Any suggestions and/or tips on how to do this?

Thanks,
Robert

 

[Jeff Boyce]

Robert

I suppose one approach might be to add a control alongside the combobox.
That control would be for a start-checking-on-date.

In the AfterUpdate event for that textbox, you could re-query the combobox,
which would use a query involving the form's textbox as a selection
criterion. The trick would be to handle "All" dates, but perhaps your users
could just get used to entering a 'start date' before listing.

If you have instructors who's classes you want to be able to go back to,
what about the idea of including a combobox to select the instructor and
limit the existing combobox to display only that instructor's classes?

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Robert T]

Hi Jeff:

I like the idea of entering a Starting Date, but I love the idea of
filtering the combo box so the instructor sees only his/her classes. How can
I limit the classes in the combo box to one instructor?

Thanks,
Robert

 

[Robert T]

Jeff:

Update
--------
I liked your idea of filtering the choices so I changed the Combo Box query
by adding a parameter for the Instructor. When the user clicks on the combo
box, they type their name in the parameter box and then they see only their
classes. That works as designed.

Altough that's a big improvement, I have a better idea. Obviously with a
parameter box, users have to manually type their last name correctly and
that's prone to errors. So I created a form called "dfrmSelectAnInstructor"
which has it's own combo box which is a drop down list with all of the
instructor names. With this form, we don't have to worry about spelling
errors and the user doesn't have to type anything, they simply select their
name from a drop down list.

Well the above sounds good, but I'm doing something wrong so I would
definitely appreciate step by step help.

Thanks,
Robert

 

[Robert T]

I couldn't get that to work [opening the dialog form from the Combo Box] so I
tried it from a different angle. From the Main Menu, the user makes a
selection which takes them to:

dfrmSelectAnInstructor

When this form opens, they choose an Instructor from the drop down list and
click OK. The name chosen is given the value "vInstructor".

When the form opens up, all classes are listed, however, once the user
clicks on the Combo Box drop down, the only choices are those classes where
the Instructor equals vInstructor. This is exactly what I wanted, but there's
still one other minor problem.

Everything as described above works, however, I would like to filter the
form BEFORE the user clicks on the Combo Box drop down. When a user selects
an Instructor on "dfrmSelectAnInstructor", that brings them to the One to
Many form showing classes and the students registered for each class, I would
like to open the form filtered by the Instructor's class. In other words, the
Instructor will only see their classes.

I'm sure this is possible, but I've never done this before so i obviously
need help. Yes I'm proud of what I accomplished but this would be a nice
finishing touch.

Thanks,
Robert

 

[Jeff Boyce]

Robert

If I'm understanding your description, you are using a separate form for
each "filtering" combobox. Don't bother!

If you use "cascading comboboxes" (do a search on this expression), you can
use a single form, have the user make a selection in a first combobox, then
update the list shown in the second combobox to only those that "belong" to
the choice made in the first. You could even do this filtering a third (or
more) time, basing the 3rd on the values in first and second.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I couldn't get that to work [opening the dialog form from the Combo Box] so
I
tried it from a different angle. From the Main Menu, the user makes a
selection which takes them to:

dfrmSelectAnInstructor

When this form opens, they choose an Instructor from the drop down list
and
click OK. The name chosen is given the value "vInstructor".

When the form opens up, all classes are listed, however, once the user
clicks on the Combo Box drop down, the only choices are those classes
where
the Instructor equals vInstructor. This is exactly what I wanted, but
there's
still one other minor problem.

Everything as described above works, however, I would like to filter the
form BEFORE the user clicks on the Combo Box drop down. When a user
selects
an Instructor on "dfrmSelectAnInstructor", that brings them to the One to
Many form showing classes and the students registered for each class, I
would
like to open the form filtered by the Instructor's class. In other words,
the
Instructor will only see their classes.

I'm sure this is possible, but I've never done this before so i obviously
need help. Yes I'm proud of what I accomplished but this would be a nice
finishing touch.

Thanks,
Robert

Jeff:

Update
--------
I liked your idea of filtering the choices so I changed the Combo Box
query
by adding a parameter for the Instructor. When the user clicks on the
combo
box, they type their name in the parameter box and then they see only
their
classes. That works as designed.

Altough that's a big improvement, I have a better idea. Obviously with a
parameter box, users have to manually type their last name correctly and
that's prone to errors. So I created a form called
"dfrmSelectAnInstructor"
which has it's own combo box which is a drop down list with all of the
instructor names. With this form, we don't have to worry about spelling
errors and the user doesn't have to type anything, they simply select
their
name from a drop down list.

Well the above sounds good, but I'm doing something wrong so I would
definitely appreciate step by step help.

Thanks,
Robert

 

[Robert T]

Hi Jeff:

[Robert

If I'm understanding your description, you are using a separate form for
each "filtering" combobox. Don't bother!]

I truly appreciate your advice and will definitely research Cascading Combo
boxes, however, I don't understand why you disapprove of my solution.

1. The use clicks on a button to go to the Class Registration Form.
2. Prior to getting on there, a dialog form pops up asking the user to
select a name from a drop down list and then click OK. Their choice is
captured as a variable named vInstructor.
3. When they click OK they go to the form.
4. Jeff, the only thing that's changed is the Combo Box. When they click on
the combo box, the query uses the variable [vInstructor] to filter the query,
so it shows only those records matching the name selected.

That's exactly what the other instructors wanted and they seem very pleased
with how that works. It's easy, error free, and relatively quick. Why do you
disapprove of that approach?

Thanks,
Robert

Robert

If I'm understanding your description, you are using a separate form for
each "filtering" combobox. Don't bother!

If you use "cascading comboboxes" (do a search on this expression), you can
use a single form, have the user make a selection in a first combobox, then
update the list shown in the second combobox to only those that "belong" to
the choice made in the first. You could even do this filtering a third (or
more) time, basing the 3rd on the values in first and second.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I couldn't get that to work [opening the dialog form from the Combo Box] so
I
tried it from a different angle. From the Main Menu, the user makes a
selection which takes them to:

dfrmSelectAnInstructor

When this form opens, they choose an Instructor from the drop down list
and
click OK. The name chosen is given the value "vInstructor".

When the form opens up, all classes are listed, however, once the user
clicks on the Combo Box drop down, the only choices are those classes
where
the Instructor equals vInstructor. This is exactly what I wanted, but
there's
still one other minor problem.

Everything as described above works, however, I would like to filter the
form BEFORE the user clicks on the Combo Box drop down. When a user
selects
an Instructor on "dfrmSelectAnInstructor", that brings them to the One to
Many form showing classes and the students registered for each class, I
would
like to open the form filtered by the Instructor's class. In other words,
the
Instructor will only see their classes.

I'm sure this is possible, but I've never done this before so i obviously
need help. Yes I'm proud of what I accomplished but this would be a nice
finishing touch.

Thanks,
Robert

Jeff:

Update
--------
I liked your idea of filtering the choices so I changed the Combo Box
query
by adding a parameter for the Instructor. When the user clicks on the
combo
box, they type their name in the parameter box and then they see only
their
classes. That works as designed.

Altough that's a big improvement, I have a better idea. Obviously with a
parameter box, users have to manually type their last name correctly and
that's prone to errors. So I created a form called
"dfrmSelectAnInstructor"
which has it's own combo box which is a drop down list with all of the
instructor names. With this form, we don't have to worry about spelling
errors and the user doesn't have to type anything, they simply select
their
name from a drop down list.

Well the above sounds good, but I'm doing something wrong so I would
definitely appreciate step by step help.

Thanks,
Robert


:

Hi Jeff:

I like the idea of entering a Starting Date, but I love the idea of
filtering the combo box so the instructor sees only his/her classes.
How can
I limit the classes in the combo box to one instructor?

Thanks,
Robert

:

Robert

I suppose one approach might be to add a control alongside the
combobox.
That control would be for a start-checking-on-date.

In the AfterUpdate event for that textbox, you could re-query the
combobox,
which would use a query involving the form's textbox as a selection
criterion. The trick would be to handle "All" dates, but perhaps
your users
could just get used to entering a 'start date' before listing.

If you have instructors who's classes you want to be able to go back
to,
what about the idea of including a combobox to select the instructor
and
limit the existing combobox to display only that instructor's
classes?

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


We have a One to Many form that displays all of our classes in the
one
portion and every student registered for each class in the subform.

There is a combo box on the form that works very well, but contains
too
many
choices for my supervisor. When the user clicks on the combo box,
it runs
a
query that displays all scheduled classes in the future, plus all
classes
that were completed within the last 90 days. In other words, all
classes
greater than 90 days ago.

That's too many classes for my supervisor who wants the ability to
enter a
starting/ending date so she can restrict the combo box results to
let's
say
February 1, 2008 to March 3, 2008.

Please note that we must keep the ability to go back 90 days to
edit class
information. Very often instructors forget to update their classes
and
must
go back at a later date.

Any suggestions and/or tips on how to do this?

Thanks,
Robert

 

[Jeff Boyce]

Robert

Whatever works!

(I tend to use approaches with "fewer moving parts". Hence, a single form
with multiple comboboxes, rather than a series of linked/coordinated forms,
each with its own combobox (and necessary related queries).)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff:

[Robert

If I'm understanding your description, you are using a separate form for
each "filtering" combobox. Don't bother!]

I truly appreciate your advice and will definitely research Cascading
Combo
boxes, however, I don't understand why you disapprove of my solution.

1. The use clicks on a button to go to the Class Registration Form.
2. Prior to getting on there, a dialog form pops up asking the user to
select a name from a drop down list and then click OK. Their choice is
captured as a variable named vInstructor.
3. When they click OK they go to the form.
4. Jeff, the only thing that's changed is the Combo Box. When they click
on
the combo box, the query uses the variable [vInstructor] to filter the
query,
so it shows only those records matching the name selected.

That's exactly what the other instructors wanted and they seem very
pleased
with how that works. It's easy, error free, and relatively quick. Why do
you
disapprove of that approach?

Thanks,
Robert

Robert

If I'm understanding your description, you are using a separate form for
each "filtering" combobox. Don't bother!

If you use "cascading comboboxes" (do a search on this expression), you
can
use a single form, have the user make a selection in a first combobox,
then
update the list shown in the second combobox to only those that "belong"
to
the choice made in the first. You could even do this filtering a third
(or
more) time, basing the 3rd on the values in first and second.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I couldn't get that to work [opening the dialog form from the Combo Box]
so
I
tried it from a different angle. From the Main Menu, the user makes a
selection which takes them to:

dfrmSelectAnInstructor

When this form opens, they choose an Instructor from the drop down list
and
click OK. The name chosen is given the value "vInstructor".

When the form opens up, all classes are listed, however, once the user
clicks on the Combo Box drop down, the only choices are those classes
where
the Instructor equals vInstructor. This is exactly what I wanted, but
there's
still one other minor problem.

Everything as described above works, however, I would like to filter
the
form BEFORE the user clicks on the Combo Box drop down. When a user
selects
an Instructor on "dfrmSelectAnInstructor", that brings them to the One
to
Many form showing classes and the students registered for each class, I
would
like to open the form filtered by the Instructor's class. In other
words,
the
Instructor will only see their classes.

I'm sure this is possible, but I've never done this before so i
obviously
need help. Yes I'm proud of what I accomplished but this would be a
nice
finishing touch.

Thanks,
Robert



:

Jeff:

Update
--------
I liked your idea of filtering the choices so I changed the Combo Box
query
by adding a parameter for the Instructor. When the user clicks on the
combo
box, they type their name in the parameter box and then they see only
their
classes. That works as designed.

Altough that's a big improvement, I have a better idea. Obviously
with a
parameter box, users have to manually type their last name correctly
and
that's prone to errors. So I created a form called
"dfrmSelectAnInstructor"
which has it's own combo box which is a drop down list with all of the
instructor names. With this form, we don't have to worry about
spelling
errors and the user doesn't have to type anything, they simply select
their
name from a drop down list.

Well the above sounds good, but I'm doing something wrong so I would
definitely appreciate step by step help.

Thanks,
Robert


:

Hi Jeff:

I like the idea of entering a Starting Date, but I love the idea of
filtering the combo box so the instructor sees only his/her classes.
How can
I limit the classes in the combo box to one instructor?

Thanks,
Robert

:

Robert

I suppose one approach might be to add a control alongside the
combobox.
That control would be for a start-checking-on-date.

In the AfterUpdate event for that textbox, you could re-query the
combobox,
which would use a query involving the form's textbox as a
selection
criterion. The trick would be to handle "All" dates, but perhaps
your users
could just get used to entering a 'start date' before listing.

If you have instructors who's classes you want to be able to go
back
to,
what about the idea of including a combobox to select the
instructor
and
limit the existing combobox to display only that instructor's
classes?

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


We have a One to Many form that displays all of our classes in
the
one
portion and every student registered for each class in the
subform.

There is a combo box on the form that works very well, but
contains
too
many
choices for my supervisor. When the user clicks on the combo
box,
it runs
a
query that displays all scheduled classes in the future, plus
all
classes
that were completed within the last 90 days. In other words, all
classes
greater than 90 days ago.

That's too many classes for my supervisor who wants the ability
to
enter a
starting/ending date so she can restrict the combo box results
to
let's
say
February 1, 2008 to March 3, 2008.

Please note that we must keep the ability to go back 90 days to
edit class
information. Very often instructors forget to update their
classes
and
must
go back at a later date.

Any suggestions and/or tips on how to do this?

Thanks,
Robert

 

[Jeff Boyce]

(in case my response wasn't explicit enough, I don't "disapprove", I was
merely offering an alternate approach.)

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff:

[Robert

If I'm understanding your description, you are using a separate form for
each "filtering" combobox. Don't bother!]

I truly appreciate your advice and will definitely research Cascading
Combo
boxes, however, I don't understand why you disapprove of my solution.

1. The use clicks on a button to go to the Class Registration Form.
2. Prior to getting on there, a dialog form pops up asking the user to
select a name from a drop down list and then click OK. Their choice is
captured as a variable named vInstructor.
3. When they click OK they go to the form.
4. Jeff, the only thing that's changed is the Combo Box. When they click
on
the combo box, the query uses the variable [vInstructor] to filter the
query,
so it shows only those records matching the name selected.

That's exactly what the other instructors wanted and they seem very
pleased
with how that works. It's easy, error free, and relatively quick. Why do
you
disapprove of that approach?

Thanks,
Robert

Robert

If I'm understanding your description, you are using a separate form for
each "filtering" combobox. Don't bother!

If you use "cascading comboboxes" (do a search on this expression), you
can
use a single form, have the user make a selection in a first combobox,
then
update the list shown in the second combobox to only those that "belong"
to
the choice made in the first. You could even do this filtering a third
(or
more) time, basing the 3rd on the values in first and second.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I couldn't get that to work [opening the dialog form from the Combo Box]
so
I
tried it from a different angle. From the Main Menu, the user makes a
selection which takes them to:

dfrmSelectAnInstructor

When this form opens, they choose an Instructor from the drop down list
and
click OK. The name chosen is given the value "vInstructor".

When the form opens up, all classes are listed, however, once the user
clicks on the Combo Box drop down, the only choices are those classes
where
the Instructor equals vInstructor. This is exactly what I wanted, but
there's
still one other minor problem.

Everything as described above works, however, I would like to filter
the
form BEFORE the user clicks on the Combo Box drop down. When a user
selects
an Instructor on "dfrmSelectAnInstructor", that brings them to the One
to
Many form showing classes and the students registered for each class, I
would
like to open the form filtered by the Instructor's class. In other
words,
the
Instructor will only see their classes.

I'm sure this is possible, but I've never done this before so i
obviously
need help. Yes I'm proud of what I accomplished but this would be a
nice
finishing touch.

Thanks,
Robert



:

Jeff:

Update
--------
I liked your idea of filtering the choices so I changed the Combo Box
query
by adding a parameter for the Instructor. When the user clicks on the
combo
box, they type their name in the parameter box and then they see only
their
classes. That works as designed.

Altough that's a big improvement, I have a better idea. Obviously
with a
parameter box, users have to manually type their last name correctly
and
that's prone to errors. So I created a form called
"dfrmSelectAnInstructor"
which has it's own combo box which is a drop down list with all of the
instructor names. With this form, we don't have to worry about
spelling
errors and the user doesn't have to type anything, they simply select
their
name from a drop down list.

Well the above sounds good, but I'm doing something wrong so I would
definitely appreciate step by step help.

Thanks,
Robert


:

Hi Jeff:

I like the idea of entering a Starting Date, but I love the idea of
filtering the combo box so the instructor sees only his/her classes.
How can
I limit the classes in the combo box to one instructor?

Thanks,
Robert

:

Robert

I suppose one approach might be to add a control alongside the
combobox.
That control would be for a start-checking-on-date.

In the AfterUpdate event for that textbox, you could re-query the
combobox,
which would use a query involving the form's textbox as a
selection
criterion. The trick would be to handle "All" dates, but perhaps
your users
could just get used to entering a 'start date' before listing.

If you have instructors who's classes you want to be able to go
back
to,
what about the idea of including a combobox to select the
instructor
and
limit the existing combobox to display only that instructor's
classes?

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


We have a One to Many form that displays all of our classes in
the
one
portion and every student registered for each class in the
subform.

There is a combo box on the form that works very well, but
contains
too
many
choices for my supervisor. When the user clicks on the combo
box,
it runs
a
query that displays all scheduled classes in the future, plus
all
classes
that were completed within the last 90 days. In other words, all
classes
greater than 90 days ago.

That's too many classes for my supervisor who wants the ability
to
enter a
starting/ending date so she can restrict the combo box results
to
let's
say
February 1, 2008 to March 3, 2008.

Please note that we must keep the ability to go back 90 days to
edit class
information. Very often instructors forget to update their
classes
and
must
go back at a later date.

Any suggestions and/or tips on how to do this?

Thanks,
Robert