snyperj
asked on
Adding a blank (null) to a combobox selection list.
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!
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!
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;
SELECT tblName.FieldName, tblName.FieldName2
FROM tblName
GROUP BY tblName.FieldName, tblName.FieldName2
ORDER BY tblName.FieldName
UNION select '', -1 from tblName.FieldName;
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
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
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
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
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(vbKeyContr ol) 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(vbKeyNa me) < 0 Then
SWA_GetKeyState = True
Else
SWA_GetKeyState = False
End If
End Function
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(vbKeyContr
Screen.ActiveControl = Null 'Null out active control
SSF_SetToNull = True 'Successful
End If
End Function
Function SWA_GetKeyState(vbKeyName)
If adiSWA_GetKeyState(vbKeyNa
SWA_GetKeyState = True
Else
SWA_GetKeyState = False
End If
End Function
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to all who suggested. I found jefftwilleys the quickest and easiest to implement but I appreciate all who gave suggestions.
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
ASKER
understood. Thanks.
i.e. cboName1="" ' place this or something similiar in the code for the command button.