?
Solved

Removing empty items from combobox

Posted on 2003-12-01
5
Medium Priority
?
636 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 800 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

765 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