Clear an unbound Listbox using VBA in Access 2007
Posted on 2009-05-07
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 *****
Set objRecordset = objDatabase.Execute(strSQL)
Do While Not objRecordset.EOF
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!