Adding a blank (null) to a combobox selection list.

Posted on 2007-04-03
Medium Priority
Last Modified: 2010-08-05
I have several combo boxes that act as filters for a listbox on the same form.
When the form opens, the combo boxes are blank and all records are shown in the listbox.

When a selection is made in one or more of the combo boxes, the list is filtered.  That all works fine.

However, I would like an easy way to set a combobox back to null, thus removing it's value as a filter.  I think the easiest way for a user to understand/accomplish would be if there was a "blank" selection in the combo box selections.  How can I add one?

Question by:snyperj
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3

Expert Comment

ID: 18843414
Create a command button and reference your combos and set the value = ""

i.e. cboName1="" ' place this or something similiar in the code for the command button.

Expert Comment

ID: 18843469
To a blank to combo box:

SELECT tblName.FieldName, tblName.FieldName2
FROM tblName
GROUP BY tblName.FieldName, tblName.FieldName2
ORDER BY tblName.FieldName
UNION select '', -1 from tblName.FieldName;

Expert Comment

ID: 18843479

SELECT tblName.FieldName, tblName.FieldName2
FROM tblName
GROUP BY tblName.FieldName, tblName.FieldName2
ORDER BY tblName.FieldName
UNION select '', -1 from tblName; ' if you add more columns (fields) to your combo then add same number of -1
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 39

Expert Comment

ID: 18843490
this is the same as adding <All> to the top of your combobox and users will likely understand that better. You do that by making the queries your comboboxes are using as rowsources be UNION queries. Can you give us an example of the SQL for one of the comboboxes?

You will likely also need to change you filtering code to ignore the <All>. Can you give us the filtering code for one of the combobs also?

LVL 75
ID: 18844661
Here is a general purpose function you can use in many different ways, not just for this:

1) Create a new VBA module
2) Paste in all the code below
3) Add the function call below to the On Dbl Click event - **on the property sheet* - of any control (text or combo box) you want the user to be able to clear. To clear the control, the user holds down the Control Key and double clicks!  Note that you can use a different vb key if you don't like 'Control'. However, 'Control / Clear' ... get the drift?


Note that you can select several controls (that you want to be able to clear) at one time, then paste in the line of code on the On Dbl Click event.  

Option Compare Database
Option Explicit

   'Return the state of keyboard key requested....
    Declare Function adiSWA_GetKeyState Lib "user32.dll" Alias "GetKeyState" (ByVal nKey%) As Integer

Function SSF_SetToNull() As Boolean

    SSF_SetToNull = False                                    'Initialize
    If SWA_GetKeyState(vbKeyControl) Then     'Check for Control+double click
        Screen.ActiveControl = Null                         'Null out active control
        SSF_SetToNull = True                                  'Successful
    End If

End Function

Function SWA_GetKeyState(vbKeyName)

    If adiSWA_GetKeyState(vbKeyName) < 0 Then
        SWA_GetKeyState = True
        SWA_GetKeyState = False
    End If

End Function

Expert Comment

ID: 18845465
What type of combo box is this?  If it's just a value list, you might get away with just putting two semi-colons.  ex.  apples:oranges;;squash    would display as



Not exactly null, but... If the list looks to a query or table, can you add a null entry there?
Otherwise, I'd go with the old standby "ALL" selection as others have suggested.
LVL 34

Accepted Solution

jefftwilley earned 2000 total points
ID: 18846333
how about

Private Sub Combo6_DblClick(Cancel As Integer)
Me.Combo6.Value = ""
End Sub

Author Comment

ID: 18846529
Thanks to all who suggested.  I found jefftwilleys the quickest and easiest to implement but I appreciate all who gave suggestions.
LVL 75
ID: 18846592

Not to take anything away from Jeff, but just note however, that does not set the combo box to Null.  It sets it to an Empty String ... and there is a big difference  ...  which may or may not make a difference in some cases.  
Your question asked "Easy way to set a combobox back to null" ...


Author Comment

ID: 18846616
understood. Thanks.

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question