INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Combo Boxes

How do I add an <All> selection to my combo box drop-down? by Eupher
Posted: 3 Dec 03 (Edited 19 Jun 06)

A common use of the combo box is to set a criteria for a search, or to specify the records displayed in another form/subform or report.  You may want to give your users the option of selecting all choices as the criteria.  Here's one way to do it:

In this example I'm assuming a wizard-created combo based on a table named "tblMyTable" with fields "RecID" and "CompanyName" as the Row Source.  The first column is the bound column and is hidden.  After using the wizard to create the combo, the SQL of the combo will look something like this:

CODE

SELECT tblMyTable.RecID, tblMyTable.CompanyName FROM tblMyTable ORDER BY tblMyTable.CompanyName

Now to add an <All> choice to this, we'll change the combo's SQL to a union query:

CODE

SELECT tblMyTable.RecID, tblMyTable.CompanyName FROM tblMyTable UNION SELECT "*", "<All>" FROM tblMyTable ORDER BY tblMyTable.CompanyName

To directly edit the combo's SQL, just right-click in the Row Source field of the combo's property sheet and select "Zoom" from the pop-up menu.

The combo drop-down will now display a list of companies, with <All> at the top of the list.  You can now use the <All> selection in your code just as you would any other selection.  Specifying an asterisk as the bound value in this example will make it useful for queries in which you would use the "Like" operator.

Some things to remember:
1) There must be the same number of fields on both sides of the UNION statement.  In this example there are 2 fields on each side of the UNION clause.
2) Remember which column is the bound column, as that is most often the value you will use for processing purposes.  The order of the field items on the right side of the union is significant.  In this example, the first column is the bound column; hence the asterisk as the value of the first column on the right side in the example.
3) It may seem strange to include the table name in the right-hand side of the UNION query, as you're adding values instead of field names.  Don't worry about that.  A table name must be in the SQL, but in this case the table name doesn't matter precisely because you're adding values, not field names.  An alternative method is to create a single-record table for the right side of the union query, with fields to hold the * and <All> values.  In that case you would specify the field names and table name on the right side of the UNION clause instead of the actual values.

Thanks to ESquared for comments and clarification.

**Addendum**

If you are using your combo value as the criteria in a query, i.e.:

CODE

WHERE tblMyTable.CompanyName = [Forms]![frmMyForm]![cboMyCombo]
...and you don't wish to use the Like operator as suggested above, you can change the values on the right-hand side of the UNION clause in the combo's rowsource to "<All>", "<All>" - then change the criteria of your query to:

CODE

WHERE tblMyTable.CompanyName = [Forms]![frmMyForm]![cboMyCombo] Or [Forms]![frmMyForm]![cboMyCombo] = "All"
 (replace object names in blue with correct names from YOUR form)

With grateful acknowledgment to the expertise of PHV and TheAceMan1.

HTH,

Ken S.

Back to Microsoft: Access Forms FAQ Index
Back to Microsoft: Access Forms Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close