Solved

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

Posted on 2007-04-03
10
414 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
 
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now