Using a recordset to populate a list box

[Mel P]

There is undoubtably a better way to do it but here is
what I have done and what my problem is.

I have generated an ADO recordsetset (rst). I want to use
this to populate a list box. I set the list box
RowSourceType to value list. If I try to assign values
inside the loop with;

Do Until rst.EOF
lstBox.RowSource = rst!ID ";" & rst!Item & ";"
rst.MoveNext
Loop

This works BUT each row in the list box is the first row
and is overwritten at each visit, so I only get one row of
the final value!

If I create an array of the ID and Item values, in this
loop and then concatenate the values to make a long string
of pairs of the values with ";" separators and then make
this string the RowSource, this works - the list box is
populated. I can then select a value etc..

What have I missed please - how can I populate a list box
from an ADO recordset. (I know I can populate the listbox
from a query but I would like to know if I can do it this
way. Any suggestions welcome. Thanks

Mel P

 

[Guest]

You need a comma (,) between each row in the value list, the semi-colon just separates the values in one row.

 

[Guest]

Hi Dorian - Thanks - but - whatever I do with ; or , at
end or as a separator still only overwrites the value as
it loops and ends with one value in the list ?

-----Original Message-----
You need a comma (,) between each row in the value list,

the semi-colon just separates the values in one row.

 

[Chris Nebinger]

The problem is that as the recordset scrolls, you will
only ever have 1 item shown.

Try:

Dim strRowSource as String
Do Until rst.EOF
strRowSource=strRowSource & rst!ID & ";" & rst!Item
& ";"
rst.MoveNext
Loop
LstBox.RowSource = strRowSource



But, why not just set the RowSource to rst.Name?


Chris Nebinger

 

[Mel P]

Hello Chris;

Thanks -tried your suggestions - the first one is a neater
version of mine to generate a string - this works but has
a limit of 2000 characters or it generates an error.

The second - rowsource = rst!Name just populates one line
again.

Thanks anyway ! Mel