Solved

Removing empty items from combobox

Posted on 2003-12-01
5
632 Views
Last Modified: 2008-03-17
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.

Previous attempts:
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
            Me.cboFiltVal.RemoveItem (count)
        End If
    Next
0
Comment
Question by:theSpaceCow
[X]
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
5 Comments
 
LVL 3

Accepted Solution

by:
mccredb earned 200 total points
ID: 9851113
Try cutting & pasting the following

SQL = SQL & " WHERE NZ(Trim([" & Me.cboFilter.Value & "]),'')<>'' "



0
 
LVL 17

Expert Comment

by:Natchiket
ID: 9851270
Are you requerying cboFilVal on the afterupadate event of cboFilter ?
Are your backend tables Access or SQL server ?
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 9852005
Thats because your indexes are messed up

When you remove your listcount also -1 so you have to account for that also


 Dim count As Integer
Dim J as Integer
j =  Me.cboFiltVal.ListCount - 1
    For count = 0 To J
        If Left(Me.cboFiltVal.ItemData(count), 1) = " " Or Me.cboFiltVal.ItemData(count) = "" Then
            Me.cboFiltVal.RemoveItem (count)
         J = J - 1
        End If
    Next
0
 

Author Comment

by:theSpaceCow
ID: 9862602
Sorry to make you fine folks wait so long -- I got dragged away from this project by another imminent concern, and am only getting back to it now....

Mccredb -- Unfortunately, that doesn't work;  If I understand correctly, the NZ() and Trim() functions are happening to the field name in the Filter field, and not the values in that field being returned by the query.  Would it be feasible to open a recordset based on my query, then loop through that recordset, Trimming each value, and then query THAT recordset with a WHERE clause that would return everything except zero-length strings?

Natchiket -- MS Access backend.  I'm requerying FiltVal on its own GotFocus event.

Mikal613 -- Thanks, I hadn't realized that ListCount would return a different value each time the For statement was evaluated.  Nonetheless, your code doesn't work as provided.  What's weird is that whenever I run RemoveItem(), the loop always executes once and only once.  Remove that (the core of the loop, of course), and the loop performs properly.

Thanks for all your help, everyone, but I'm not quite there yet. Any ideas as to why cbo.FiltVal.RemoveItem(count) might be wrecking my control structure would be very much appreciated.
0
 

Author Comment

by:theSpaceCow
ID: 9875407
All apologies to Mccredb!  After finding a sneaky error elsewhere in this module that kept the code from working, I tinkered with it just a bit, and it worked smashingly!

thanks again! = )
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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

707 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