Solved

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

Posted on 2007-04-03
10
417 Views
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?

Thanks!
0
Comment
Question by:snyperj
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 7

Expert Comment

by:Imoutwest
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.
0
 
LVL 7

Expert Comment

by:Imoutwest
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;
0
 
LVL 7

Expert Comment

by:Imoutwest
ID: 18843479
Typo:

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
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 39

Expert Comment

by:stevbe
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?

Steve
0
 
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?

=SSF_SetToNull()

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
    Else
        SWA_GetKeyState = False
    End If

End Function
0
 
LVL 1

Expert Comment

by:ShesALive1
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

apples
oranges

squash

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.
0
 
LVL 34

Accepted Solution

by:
jefftwilley earned 500 total points
ID: 18846333
how about

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

Author Comment

by:snyperj
ID: 18846529
Thanks to all who suggested.  I found jefftwilleys the quickest and easiest to implement but I appreciate all who gave suggestions.
0
 
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" ...

mx
0
 

Author Comment

by:snyperj
ID: 18846616
understood. Thanks.
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

749 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