Solved

Clear an unbound Listbox using VBA in Access 2007

Posted on 2009-05-07
25
2,417 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 250 total points
ID: 24330401
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!

 
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: 24330650
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Menus 6 51
Microsoft Access report help 4 35
Create a VBA Loop in Access to check records and create and action 7 51
Return Data From Website in Access 6 46
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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.

739 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