fill a listbox in access with an ADODB.RecordSet

peteh32
peteh32 used Ask the Experts™
on
I would like to populate an access 2000 listbox with data from an ADO recordset. In VB I believe you can use the listbox's .AddItem property and loop through the recordset, but I can't seem to find an .AddItem property in access.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
If recordset is based on data that can be queried from the database, easiest thing would be to make ListBox's RowSource the query being used to populate the recordset.

e.g.

Instead of...

set rs=dbconn.execute("SELECT myField FROM myTable")
' Populate listbox with data from rs

...do...

myListBox.rowsource="SELECT myField FROM myTable"


If you want to manually add rows to myListBox, must first set ListBox's RowSourceType to ValueList. ValueLists are delimited by comma and semi-colon, so a one-column ListBox with a ValueList RowSource Type would display RowSource, "1;2;3" as:

1
2
3

A two-column ListBox would display RowSource, "1;2;3;4" as:

1  2
3  4

So, to add a row, would:

myListBox.RowSource= myListBox.RowSource & ";" & newItem

If you search for RowSourceType in help then click on the link to Function Format there are examplease of the use of a function to populate the combo/list box.

Cheers, Andrew
what you have 'discovered' is that, though they LOOK alike, the controls in VB and Access are very much different.  The ComboBox in Access (Windows Forms 2.0 controls), has  RowSource and RowSourceType properties unlike VB, and an Access ComboBox can have MULTIPLE columns (set the ColumnCount property).  VB has an Additem method, where Access does NOT.  TO get the value from the lselected row of the ComboBox in Access, you use the .Column([ColumnNumber],Row) method, where columnNumber id 0-based, and Row will USUALLY be the .ListIndex property of the ComboBox.

A word of CAUTION, if you choose to make use of the RowSourceType="Value List", to explictly assign the values as you loop through your recordset.  The documentation SAYS that the field separator is the ";", but the "," ALSO acts as a field separator, so if you are formatting Numbers (Format$(Value,"#,###.00"), for instance), the "," in the formatted number becoms a field separator!!!!  Also, if you have a text field, and that field contains a comma- same result!!!!.

Arthur Wood


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial