Combobox on Form: Select all function?

[sniki]

Hi,

I have a form with two comboboxes, one for "location" and one for
"department". However, I want to have the option to select all departments
for a certain location and the other way round.

Right now I have to select the location and a department and only those
results will show. Is it possible to have a "select all" option or an empty
option so that all departments or locations will show?

Thank you in advance.

 

[Douglas J. Steele]

Check http://www.mvps.org/access/forms/frm0043.htm at "The Access Web".

The trick, of course, is handling All correctly when selected...

 

[sniki]

I didn't really get that. The Row Source Type of my combobox is set to "Value
List". According to the article, I should be able to write "(All)"; in front
of the other options in "Row Source". However, when I open the form and
select all, nothing appears...

 

[Douglas J. Steele]

That was the point of my second sentence.

How are you using the combo box? What you need to do to accept "All" depends
on what you're doing with it. For example, if you're using the value of the
combo box for a filter, you need to ensure that when All is accepted, no
filter is set.

 

[sniki]

Yes I am using it as a filter. How do I make sure no filter is set when All
is selected?

 

[Douglas J. Steele]

How are you currently setting the filter?

 

[sniki]

I have a form with a subform. I dragged two fields from the wubform over to
the main form. If I then click on the field, the different options appear in
the properties window under "RowSource" with Value List as a type.

Was that what you meant?

 

[sniki]

In the table, I have created a field called "department" and given up field
values myself. That way I can select from the different departments that I
have choosen in advance.

The same field I then placed on my form, so when I press the arrow down, I
get the same pregiven values as in my table and it will then filter the
subform according to which of the pregiven values are choosen.

I have not given up any "after update" or "on click" function.

Does this help?

 

[Douglas J. Steele]

I'm sorry, but that makes no sense to me.

What's the Recordsource of the form being used as a subform? Is it a query
that refers to the combo box on the parent form?

 

[sniki]

The record source is a table...

I'm sorry, but that makes no sense to me.

What's the Recordsource of the form being used as a subform? Is it a query
that refers to the combo box on the parent form?

 

[Douglas J. Steele]

Sorry, but I fail to see how selecting something from the combo box can
possibly filter the results unless you're either using a query that refers
to the combo box as a criteria (which actually would still require that you
issue a Requery command) or else have code to create the filter.

 

[sniki]

Could you please explain to me how you would recommend doing it then?

For example should I create an empy Combobox and write a code for it, and in
that case what should the code be?

 

[Douglas J. Steele]

Typically what you do is put code in the AfterUpdate event of the combo box
to set a filter.

However, I'm afraid I don't really understand what you're trying to do,
which is why I was asking for more details.

Typically, you'd alter your code that sets the filter along the lines of:

If Me.MyComboBox = "All" Then
Me.Filter = vbNullString
Me.FilterOn = False
Else
Me.Filter = "SomeField = " & Me.MyComboBox
Me.FilterOn = True
End If