• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2473
  • Last Modified:

Clear an unbound Listbox using VBA in Access 2007

I'm fairly new to coding in Access 2007 and have come across a problem regarding listboxes.  This new project that I'm working on is using an Access front-end to a SQL server back-end and all of the forms that reside within the Access DB are unbound and populated on the fly.  The issue I am having is with my listboxes, I can't seem to find a way to clear the listbox whenever I want to refresh the data.  Here is the code I'm working on:

Public Sub PopulateListBox(strSQL As String, strColumn As String, lstListBox As ListBox)

***** CODE TO REFRESH/CLEAR LISTBOX WILL GO HERE *****

    Call OpenSQLServerConnection
   
    Set objRecordset = objDatabase.Execute(strSQL)
    Do While Not objRecordset.EOF
        lstListBox.AddItem (objRecordset.Fields(strColumn).Value)
        objRecordset.MoveNext
    Loop
    objRecordset.Close
   
    Call CloseSQLServerConnection
   
End Sub

This is a global function, utilized for any listbox for any form.  What I'm passing is the SQL statement used to query the database, the column name that holds the values of what I want displayed in the list, and the name of the listbox itself.  Everything works great, but I can't find a way to clear the list and then re-populate it.  I've denoted in the code where this statement(s) should go.  I thought it would be pretty simple but there are no methods for Clear/Remove, etc.

Thanks for the help!
0
BJTurner
Asked:
BJTurner
  • 12
  • 11
  • +1
1 Solution
 
adraughnCommented:
Did you try lstListBox..Clear?
0
 
adraughnCommented:
you can also use lstListBox.recordsource = ""
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
If it's not in multi-select mode


Me.YourListBoxName = Null

or

Me.YourListBoxName.RowSource = ""

mx
0
Technology Partners: 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!

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Time for a vacation to CA adria ... list boxes don't have a recordsource ..

mx
0
 
adraughnCommented:
you know what i meant - gimme a break - i don't use access anymore... :)
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
sure ... I know ... what you meant  ... but the end user won't

like said ... vacation time to CA!!!

mx
0
 
adraughnCommented:
.clear is easy to understand....
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
there is no such command as .Clear for an Access list box.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
and remember, we are on the 3 strike rule here in CA, LOL.

mx
0
 
BJTurnerAuthor Commented:
The lstListBox.RowSource = "" is what did it.  Thanks so much!

And I wish I could take a vacay to CA ... I'm in need of one as well.

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"Grade:  A - "The lstListBox.RowSource = "" is what did it.  Thanks so much!

And I wish I could take a vacay to CA ... I'm in need of one as well. "

You are welcome.

mx
0
 
adraughnCommented:
oops - vb again.... ok, ok, :)
0
 
adraughnCommented:
ahhh - it was removed in access 2003..... :P
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I don't think so ... .Clear never existed ... and if it had, it would not be removed.

You REEEEEALLY need a vacation ... Come out now while I have the time!

mx
0
 
adraughnCommented:
http://www.infoqu.com/100173-1-clear-list-box.html

two sources that say it did exist....

maybe you need a vacation to Cincinnati, Joe :)
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Trust me ... there has never been a .Clear.  I'm looking at A2002 here at work ... NO Clear.

Neither of those sources mention a .Clear method.

Wow ... you're in luck ... it's now happy hour time back in Cinn!!

mx
0
 
adraughnCommented:
"These two methods were not available in Access 97 or 2000.

Access 97 had a Clear method to clear a list box. This is no longer available, but it is very simple to clear the list box by coding:
      lstOutput.RowSource =            = the empty string
"

from:

http://www.cse.dmu.ac.uk/~mcspence/Differences%20between%20VBA%20in%20Access%2097%20and%20Access%202000.doc#List_Boxes
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I'm not sure that is correct ... and I cannot image a method like .Clear being removed.

mx
0
 
adraughnCommented:
they removed it because you can use the rowsource. clear is a valid method in vb still.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
That makes no sense.  Clearing via the row source would be a performance hit, requiring a requery of whatever data was in the row source.  Wherein setting the row source ="" certainly works, it's not the most efficient.  In any case, if the list box is in single select mode, the just setting it = Null will clear the selected item.

mx
0
 
adraughnCommented:
i didn't say it made sense.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
ok.  now, about that VACATION !!!!!!!!!!!!

0
 
adraughnCommented:
email me
0
 
RLUNTCommented:
Finally a dialog that really addresses the issue of giving sensless answers as a reason for being chided (picked upon). Thanks
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 12
  • 11
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now