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

Posted on 2007-04-03
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 500 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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.

717 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