How do you clear a listbox?

[MajP]

@moke123, there are times where this makes coding easier. Imagine you have a form where you use the built in access features in data sheet view to filter and sort the records. You can then simply pass the reocordset to a listbox without trying to rebuild a Sql string based on the FilterBy and OrderBy properties. If you are working with an external db like SQL Server and you are not using linked tables, you can build an ADO recordset and bind to the listbox.
I use this a lot in my FAYT listboxes and comboboxes. It provides me a lot of flexibility since I do not to know antying about the rowsource or have to mess with a rowsource and try some complex string manipulation. You can apply a Filter to a DAO recordset as easy as you can a form. This is not as efficient, but makes the code very flexible to work with any rowsource.

 

[moke123]

Interesting. Thanks.

 

[MajP]

In the FAYT. I store the listbox's original recordset based on the original rowsource when I create the FAYT. You can then apply the filter to the original recordset and then use the recordset to create a new recordset. You are use to seeing SomeDb.OpenRecordset but you can also do SomeRecordset.OpenRecordset.

Recordset.OpenRecordset method (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

Set rsTemp = mRsOriginalList.OpenRecordset(dbOpenDynaset)
rsTemp.Filter = StrFilter
Set rsTemp = rsTemp.OpenRecordset

MRsOriginalList is the recordset the listbox/combobox has to start with when you open the form
RsTemp is a new local recordset = the mRsOriginalList
before opening RSTemp I set the filter
Then use the Original recordset to open the new recordset with a filter.

This just makes it very simple to apply the filter without worrying about how to parse the new string and handling things where the sql might have an Order By clause or worse built on a crosstab.

However, this is the problem that was seen. You can get a conflict doing this. Your recordsource and recordset are out of synch. That is why you solution to first set the recordsource "" or mine to requery ensures they are then synched. If you set the recordset to nothing and do not modify the rowsource to "" the old rowsource will win out and reassign the "nothing" to the old recordsource.

 

[moke123]

I'm familiar with filtered recordsets, just never seen them used with a listbox. Not sure I have any use for doing it that way but I'll keep it in mind just in case. I'll probably lay awake all night trying to think back to see if I should have used it somewhere.

 

[MajP]

I probably should have rephrased my post

there are times where this makes coding easier

to

there are times where this makes coding easier. But this is not one of those times.

Changing the rowsource as you show is definitely easier, less code, and less prone to issues like this.

 

[cheekybuddha]

Definitely over-engineered using a recordset in this instance!

Using dynamic SQL and perhaps a DCount() first to check how many records would be returned would be much simpler.

Interesting to note that you need to Requery after setting the the .Recordset = Nothing to remove the previous results

 

[Gasman]

Definitely over-engineered using a recordset in this instance!

Using dynamic SQL and perhaps a DCount() first to check how many records would be returned would be much simpler.

Interesting to note that you need to Requery after setting the the .Recordset = Nothing to remove the previous results

I never used Requery.?
In fact my understanding, is that when you change the recordset, an implcit requery happens as it gets the new recordset?

 

[cheekybuddha]

I never used Requery.?

No, MajP did.

And it is sufficient to just set .Recordset = Nothing and .Requery to clear the results. Setting .RowSource to empty string is not necessary in this situation

In fact my understanding, is that when you change the recordset, an implcit requery happens as it gets the new recordset?

So one would imagine, but it seems a shortcoming of Access implementation here when using a DAO.Recordset object that it doesn't mimic the long-understood behaviour when changing a listbox's .RowSource

 

[moke123]

In both situations you also may need to explicitly set the listbox value to null as it will retain the last selected value even though there is no recordset or rowsource.

 

[cheekybuddha]

Making the Row Source an empty string is a standard way of clearing the content of the listbox/combobox. It does not matter whether the Row Source is a value list or a query. I have not yet encountered a situation where it would not work.

Best,
Jiri

Have you tried this situation?

 

[MajP]

To be fair the OP kind of lead us astray because it was not clear or common to populate VIA CODE the recordset of the listbox. But this does highlight an issue if you do populate the recordset via code.

I've a listbox on a form which is populated with the RecordSet of a DAO query.

Bottom line if you use a Rowsource, Access will create the recordset based on the Rowsource. The recordset and the rowsource will always be "in synch."
If you populate the listbox by setting the Recordset you can get the rowsource and recordset "out of synch".
So basically you have to change both the recordset and the rowsource. Setting the recordset to nothing or setting the rowsource to "" does not work. You have to do both and in the correct order.
You can play with the demo to see.
So @Solo712 can now clearly see a situation where making the row source an empty string does not clear the list.

So bind the listbox to the Recordset.
Setting rowsource to "" does not clear
Setting recordset to Nothing does not clea
Setting the rowsource to "" then recordset to nothing does not work but Vice Versa does
Setting the rowsource to "" then requerying the list does not work

 

[cheekybuddha]

To be fair the OP kind of lead us astray because it was not clear or common to populate VIA CODE the recordset of the listbox. But this does highlight an issue if you do populate the recordset via code.

I've a listbox on a form which is populated with the RecordSet of a DAO query.

To be fair, the OP did in the above statement try and explain what was happening. Mostly, whether through ignorance or disbelief that anyone would use a recordset object to set a listbox contents, everyone assumed that wasn't the case.

Really this is just another example of MS' half-arsed implementation. The fact that setting a listbox's recordset = Nothing or setting a new recordset object does not clear the list unless you Requery is just a bug IMHO. If not, then making the fact that setting .RowSource exhibit one behaviour and setting .Recordset exhibit a different behaviour is just stupid.

 

[Gasman]

To be fair the OP kind of lead us astray because it was not clear or common to populate VIA CODE the recordset of the listbox. But this does highlight an issue if you do populate the recordset via code.

Bottom line if you use a Rowsource, Access will create the recordset based on the Rowsource. The recordset and the rowsource will always be "in synch."
If you populate the listbox by setting the Recordset you can get the rowsource and recordset "out of synch".
So basically you have to change both the recordset and the rowsource. Setting the recordset to nothing or setting the rowsource to "" does not work. You have to do both and in the correct order.
You can play with the demo to see.
So @Solo712 can now clearly see a situation where making the row source an empty string does not clear the list.
View attachment 103979
So bind the listbox to the Recordset.
Setting rowsource to "" does not clear
Setting recordset to Nothing does not clea
Setting the rowsource to "" then recordset to nothing does not work but Vice Versa does
Setting the rowsource to "" then requerying the list does not work

In post 38 I did row, then recordset and it worked for me.
Just tried again to make sure, and it still worked?

 

[MajP]

@Gasman,
That makes it even weirder and more confusing. The reason I think that worked for you is that you got lucky with a timing issue.
If you look at my demo it you Set the rowsource to "" and in another procedure set the recordset to nothing, it will not clear.
But I added buttons to do the exact same code in one procedure. That can only happen if there is some a timing issue and cannot be guaranteed. I added two other button to show that it is a timing issue

Private Sub Command10_Click()
Me.lstTest.RowSource = ""
Set Me.lstTest.Recordset = Nothing
End Sub

Private Sub Command11_Click()
Me.lstTest.RowSource = ""
DoEvents
Set Me.lstTest.Recordset = Nothing
End Sub

10 works and 11 does not. So in truth it is not that setting the rowsource to "" does not work, but the two properties are in conflict since they are in different "states" and "resetting" each other. The Do Events allow those conflicts to overwrite each other and without it, the rowsource = "" happens so fast it wins out.

Lesson learned. Do not set the Recordset via code unless you really have to because you can get some frustrating side effects.

 

[GK in the UK]

Thank goodness it's not just me that's been puzzled by this thread. MajP's demo works as he says it does, but it's not the behaviour I see. My listbox properties are: Control Source: empty, RowSource, empty, RowSourceType: Table/Query. RowSource is set in code, list populates, and when I set the RowSource to "", the list box clears. It's that simple.

 

[MajP]

but it's not the behaviour I see. My listbox properties are: Control Source: empty, RowSource, empty, RowSourceType: Table/Query. RowSource is set in code, list populates, and when I set the RowSource to "", the list box clears. It's that simple.

Just to be clear you do understand that everyone agrees there is no issue when setting the rowsource in code? And you are not going to see this behavior if you do. The issue only arises setting the Recordset directly.

 

[Pat Hartman]

You're right. This was way over - engineered.

FYI,
I see VB techniques in the app such as omitting the Me but including the .Value when referencing a control as well as loading the RowSource using a DAO loop.

Although there certainly are reasons for loading a combo or listbox's RowSource using a DAO loop, the most common and simplest solution is to use a query.

I bound the RowSource to a querydef that uses the textbox as its criteria. That has the form open empty. enter a value and press OK brings up the list. Clear clears the value from the textbox and requeries the RowSource and so it empties the listbox WITHOUT removing the column headers.

 

[Solo712]

So @Solo712 can now clearly see a situation where making the row source an empty string does not clear the list.

What @Solo712 clearly sees is that if you want to complicate things in Access, or any other software development platform for that matter, there are definitely opportunities to do it..

 

[Deleted member 73419]

Thanks for the input guys. Plenty to look back over...

Looks like setting the recordset to nothing and doing a requery seems to have done the trick

 

[Pat Hartman]

It is the RowSource we are talking about NOT a RecordSource. They are different.
Did you notice the difference in the example i posted? Controlling the contents of the RowSource rather than removing it, leaves the column headers in the listbox.

We got to 60 posts in the thread. Several days ago in post 18. I told you how to solve the problem. Then since you were still floundering, for some reason that escapes me, I bothered to build a working example to show you how to do this by controlling the selection criteria in the RowSource, which you seem to not have looked at.