Bindind a listbox to a recordset using DAO in Access 2000 VBA

[markawmaw]

Hi All,

I can't seem to get this to work. I know the recordset is being
created and I know that is has the right number of records in it, but
the Listbox doesn't appear to bind itself; I end with a blank list
box.

The form (form1) has a listbox on it (list3) and a command buton
(command2)

Option Compare Database

Private Sub Command2_Click()

On Error GoTo exitSub

Dim wks As DAO.workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set wks = CreateWorkspace("", "admin", "", dbUseJet)
Set db = wks.OpenDatabase("c:\tmp\db1.mdb")
Set rs = db.OpenRecordset("SELECT cmp_s_name FROM tblCompany",
dbOpenSnapshot)


Forms("form1").Controls!List3.ColumnCount = rs.Fields.Count
Forms("form1").Controls!List3.RowSourceType = "Table/Query"
Forms("form1").Controls!List3.RowSource = "SELECT * FROM
tblCompany"
Forms("form1").Controls!List3.Requery

MsgBox rs.Fields.Count

Exit Sub

exitSub:
Debug.Print Err.Number
Debug.Print Err.Description
Set rs = Nothing
Set db = Nothing
Set wks = Nothing

End Sub

 

[Jezebel]

Unless things have changed since W2000, you can bind a listbox to a
recordset at all. RowSource works only in Excel, and only to bind the list
to a range of cells.

You'll need to populate the list by hand:

Do until rs.EOF
Forms("form1").Controls!List3.AddItem rs(1)
:

 

[markawmaw]

I'm not actually trying to bind to the recordset - maybe I worded that
badly.. Using the reocrdset proves I can connect to the remote
database and get records etc.... As far as the listbox is concerned I
am trying to bind to an SQL statement, which is allowable - but not
working.

Code snippet:

 

[Chad DeMeyer]

See below for in-line comments.

I'm not actually trying to bind to the recordset - maybe I worded that
badly..

Even so, as Jezebel stated: "RowSource works only in Excel, and only to bind
the list to a range of cells."

Using the reocrdset proves I can connect to the remote
database and get records etc.... As far as the listbox is concerned I
am trying to bind to an SQL statement, which is allowable - but not
working.

I've always created my recordsets with ADO rather than DAO, so I don't know
how much their object models have in common. With ADO, I use GetRows to
return a two-dimensional array. Then I use the .Columns property to copy
the contents of the array to the listbox.

arrData = rs.GetRows(Rows, Start, Fields)
Forms("form1").Controls!List3.Column() = arrData

Hope that helps
Regards,
Chad

 

[Jezebel]

I'm not actually trying to bind to the recordset - maybe I worded that
badly.. Using the reocrdset proves I can connect to the remote
database and get records etc.... As far as the listbox is concerned I
am trying to bind to an SQL statement, which is allowable - but not
working.

That's what 'binding' means, and you can't do it with a UserForm listbox.
(Perhaps you're thinking of VB controls, which do support this; but sadly,
VBA UserForms are different.)

As I said, you need to populate the listbox by hand, iterating the recordset
and using AddItem.