?
Solved

Clear an unbound Listbox using VBA in Access 2007

Posted on 2009-05-07
25
Medium Priority
?
2,438 Views
Last Modified: 2013-11-28
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
Comment
Question by:BJTurner
[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
  • 12
  • 11
  • +1
25 Comments
 
LVL 13

Expert Comment

by:adraughn
ID: 24330384
Did you try lstListBox..Clear?
0
 
LVL 13

Expert Comment

by:adraughn
ID: 24330393
you can also use lstListBox.recordsource = ""
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1000 total points
ID: 24330401
If it's not in multi-select mode


Me.YourListBoxName = Null

or

Me.YourListBoxName.RowSource = ""

mx
0
Independent Software Vendors: 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!

 
LVL 75
ID: 24330411
Time for a vacation to CA adria ... list boxes don't have a recordsource ..

mx
0
 
LVL 13

Expert Comment

by:adraughn
ID: 24330417
you know what i meant - gimme a break - i don't use access anymore... :)
0
 
LVL 75
ID: 24330458
sure ... I know ... what you meant  ... but the end user won't

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

mx
0
 
LVL 13

Expert Comment

by:adraughn
ID: 24330479
.clear is easy to understand....
0
 
LVL 75
ID: 24330513
there is no such command as .Clear for an Access list box.

mx
0
 
LVL 75
ID: 24330523
and remember, we are on the 3 strike rule here in CA, LOL.

mx
0
 

Author Closing Comment

by:BJTurner
ID: 31579203
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
 
LVL 75
ID: 24330578
"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
 
LVL 13

Expert Comment

by:adraughn
ID: 24330589
oops - vb again.... ok, ok, :)
0
 
LVL 13

Expert Comment

by:adraughn
ID: 24330600
ahhh - it was removed in access 2003..... :P
0
 
LVL 75
ID: 24330618
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
 
LVL 13

Expert Comment

by:adraughn
ID: 24330663
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
 
LVL 75
ID: 24330705
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
 
LVL 13

Expert Comment

by:adraughn
ID: 24330789
"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
 
LVL 75
ID: 24330833
I'm not sure that is correct ... and I cannot image a method like .Clear being removed.

mx
0
 
LVL 13

Expert Comment

by:adraughn
ID: 24335422
they removed it because you can use the rowsource. clear is a valid method in vb still.
0
 
LVL 75
ID: 24337646
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
 
LVL 13

Expert Comment

by:adraughn
ID: 24337871
i didn't say it made sense.
0
 
LVL 75
ID: 24337950
ok.  now, about that VACATION !!!!!!!!!!!!

0
 
LVL 13

Expert Comment

by:adraughn
ID: 24338061
email me
0
 
LVL 1

Expert Comment

by:RLUNT
ID: 25833020
Finally a dialog that really addresses the issue of giving sensless answers as a reason for being chided (picked upon). Thanks
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

770 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