Custom ADO recordsets and listboxes...

[James Cane]

Here's an interesting problem that someone might have an answer to...

Some time ago, I wrote a set of utility classes which wrap up the
custom row source function needed to add arbitrary items to a combo or
listbox. It all works nicely and allows me to do things such as
sorting by clicking on column headings.

Recently, all the machines here were upgraded to Access XP from 97 and
I thought it might be time to take advantage of the new ADO support.
As you might know, using a row source function is rather slow, even
with well optimised code, and scrolling through a large list box isn't
exactly the smoothest experience you can have. It's all a lot faster
if you can bind the listbox directly to some data.

So I wrote some code to create a disconnected recordset, populate it
with my data and attach it to a listbox. The thing is, I can get the
column headings to show correctly and I can even get the listbox to
display the correct number of rows, but all the contents of the rows
are blank, no matter what I do. Here's some simple code to
illustrate:

Create a new blank form. Add one list box and the following
code:

Private Sub Form_Load()

Dim objRS As ADODB.Recordset

Set objRS = New ADODB.Recordset

With objRS

Call .Fields.Append("Item1", adChar, 255)
Call .Open

Call .AddNew
.Fields("Item1").Value = "Test Line 1"
Call .AddNew
.Fields("Item1").Value = "Test Line 2"
Call .AddNew
.Fields("Item1").Value = "Test Line 3"
Call .Update

Set .ActiveConnection = CurrentProject.Connection
End With

Set List1.Recordset = objRS
End Sub

In case you're wondering, I have to set the ActiveConnection property.
Access apparently doesn't support binding to disconnected recordsets,
which I suspect that might be part of the problem, although I can't
see why.
When you look at the contents of the recordset in code, it all seems
to have worked successfully.

Anybody got any ideas?

Thanks in advance!

James

 

[Pavel Romashkin]

If I recall correctly, only a connected ADO rst can be used as data
source for a combo box or list box in Access 2002.
Try to use a table-based ADO rst to see if this will take care of the issue.

Pavel

 

[James Cane]

That's right, you can only use connected recordsets. I was hoping to
get round this by setting the ActiveConnection property on the
recordset after adding my data - meaning that the recordset would be
connected, but not actually taking its data from the connection. It
doesn't complain about me doing this and executes without errors, but
the data doesn't actually display in the listbox - instead, the
listbox contains the correct number of rows, but they're all blank. I
can query the recordset and see my data through code though.

Unfortunately, using a recordset based on data from a table isn't an
option as my data won't be coming from a table, or any form of
database storage. It's all generated dynamically through code.

 

[Pavel Romashkin]

If so, I am afraid you are "out of luck" - quoting the Access bible I
read this in. At least until the next release of Access... I am not sure
if standalone recordsets are allowed as cb sources in Access 2003.

Pavel