populate listbox with recordset

[eugzl]

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]

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

 

[eugzl]

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]

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

Faking a Multi Value Field

I have seen a few posts lately on people wanting to use multi valued fields. Most Access developers steer away from them and build their own related tables. Although they are properly normalized and work well, they can get very confusing even if you know the ins and outs. I wanted to see if I...

www.access-programmers.co.uk

this example binds a DAO recordset to a combobox

Find as we typed

Part of my form is to enter customer's info based on Combo Box. My combo is working if we type the beginning part of the customer name. How can we make it even 3 characters in the middle of the name, can auto find the closest names?. If any sample of database, could you uploaded it here please...

www.access-programmers.co.uk

 

[eugzl]

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]

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.

 

[MajP]

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]

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]

ADO recordset would be BETTER than a bound query as the RowSource.

I already did. See my first example.

 

[Pat Hartman]

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]

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

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]

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.