How do you clear a listbox?

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

 

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

 

[Pat Hartman]

Yes, I have been following the thread. And so far, haven't seen any rational reason for loading the Listbox on the fly using code. It looks to me that the OP simply didn't know how to do it with a query which frequently happens with people who are fluent in other environments and who don't understand how Access works.

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

 

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

 

[Pat Hartman]

Good answer