Solved

Removing empty items from combobox

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

911 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

21 Experts available now in Live!

Get 1:1 Help Now