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!
BJTurnerAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
If it's not in multi-select mode


Me.YourListBoxName = Null

or

Me.YourListBoxName.RowSource = ""

mx
0
 
adraughnCommented:
Did you try lstListBox..Clear?
0
 
adraughnCommented:
you can also use lstListBox.recordsource = ""
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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 Access MVP)Database ArchitectCommented:
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 Access MVP)Database ArchitectCommented:
there is no such command as .Clear for an Access list box.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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 Access MVP)Database ArchitectCommented:
"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 Access MVP)Database ArchitectCommented:
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 Access MVP)Database ArchitectCommented:
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 Access MVP)Database ArchitectCommented:
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 Access MVP)Database ArchitectCommented:
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 Access MVP)Database ArchitectCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.