How do you clear a listbox?

[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.

 

[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

 

[MajP]

We got to 60 posts in the thread. Several days ago in post 18. I told you how to solve the problem

What are you talking about??? Have you not read any of this thread? No, your solution posted in 18 does not work in this case! Did you try your solution? If so post an example using the OPs database, like I did. It took sixty posts because I explained the nuances of what happens if you manually assign a recordset. I explain why setting the rowsource to "" or as your suggestion to use another query, does nothing. Your suggestion will not work since the OP created and assigned a recordset and did not use a rowsource. I provided a demo database that clearly shows, that your suggestion will not work for the OP.
Yes, If they populated the list with a rowsource, your suggestion would work, but that would be a trivial issue and there would not be any discussion. You may want to actually read and look at my solution since you may find it interesting.
Yes, everyone pretty much agrees that the OP should simply use a rowsource instead of setting the recordset, but that is not what they did. I also explained why at times you may want to set the recordset directly as the OP does. Further, there may be times when you use the additem method to populate the list, and that would require an even different solution as posted by @jdraw in post #16.

 

[MajP]

Please, Maj, tell me the reason that this listbox MUST be built with code rather than a query.

That makes NO sense! You are arguing a point that noone disagrees with. have stated multiple times that the user should use a rowsource and would have avoided this problem. So why would I now state otherwise? No where for this case do I suggest loading the recordset. Again my point is if you do load the recordset then changing the rowsource does nothing without other steps such as also settings the recordset to nothing.
I have, however, listed real examples when you would need to load the recordset.