populate listbox with recordset (1 Viewer)

eugzl

Member
Local time
Today, 18:04
Joined
Oct 26, 2021
Messages
100
Hi All.
In my form I'm populating ListBox thru RowSource. How to populate ListBox with Recordset? If it is possible. And what need to use ADO or DAO? I will appreciate for sample and detail explanation.

Thanks.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:04
Joined
Oct 29, 2018
Messages
18,792
Hi. Welcome to AWF!

Once you have a recordset object, you could try assigning it to the listbox using something like:

Set Me.ListboxName.Recordset = rs
 
Last edited:

eugzl

Member
Local time
Today, 18:04
Joined
Oct 26, 2021
Messages
100
Hi theDBguy. Thanks for reply.
I never populate form or controls using recordset but I would like to do it. I will appreciate if you will share sample file with brief explanation.
Thanks
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:04
Joined
Feb 19, 2002
Messages
35,935
I never populate form or controls using recordset but I would like to do it.
OK, but why? Access is a RAD (Rapid Application Development) tool and its best feature is bound forms. If you are avoiding bound forms for some reason, you probably ought to be looking for a different development platform since if you aren't using the RAD tools of Access, there is no reason to incur the overhead of a RAD tool.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:04
Joined
May 21, 2018
Messages
6,274
You can bind a Form to a DAO or ADODB recordset. Not too much explanation required
Set me.Recordset = SomeADOor DAOrecordset

I have never populated a control with an ADODB recordset, but do it all the time with DAO. I assume it will work. Like @theDBguy said
set me.SomeListbox.recordset = SomeRecordset

This example creates an ado recordset and binds it to a subform
this example binds a DAO recordset to a combobox
 
Last edited:

eugzl

Member
Local time
Today, 18:04
Joined
Oct 26, 2021
Messages
100
Hi Pat. Thanks for reply.
Sorry. You should be miss understood. Previously I binded forms to tables or queries by using properties rowsource. Now I would to create with advance way by using recordset and ask a help to figure out how it to do.
Thanks
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:04
Joined
Feb 19, 2002
Messages
35,935
Just because you can do something, doesn't mean you should.

Now I would to create with advance way by using recordset and ask a help to figure out how it to do.
As I mentioned, this is not an "advanced" or "better" way. It is non-standard and you are avoiding the best feature of Access.

Doing this just to find out how it works and how it is different is fine. I'm all for education. But just because the method is harder and more work does not in any way make it "better". When you work with Access, the best solution is the Access solution. The whole point of using a RAD tool is to take advantage of the things it does for you. If you think you can do something better than Access, you're probably wrong and you probably should be developing using a different tool.

There are situations where you might want to do this though and the most common reason is because you want to link to a remote SQL Server or other RDBMS over the web rather than on your LAN.
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:04
Joined
May 21, 2018
Messages
6,274
Why do you not want to use bound forms/controls?
A combobox or form that gets its values rowsource/recordsource from a recordset is no less a bound form or control as one that gets its rowsource/recordsource from a rowsource string. Apples and oranges.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:04
Joined
Feb 19, 2002
Messages
35,935
I'm not going to argue about this with you. You know what I'm talking about. Just because you can do something, doesn't mean you should. I would need a really good reason to use a DAO/ADO recordset rather than a bound query/table as the RowSource of a combo or listbox. Perhaps you can give us an example of when an ADO recordset would be BETTER than a bound query as the RowSource.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:04
Joined
May 21, 2018
Messages
6,274
ADO recordset would be BETTER than a bound query as the RowSource.
I already did. See my first example.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:04
Joined
Feb 19, 2002
Messages
35,935
I didn't say the technique wasn't possible or even possibly useful in some out of the ordinary situation. That doesn't make it somehow "better" than more standard solutions in all cases.

I didn't see anything about searching while typing or simulating a multi-value field in the question. Aren't you even the least bit curious as to why a brand new member wants to use a recordset in this way?

PS, the fake Multi-Value field is pretty slick. If MS wanted to add features to Access, this should be one of them.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:04
Joined
May 21, 2018
Messages
6,274
I did not say anything about being a better technique, I just said that setting the recordset dynamicallyis still creating a bound form or control. In no way is this unbound. In fact I do not even see a difference. I think it does the exact same thing just in a different order.

@Pat Hartman So let me ask you if there is anything fundamentally different. Assume in the onload event

Code:
me.recordsource = "Select * from tblOne"
or
Set me.recordset = currentdb.openrecordset("select * from tblOne",dbopendynaset)

Both cases create a bound form with a bound dao recordset. There is no difference in my opinion.

Since I can think of quite a few times I have done this, and really do not see any difference I did not question the OPs reason. Again I did not say it was better, but there is utility and good to know it is possible.

So a real world example. Lets say the OP has a form in datasheet view and sorts and selects a group of records using the pulldown sort and filter capability. The OP wants to display this sorted filtered list in another listbox somewhere. He can very easy pass the recordset to a listbox with this applied filter and sort. Simply set the recordset to the forms recordset. Since a listbox does not have a filter or orderby property to achieve the same the OP would have to write more complex code to build the SQL string from the original SQL and the filter and order by. This can be really complicated if the original SQL contains a where and Order by clause.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:04
Joined
Feb 19, 2002
Messages
35,935
So, you don't care why the OP wants to do this? OK.

I wonder how long the open recordset hangs around. I wonder if a new recordset is instantiated each time the code runs. Access probably isn't smart enough to reuse an existing one. It can't clean up after itself in many situations which is why it requires at least occasional compacting if you keep reusing the same FE. We do know that Access does proper cleanup when you use standard methods though and we know the kind of problems we can un into when we open recordsets from VBA and don't close them.
 

Users who are viewing this thread

Top Bottom