Set Access 2003 Listbox recordset via code?

[kiln]

I thought we were supposed to be able to fill a listbox with a recordset
in A2003...but I can't seem to do it. I can tap the recordset property
of listboxes, but can't change etc the recordset. I'm trying the DAO rs,
the only examples I've seen use ADO, is that what it's about? Or am I
wrong, one must still use a callback function (or value string) to fill
a listbox with an adhoc list?

 

[Dirk Goldgar]

I thought we were supposed to be able to fill a listbox with a
recordset in A2003...but I can't seem to do it. I can tap the
recordset property of listboxes, but can't change etc the recordset.
I'm trying the DAO rs, the only examples I've seen use ADO, is that
what it's about? Or am I wrong, one must still use a callback
function (or value string) to fill a listbox with an adhoc list?

What have you tried? I've never done this before, but I just tried it
and this code seems to work fine in my test:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT ID, Description FROM
Table1")

Set Me.List0.Recordset = rs

 

[Dirk Goldgar]

What have you tried? I've never done this before, but I just tried it
and this code seems to work fine in my test:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT ID, Description FROM
Table1")

Set Me.List0.Recordset = rs

I should mention that it appears the list box's RowSourceType property
must be set to "Table/Query" for this to work. I should also mention
that I tested with Access 2002, not 2003.

 

[Allen Browne]

If you can get the records into a RecordSet, you can probably build a SQL
statement fairly easily, and assign it to the RowSource of the list box.

If the list really is made up of ad hoc values, set RowSourceType to "Value
List", and assign a string of the delimited values to the RowSource.

 

[kiln]

What have you tried? I've never done this before, but I just tried it
and this code seems to work fine in my test:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT ID, Description FROM
Table1")

Set Me.List0.Recordset = rs

Not sure why this fails, it's just like your code:

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

Set db = CurrentDb
Set rs = db.OpenRecordset("tblItem")
Set lstItem.Recordset = rs

The error is 7965 "The object you entered is not a valid recordset
property"

 

[kiln]

If you can get the records into a RecordSet, you can probably build a SQL
statement fairly easily, and assign it to the RowSource of the list box.

If the list really is made up of ad hoc values, set RowSourceType to "Value
List", and assign a string of the delimited values to the RowSource.

Hi Alan

Yes I know about the regular techniques. I don't even need to use a rs
as rowsource on any real project right now. But I was reminded of the
new feature (as I remembered it) from some reading I was doing and
thought I'd kick the tires and see it actually work...but so far no
luck.

 

[kiln]

I should mention that it appears the list box's RowSourceType property
must be set to "Table/Query" for this to work. I should also mention
that I tested with Access 2002, not 2003.

It is set to "Table/Query".

 

[Dirk Goldgar]

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

Set db = CurrentDb
Set rs = db.OpenRecordset("tblItem")
Set lstItem.Recordset = rs

The error is 7965 "The object you entered is not a valid recordset
property"

It looks to me like you can't use a table-type recordset. Try changing
this line ...

Set rs = db.OpenRecordset("tblItem")

to this ...

Set rs = db.OpenRecordset("tblItem", dbOpenDynaset)

 

[kiln]

It looks to me like you can't use a table-type recordset. Try changing
this line ...


to this ...

Set rs = db.OpenRecordset("tblItem", dbOpenDynaset)

Hi - that was it, I'd thought of checking that but didn't. Both of the
following will work, in addition to the SQL string setup you used:

Set rs = db.OpenRecordset("tblItem", dbOpenDynaset)
Set rs = db.OpenRecordset("qryItem")

Thanks

 

[Dirk Goldgar]

Hi - that was it, I'd thought of checking that but didn't. Both of the
following will work, in addition to the SQL string setup you used:

Set rs = db.OpenRecordset("tblItem", dbOpenDynaset)
Set rs = db.OpenRecordset("qryItem")

Right, because they both produce dynasets, not table-type recordsets.
For completeness, this would work:

Set rs = db.OpenRecordset("tblItem")

*IF* tblItem were an attached table, not a local one.