Removing empty items from combobox
Posted on 2003-12-01
I'm sure this is relatively easy, but I'm having a little trouble removing blank items from a combobox on a form in my Access database. The form has two comboboxes on it: The first (cboFilter) uses DAO.recordset and DAO.field to return every field within the specified table, and the second (cboFiltVal) uses "SELECT DISTINCT" in its onGotFocus event to get every value that has been entered into that field thus far. Just for efficiency and prettiness' sake, I want to strip out all the 'blank' entries in the combobox (which could be "", " ", " ", etc...), either in the SQL query's recordset itself (before setting it as the combobox's rowSource), or afterwards via RemoveItem(x). It's possible for valid entries to have a space within them, but they should never start with a space or be entirely blank, which is what I attempted to filter out below. Speed/efficiency is not a dire concern, so any solution that works will be perfect.
appending criteria to rowSource before attaching it to the control....
SQL = SQL & " WHERE " & Me.cboFilter.Value & " IS NOT NULL AND " & Me.cboFilter.Value & " NOT LIKE ' %' AND " & Me.cboFilter.Value & " <> '' "
attempting to remove incorrect values retroactively...
Dim count As Integer
For count = 0 To Me.cboFiltVal.ListCount - 1
If Left(Me.cboFiltVal.ItemData(count), 1) = " " Or Me.cboFiltVal.ItemData(count) = "" Then