How to add "All" function to a combo box.

[Guest]

I would like to add “All†to a combo box to select all records listed in the
drop down box.

I have created a combo box within a form that will select “DayShiftâ€
“SwingShift†or “Grave†employees from a list of employees. The “Row Sourceâ€
type is a value list but I could change that if needed. If I select
“DayShift†“SwingShift†or “Grave†from the combo box, a query returns to
the form only names of employees within that category.

I would also like to have an “All†function that will show the entire list
of employees if it is selected in the combo box.

My query currently uses [forms]![frm_Employees]!Combo10] as the criteria.

I spent several hours today trying to follow instructions from Microsoft
Support at http://support.microsoft.com/kb/210290 That site has some code to
accomplish what I need. Unfortunately after following their directions my
combo box does not include the word “All†or any other options to select. I
believe I am pasting the code correctly into the form and setting the row
source correctly per instructions. I’m not sure if I am following the
instruction for the “Tag†property correctly.

I have also looked at information on using a Union Query Method but I don’t
understand it.

Any suggestions or simplifications on how to accomplish this will be
appreciated.

Quin

 

[Brendan Reynolds]

I generally find using the union query as explained in that article to be
the simplest solution in most circumstances. What is it about the
instructions for using a union query that you don't understand?

 

[Guest]

learn SQL kid

-Aaron

The Union Query apparently requires a SQL statement and I have zero
experience with SQL. Even if I figured out how to write the SQL statement
I'm not sure how the Union Query would be implimented to work with my
existing query that selects employees.

Your reply has caused me to do some investigation on this method. Maybe
it
will prove to be the best way to go.

I generally find using the union query as explained in that article to be
the simplest solution in most circumstances. What is it about the
instructions for using a union query that you don't understand?

--
Brendan Reynolds
Access MVP

I would like to add "All" to a combo box to select all records listed in
the
drop down box.

I have created a combo box within a form that will select "DayShift"
"SwingShift" or "Grave" employees from a list of employees. The "Row
Source"
type is a value list but I could change that if needed. If I select
"DayShift" "SwingShift" or "Grave" from the combo box, a query returns
to
the form only names of employees within that category.

I would also like to have an "All" function that will show the entire
list
of employees if it is selected in the combo box.

My query currently uses [forms]![frm_Employees]!Combo10] as the
criteria.

I spent several hours today trying to follow instructions from
Microsoft
Support at http://support.microsoft.com/kb/210290 That site has some
code
to
accomplish what I need. Unfortunately after following their directions
my
combo box does not include the word "All" or any other options to
select.
I
believe I am pasting the code correctly into the form and setting the
row
source correctly per instructions. I'm not sure if I am following the
instruction for the "Tag" property correctly.

I have also looked at information on using a Union Query Method but I
don't
understand it.

Any suggestions or simplifications on how to accomplish this will be
appreciated.

Quin

 

[Peter Hibbs]

Quin,

If the number of items in the Combo box is large and is likely to
change as users add more items then you should use a table to store
those items and give the users a method to add new ones.

If on the other hand, the number of items is small and is not likely
to change significantly then the method you are already using would be
preferable. To do what you want do as follows :-

In the Combo box control leave the Row Source Type as Value List.
In the Row Source property enter -
*;<< ALL >>;DayShift;DayShift;SwingShift;SwingShift;Grave;Grave
Enter 2 in the Column Count property.
Enter 0cm;1cm in the Column Widths property.
Enter "*" in the Default Value property (if you want the << ALL >>
item to be the default).
Make sure the Limit To List property is set to Yes.
In the After Update event you should requery the form or list box that
is displaying your data. Something like ListBox.Requery.
I enclosed the ALL in << >> to make sure it was the first item in the
list (although in your example it would be anyway).

In the query change the criteria to -
Like [Forms]![frm_Employees]!Combo10]

Incidentally, you should really give your controls more meaningful
names such as cboEmployees. It's not essential but it will save time
later when your database gets more complicated.

Hope that helps.

Peter Hibbs.

 

[Guest]

Peter, your answer was exactly what I was hoping for. I understood how to
impliment every step and it only took me a few minutes to test it and get it
working!

I'm not exactly sure why it works. I take it that the "like" statement in
the query lets the "*" work when it gets sent to the query and I think maybe
the 2 in the column count along with the 0cm;1cm in the Column Widths
property has something to do with hiding unwanted text in the combo box but
I'm not too sure why the Dayshift, Swingshift, Grave is repeated in the row
source.

Apparently you know exactly what you are doing because it works great. This
will be a great piece of knowledge for me to use on a regular basis.

Thanks,

Quin

Quin,

If the number of items in the Combo box is large and is likely to
change as users add more items then you should use a table to store
those items and give the users a method to add new ones.

If on the other hand, the number of items is small and is not likely
to change significantly then the method you are already using would be
preferable. To do what you want do as follows :-

In the Combo box control leave the Row Source Type as Value List.
In the Row Source property enter -
*;<< ALL >>;DayShift;DayShift;SwingShift;SwingShift;Grave;Grave
Enter 2 in the Column Count property.
Enter 0cm;1cm in the Column Widths property.
Enter "*" in the Default Value property (if you want the << ALL >>
item to be the default).
Make sure the Limit To List property is set to Yes.
In the After Update event you should requery the form or list box that
is displaying your data. Something like ListBox.Requery.
I enclosed the ALL in << >> to make sure it was the first item in the
list (although in your example it would be anyway).

In the query change the criteria to -
Like [Forms]![frm_Employees]!Combo10]

Incidentally, you should really give your controls more meaningful
names such as cboEmployees. It's not essential but it will save time
later when your database gets more complicated.

Hope that helps.

Peter Hibbs.

I would like to add “All†to a combo box to select all records listed in the
drop down box.

I have created a combo box within a form that will select “DayShiftâ€
“SwingShift†or “Grave†employees from a list of employees. The “Row Sourceâ€
type is a value list but I could change that if needed. If I select
“DayShift†“SwingShift†or “Grave†from the combo box, a query returns to
the form only names of employees within that category.

I would also like to have an “All†function that will show the entire list
of employees if it is selected in the combo box.

My query currently uses [forms]![frm_Employees]!Combo10] as the criteria.

I spent several hours today trying to follow instructions from Microsoft
Support at http://support.microsoft.com/kb/210290 That site has some code to
accomplish what I need. Unfortunately after following their directions my
combo box does not include the word “All†or any other options to select. I
believe I am pasting the code correctly into the form and setting the row
source correctly per instructions. I’m not sure if I am following the
instruction for the “Tag†property correctly.

I have also looked at information on using a Union Query Method but I don’t
understand it.

Any suggestions or simplifications on how to accomplish this will be
appreciated.

Quin

 

[Peter Hibbs]

Hi Quin,

I'm glad it worked OK for you but I can take no credit for this
really. It is the sort of thing which is described in the book called
Access 2000 Developer's Handbook, Volume 1 by Getz, Litwin and
Gilbert. I would recommend you purchase this book if you have any
interest in Access at all. As Aaron said, you should also try and
learn a bit of SQL (the book has a whole chapter on this subject), you
will find it invaluable later.

You are basically correct about how the Combo box works. This method
adds two columns to the Combo box, column 0 is hidden (by setting the
Column Width to 0) and is used to supply the required criteria to the
query.

Column 1 is visible and shows the various items for the user in the
Combo box. You need them twice because the query uses the first item
and the user sees the second item. Try changing one of the items to
something different and see what happens. If you change the second
Grave to xyz, for example, the query will work exactly the same except
the user will see xyz instead of Grave in the Combo box. If you change
the first Grave to xyz the user will see Grave in the Combo box but
the query will return no records because it cannot find a match for
xyz in the table.

The Like command in the query is used because the * character returns
a match on any value in the table which is what you want for the ALL
option.

Hope that is a bit clearer.

Good luck with your project.

Peter.

Peter, your answer was exactly what I was hoping for. I understood how to
impliment every step and it only took me a few minutes to test it and get it
working!

I'm not exactly sure why it works. I take it that the "like" statement in
the query lets the "*" work when it gets sent to the query and I think maybe
the 2 in the column count along with the 0cm;1cm in the Column Widths
property has something to do with hiding unwanted text in the combo box but
I'm not too sure why the Dayshift, Swingshift, Grave is repeated in the row
source.

Apparently you know exactly what you are doing because it works great. This
will be a great piece of knowledge for me to use on a regular basis.

Thanks,

Quin

Quin,

If the number of items in the Combo box is large and is likely to
change as users add more items then you should use a table to store
those items and give the users a method to add new ones.

If on the other hand, the number of items is small and is not likely
to change significantly then the method you are already using would be
preferable. To do what you want do as follows :-

In the Combo box control leave the Row Source Type as Value List.
In the Row Source property enter -
*;<< ALL >>;DayShift;DayShift;SwingShift;SwingShift;Grave;Grave
Enter 2 in the Column Count property.
Enter 0cm;1cm in the Column Widths property.
Enter "*" in the Default Value property (if you want the << ALL >>
item to be the default).
Make sure the Limit To List property is set to Yes.
In the After Update event you should requery the form or list box that
is displaying your data. Something like ListBox.Requery.
I enclosed the ALL in << >> to make sure it was the first item in the
list (although in your example it would be anyway).

In the query change the criteria to -
Like [Forms]![frm_Employees]!Combo10]

Incidentally, you should really give your controls more meaningful
names such as cboEmployees. It's not essential but it will save time
later when your database gets more complicated.

Hope that helps.

Peter Hibbs.

I would like to add “All” to a combo box to select all records listed in the
drop down box.

I have created a combo box within a form that will select “DayShift”
“SwingShift” or “Grave” employees from a list of employees. The “Row Source”
type is a value list but I could change that if needed. If I select
“DayShift” “SwingShift” or “Grave” from the combo box, a query returns to
the form only names of employees within that category.

I would also like to have an “All” function that will show the entire list
of employees if it is selected in the combo box.

My query currently uses [forms]![frm_Employees]!Combo10] as the criteria.

I spent several hours today trying to follow instructions from Microsoft
Support at http://support.microsoft.com/kb/210290 That site has some code to
accomplish what I need. Unfortunately after following their directions my
combo box does not include the word “All” or any other options to select. I
believe I am pasting the code correctly into the form and setting the row
source correctly per instructions. I’m not sure if I am following the
instruction for the “Tag” property correctly.

I have also looked at information on using a Union Query Method but I don’t
understand it.

Any suggestions or simplifications on how to accomplish this will be
appreciated.

Quin