Solved

Removing empty items from combobox

Posted on 2003-12-01
5
590 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
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 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

22 Experts available now in Live!

Get 1:1 Help Now