[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Clear an unbound Listbox using VBA in Access 2007

Posted on 2009-05-07
25
Medium Priority
?
2,456 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
Industry Leaders: 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

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!

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

656 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