Browse All Articles > Adding the option “all” to a ComboBox
This is such a frequent request that literally hundreds of pages can be found describing one technique or another to achieve the desired result. I particularly like one example from Microsoft, Adding (All) Options to Combo Boxes or List Boxes in Access 2007, which is by far the most convoluted solution I have ever seen, although it is remarkably generic as well. At the time of writing, a user comment on the page expresses the general feeling: “why does such a simple thing require 50 lines of code?”
The vital question that is often overlooked, or asked only in passing is: “why do you need this option, and how do you intend to use it?” Depending on the answer, the solution can be surprisingly simple.
The aim of the article is to show not one but several solutions to the request itself, and to concentrate equally on the way the “all” option is used.
A very simple demo file is included, but the article is hopefully understandable without downloading it.
ComboAddAll.mdb
A combo box can take all the values from the list, but it can also be left blank. The control will have the default value Null, and the combo will display nothing. A user can be instructed to delete the entry as well, provided this value — Null — is handled in a meaningful way by the application.
The main objection to this idea is that the combo box should display something at all times, to keep the user informed. I don't agree with this, and rather like empty controls when I have no particular need for them, but that's not the point. If the combo box displays its bound column, say a country code or the name of a category, there is an easy solution. The format “@;;(all)” will display “(all)” instead of nothing when the control's value is Null.
The same works for text boxes, incidentally, with the quite useful version “@;;[Blue](last name — required)”. If the bound column is a number, the formatting string would be “0;;;\0” to display zero when the field is blank. When the value is used, Nz() can convert Null to zero.
If the bound column isn't displayed this trick doesn't work, because the formatting is ignored. One could use conditional formatting to change the background colour, but that doesn't replace an explicit message in the control.
In any case, Null is a valid value unless it's prohibited by a validation rule or automatically changed through a Visual Basic event handler, typically `after update´.
When controls on a form are used as criteria in a query, Null values usually break the mechansim. This is the reason for the vast majority or requests to add “all” as an option. The query looks perhaps like this:
SELECT ProductID, ProductNAme, CategoryID
FROM Products
WHERE CategoryID=Forms!frmFilters!cboCategory
AND ...
In the query design grid, this becomes
Anyway, it is difficult to modify the criteria to allow records matching a specific criteria from a control or all records when the control is Null. Many apparently good solutions will totally mess up the query grid when the query is next opened. The demo file contains one query that looks perfectly reasonable in SQL view, but gets totally mangled by the query design grid. There is a good reason for that, and it must be accepted as an intrinsic limitation of the entire “criteria grid” concept.
To maintain compatibility with the query design grid, the trick is to switch the criteria and the field, like this:
This syntax really returns all records when a control is Null, even when a field is itself Null, and doesn't mess up the query grid. This trick is not nearly as useful for queries written and maintained completely in SQL view, naturally.
Once you know how to create a criteria where Null means “all”, you might no longer need the additional row in your combo boxes. Simply add this message: “A blank filter means that you do not want to filter on this field; use [Del] to delete the value from a combo box.”
Perhaps also “none”, “all active products”, “VIP only”, etc. It does make sense to offer in the same combo box individual choices as well as some predefined global filters.
Static Value Lists
This is the simple case. If the row source is static, you simply add the options in the list, and you are done. You will need to handle the additional cases, but it's no longer a combo box problem.
From Visual Basic, you also have the AddItem method, which you can use to add options dynamically, at any position in the list.
Using SQL
This is the most frequent answer, found as solution in numerous questions here on EE as well. To implement this solution, the query needs to be updated in SQL view. For many Access users, this is the first time they have to do so, but it isn't as hard as it might look.
If the query showing the list of categories looks like this:
SELECT CategoryID, CategoryName
FROM Categories
ORDER BY CategoryID
it can be updated like this:
SELECT CategoryID, CategoryName
FROM Categories
UNION
SELECT Null, '(all categories)'
FROM Categories
ORDER BY CategoryID
The first lines are left as they are. The UNION operator introduces a new query (there can be several, each preceded by UNION) which needs to have the same number of columns, but not column names (they are ignored). Ideally, the data type should match, or the entire column will be treated as text. This isn't a problem with combo boxes, because all columns will be treated as text anyway, but it might alter the sorting of the records.
The second FROM clause is not really used. It is a dummy because neither expression in the SELECT clause uses any field from the table. Any table will do, but the FROM clause is required. Only one record is created here, as if the query had the DISTINCT option. This is a side effect of the UNION operator, which makes the entire query DISTINCT (meaning “distinct values”).
To remove this side effect (not here, it's quite useful in our case) the operator UNION ALL can be used instead.
The last line is the original ORDER BY clause, which will still work because the name of the columns are determined by the first query. Note that the “(all categories)” row will be sorted first because Jet always sorts Nulls first. Null also doesn't alter the data type: it is compatible with all types.
Generating a Value List
Some people are more familiar with Visual Basic than with SQL. In order to gain full control over the row source, it is possible to replace the Table/Query row source with an equivalent Value List. The examples below are executed while the form is loading, and replace the query of a combo by an equivalent value list.
In any case, if the list displayed in the combo does not change during the life of the form, creating a static value list offers the most control and is quite often the most efficient also (in terms of speed and resources). The advantage over an entirely static list is that the row source is examined once, so that any changes in the base table(s) is visible the next time the form is opened, without any recoding.
List-fill User Defined Function
The page mentioned in the introduction does just that. There are simpler variants, and versions managing more than one list at a time; they are also the most versatile for example when dealing with external data sources or stored procedures, but I don't have anything more to say about them in this article.
Although the option has now been added, and is selectable in the combo box, it doesn't “stick”. The value is simply cleared, and no message remains. (You can observe this in the demo database, if you wish.)
In the examples above, I have always associated Null or a zero-length string (ZLS) to “all”. Due to the mechanics of a combo box, this value can never be selected or rather appear to be selected in the drop-down section. In other words, it works (Null is selected), but the visual feedback is not what it should be. The reason is that, in order for a row to be visibly selected, it needs to have a bound column matching the current value.
In practice, it is perfectly logical to have an option in a combo box to clear it. If the fingers are on the keyboard, the [Del] key is used, if the hand is over the mouse, “all” (or “none”) can be selected with the same result.
If “all” — or rather a message like “(all categories)” — should always be visible, this requires a few additional steps:
SELECT 0, '(all categories)'
and the `form open´ events to something like:
Finally, the example from the section “Using Null to mean `all´” can be changed like this:
I mentioned earlier that other options could be added. However, it is rather difficult to manage complex expressions in a query criteria without seriously impeding its performance. Other options become meaningful when the criteria is built block by block in a Visual Basic procedure. This is often used to build the `where condition´ argument used to open a form or a report.
Let's imagine that categories are numbered from 1 to n, and that one of the techniques above was used to add a row with 0, plus rows with -1, -2, etc., each representing some special filter. This is best handled by a Select Case statement:
Note that if too many special cases are handled this way, the result will be an inconsitent and potentially confusing interface. The combo mixes the selection of actual values used for filtering and entries which are in fact menu items, say from a hypothetical menu of “predefined searches”. This being said, a combo can very well be used as a menu, by acting immediately on the `after update´ event. Again, this would merit more explanations.
In over a dozen years of development, I have never felt the need to add the option “all” to a combo box. For some reason, a blank selection already means that to me, and my queries behave correctly with blank selection fields.
However, I was looking over the shoulder of a user the other day, much younger than myself, and she was obviously looking for a way to clear a combo box using the mouse. This is quite logical, now that I've seen it, and it does make perfect sense to offer an additional option to clear the selection in a combo, besides the [Del] key that it. When the option was added in the next update, it was felt as an improvement.
On the other hand, the fact that a blank selection control means “all” was never questioned. Pushing this idea to the extreme, would any developer expect to see “(all records)” below each field in the query design grid? Should the absence of a criteria mean “no records” or “Is Null”? Blank simply means “no action”, and it's very easy to skip over blank fields visually.
When an expert sees the question “how to add the `all´ option”, he or she should not only answer that question, but also the more important underlying question: “How to write a query that filters based on user input, but that can also show all records?”
There is certainly more to say about these questions, but I hope I have started to answer them in this article.
Markus G Fischer
(°v°)
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
This article is part of the EE-book unrestricted Access.
The vital question that is often overlooked, or asked only in passing is: “why do you need this option, and how do you intend to use it?” Depending on the answer, the solution can be surprisingly simple.
The aim of the article is to show not one but several solutions to the request itself, and to concentrate equally on the way the “all” option is used.
A very simple demo file is included, but the article is hopefully understandable without downloading it.
ComboAddAll.mdb
Note: The database in in Access 2000 format, but was created in Access 2007. After opening the database in an earlier version, if the demo doesn't work, please remove any missing reference: from Visual Basic Editor, choose (Tools | References) and uncheck any libraries marked as missing. Then find and check both libraries named “Microsoft DAO ?.? Object Library” and “Microsoft ActiveX Data Objects ?.? Library”; use the highest version if you find several versions.
Look! The option is already there!
A combo box can take all the values from the list, but it can also be left blank. The control will have the default value Null, and the combo will display nothing. A user can be instructed to delete the entry as well, provided this value — Null — is handled in a meaningful way by the application.
The main objection to this idea is that the combo box should display something at all times, to keep the user informed. I don't agree with this, and rather like empty controls when I have no particular need for them, but that's not the point. If the combo box displays its bound column, say a country code or the name of a category, there is an easy solution. The format “@;;(all)” will display “(all)” instead of nothing when the control's value is Null.
The same works for text boxes, incidentally, with the quite useful version “@;;[Blue](last name — required)”. If the bound column is a number, the formatting string would be “0;;;\0” to display zero when the field is blank. When the value is used, Nz() can convert Null to zero.
If the bound column isn't displayed this trick doesn't work, because the formatting is ignored. One could use conditional formatting to change the background colour, but that doesn't replace an explicit message in the control.
In any case, Null is a valid value unless it's prohibited by a validation rule or automatically changed through a Visual Basic event handler, typically `after update´.
Using Null to mean “all”
When controls on a form are used as criteria in a query, Null values usually break the mechansim. This is the reason for the vast majority or requests to add “all” as an option. The query looks perhaps like this:
SELECT ProductID, ProductNAme, CategoryID
FROM Products
WHERE CategoryID=Forms!frmFilter
AND ...
Field: CategoryID ProductName
Table: Products Products
Show: [x] [x]
Criteria: Forms!frmFilters!CategoryID Like Nz(Forms!frmFilters!txtName,'*')Anyway, it is difficult to modify the criteria to allow records matching a specific criteria from a control or all records when the control is Null. Many apparently good solutions will totally mess up the query grid when the query is next opened. The demo file contains one query that looks perfectly reasonable in SQL view, but gets totally mangled by the query design grid. There is a good reason for that, and it must be accepted as an intrinsic limitation of the entire “criteria grid” concept.
To maintain compatibility with the query design grid, the trick is to switch the criteria and the field, like this:
Field: Forms!frmFilters!CategoryID Forms!frmFilters!txtName
Table:
Show: [ ] [ ]
Criteria: Is Null Or CategoryID Is Null Or ProductNameThis syntax really returns all records when a control is Null, even when a field is itself Null, and doesn't mess up the query grid. This trick is not nearly as useful for queries written and maintained completely in SQL view, naturally.
Once you know how to create a criteria where Null means “all”, you might no longer need the additional row in your combo boxes. Simply add this message: “A blank filter means that you do not want to filter on this field; use [Del] to delete the value from a combo box.”
But I still want an “all” option!
Perhaps also “none”, “all active products”, “VIP only”, etc. It does make sense to offer in the same combo box individual choices as well as some predefined global filters.
Static Value Lists
This is the simple case. If the row source is static, you simply add the options in the list, and you are done. You will need to handle the additional cases, but it's no longer a combo box problem.
From Visual Basic, you also have the AddItem method, which you can use to add options dynamically, at any position in the list.
Using SQL
This is the most frequent answer, found as solution in numerous questions here on EE as well. To implement this solution, the query needs to be updated in SQL view. For many Access users, this is the first time they have to do so, but it isn't as hard as it might look.
If the query showing the list of categories looks like this:
SELECT CategoryID, CategoryName
FROM Categories
ORDER BY CategoryID
it can be updated like this:
SELECT CategoryID, CategoryName
FROM Categories
UNION
SELECT Null, '(all categories)'
FROM Categories
ORDER BY CategoryID
The second FROM clause is not really used. It is a dummy because neither expression in the SELECT clause uses any field from the table. Any table will do, but the FROM clause is required. Only one record is created here, as if the query had the DISTINCT option. This is a side effect of the UNION operator, which makes the entire query DISTINCT (meaning “distinct values”).
To remove this side effect (not here, it's quite useful in our case) the operator UNION ALL can be used instead.
The last line is the original ORDER BY clause, which will still work because the name of the columns are determined by the first query. Note that the “(all categories)” row will be sorted first because Jet always sorts Nulls first. Null also doesn't alter the data type: it is compatible with all types.
Generating a Value List
Some people are more familiar with Visual Basic than with SQL. In order to gain full control over the row source, it is possible to replace the Table/Query row source with an equivalent Value List. The examples below are executed while the form is loading, and replace the query of a combo by an equivalent value list.
' Uses a reference to “Microsoft DAO ?.? Object Library”
Private Sub Form_Load()
Dim strList As String
strList = ";(all categories);"
With cboCategory
With CurrentDb.OpenRecordset(.RowSource)
Do Until .EOF
strList = strList _
& !CategoryID & ";" _
& !CategoryName & ";"
.MoveNext
Loop
End With
.RowSourceType = "Value List"
.RowSource = strList
End With
End Sub' Uses a reference to “Microsoft ActiveX Data Objects ?.? Library”
Private Sub Form_Load()
With New ADODB.Recordset
.Open cboCategory.RowSource, CurrentProject.Connection, adOpenStatic
cboCategory.RowSourceType = "Value List"
cboCategory.RowSource _
= ";(all categories);" _
& .GetString(ColumnDelimeter:=";", RowDelimeter:=";")
End With
End SubIn any case, if the list displayed in the combo does not change during the life of the form, creating a static value list offers the most control and is quite often the most efficient also (in terms of speed and resources). The advantage over an entirely static list is that the row source is examined once, so that any changes in the base table(s) is visible the next time the form is opened, without any recoding.
List-fill User Defined Function
The page mentioned in the introduction does just that. There are simpler variants, and versions managing more than one list at a time; they are also the most versatile for example when dealing with external data sources or stored procedures, but I don't have anything more to say about them in this article.
Why doesn't the “all” option stick?
Although the option has now been added, and is selectable in the combo box, it doesn't “stick”. The value is simply cleared, and no message remains. (You can observe this in the demo database, if you wish.)
In the examples above, I have always associated Null or a zero-length string (ZLS) to “all”. Due to the mechanics of a combo box, this value can never be selected or rather appear to be selected in the drop-down section. In other words, it works (Null is selected), but the visual feedback is not what it should be. The reason is that, in order for a row to be visibly selected, it needs to have a bound column matching the current value.
In practice, it is perfectly logical to have an option in a combo box to clear it. If the fingers are on the keyboard, the [Del] key is used, if the hand is over the mouse, “all” (or “none”) can be selected with the same result.
If “all” — or rather a message like “(all categories)” — should always be visible, this requires a few additional steps:
it must be assigned a non-Null value, such as zero for numeric keys;
the combo should use this as default value;
the [Del] key must be trapped and disabled, or
the `after update´ event should reset the combo to its default value if it was cleard by the user
(this is demonstrated in the demo database);
(this is demonstrated in the demo database);
the query criteria using the combo must be updated.
The UNION query could be updated to:
SELECT 0, '(all categories)'
strList = "0;(all categories);"Finally, the example from the section “Using Null to mean `all´” can be changed like this:
Field: Forms!frmFilters!CategoryID Forms!frmFilters!txtName
Table:
Show: [ ] [ ]
Criteria: Is Null Or 0 Or CategoryID Is Null Or '*' Or ProductNameAbove “all” and other options
I mentioned earlier that other options could be added. However, it is rather difficult to manage complex expressions in a query criteria without seriously impeding its performance. Other options become meaningful when the criteria is built block by block in a Visual Basic procedure. This is often used to build the `where condition´ argument used to open a form or a report.
Let's imagine that categories are numbered from 1 to n, and that one of the techniques above was used to add a row with 0, plus rows with -1, -2, etc., each representing some special filter. This is best handled by a Select Case statement:
Select Case cboCategory
Case Is > 0
' normal case: a category was selected
varwhere = varwhere + " And " _
& "CategoryID = " & cboCategory
Case 0
' no filter
Case -1
' only “core” categories, numbered 1 to 10
varwhere = varwhere + " And " _
& "CategoryID Between 1 And 10"
Case -2
' only active categories
varwhere = varwhere + " And " _
& "CategoryID In (Select CategoryID From Categories Where Active)"
End SelectNote that if too many special cases are handled this way, the result will be an inconsitent and potentially confusing interface. The combo mixes the selection of actual values used for filtering and entries which are in fact menu items, say from a hypothetical menu of “predefined searches”. This being said, a combo can very well be used as a menu, by acting immediately on the `after update´ event. Again, this would merit more explanations.
Conclusion
In over a dozen years of development, I have never felt the need to add the option “all” to a combo box. For some reason, a blank selection already means that to me, and my queries behave correctly with blank selection fields.
However, I was looking over the shoulder of a user the other day, much younger than myself, and she was obviously looking for a way to clear a combo box using the mouse. This is quite logical, now that I've seen it, and it does make perfect sense to offer an additional option to clear the selection in a combo, besides the [Del] key that it. When the option was added in the next update, it was felt as an improvement.
On the other hand, the fact that a blank selection control means “all” was never questioned. Pushing this idea to the extreme, would any developer expect to see “(all records)” below each field in the query design grid? Should the absence of a criteria mean “no records” or “Is Null”? Blank simply means “no action”, and it's very easy to skip over blank fields visually.
When an expert sees the question “how to add the `all´ option”, he or she should not only answer that question, but also the more important underlying question: “How to write a query that filters based on user input, but that can also show all records?”
There is certainly more to say about these questions, but I hope I have started to answer them in this article.
Markus G Fischer
(°v°)
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
This article is part of the EE-book unrestricted Access.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (5)
Commented:
i've got to re-read this a few time to get the hang of it - when i'm actually needing to implement this in my project soon! so i can add personal relevance to it and be immersed in the actual situation.
thanks once again for the fantastic article! = )
Commented:
Commented:
Commented:
Commented:
This article was really helpful in getting a form passing parameters to a query to work the way I wanted.
However - I had a question on something that keeps happening when using the fixed query design (first section) -
When I enter the values in my form, it yields the right query with the fields filtered accordingly - however - one record - a particular location which is the first record in the referenced table is returned despite not matching those criteria - and more worryingly - it changes those fields to match the criteria. Any ideas on what's happening here?