Solved

Clear an unbound Listbox using VBA in Access 2007

Posted on 2009-05-07
25
2,392 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
  • 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 - Access MVP) earned 250 total points
ID: 24330401
If it's not in multi-select mode


Me.YourListBoxName = Null

or

Me.YourListBoxName.RowSource = ""

mx
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

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…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

777 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