Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

636 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