recordset as rowsource for listbox in access2000

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

Hi,

This propably has been posted before, but i need to know how i can use a
ado-based recordset, which calls a stored procedure, as source for a
listbox.

I know i can use it in a form's recordset but not for a listbox.

Greetings,

J
 
Jason said:
This propably has been posted before, but i need to know how i can use a
ado-based recordset, which calls a stored procedure, as source for a
listbox.

I know i can use it in a form's recordset but not for a listbox.
Click to expand...


Check to see if your version of Access has the Recordset
property for lint/combo boxes.
 
Jason said:
I'm using access 2000. how can i see if there such a property?
Click to expand...


Check the Object Browser (VBA window, View menu):
Library: Access
Search: ListBox

I don't have A2K so I can't check it for you.
 
Hi Marshall,

There is no recordset property in a2k. Can it be done in another way?

Greetings,

J
 
I don't think so, but I don't use ADO so I won't swear to
it.

Ought to be a way for you to munge things around and use the
RowSource though.
 
Hi Marshall,

How would you do it?


Marshall Barton said:
I don't think so, but I don't use ADO so I won't swear to
it.

Ought to be a way for you to munge things around and use the
RowSource though.
--
Marsh
MVP [MS Access]

There is no recordset property in a2k. Can it be done in another way?
Click to expand...
Click to expand...
 
Jason said:
How would you do it?
Click to expand...


Well, I don't know. I don't even know what "it" is.

I suggest that you reframe your question (without refering
to using a recordset) and post it as a new thread so someone
who works with your kind of environment can try to answer.
 
Marshall Barton said:
I don't think so, but I don't use ADO so I won't swear to
it.

Ought to be a way for you to munge things around and use the
RowSource though.
Click to expand...

PMFJI. Another possibility might be to use a user-defined rowsource
function, and use the recordset as the source of data within that
function. The recordset would have to be declared with a scope such
that it would be visible to the function.
 
The bottom line is that you can't use a recordset as the rowsource for a
list box in Access 2000, therefore you need to choose one of the other
methods available for getting data into the rows of a list box. If I
remember correctly, listboxes didn't have an AddItem method in Access 2000
either, right? So there are only three ways - a value list, a table/query,
or a call-back function.

If the recordset contains a relatively small number of rows, you could use a
value list - just loop through the recordset and concatenate values to a
semi-colon delimited string, and assign that string to the RowSource
property. Don't forget to change the RowSourceType property to Value List if
you go that route.

--
Brendan Reynolds (MVP)


Jason said:
Hi Marshall,

How would you do it?


Marshall Barton said:
I don't think so, but I don't use ADO so I won't swear to
it.

Ought to be a way for you to munge things around and use the
RowSource though.
--
Marsh
MVP [MS Access]

There is no recordset property in a2k. Can it be done in another way?


Jason wrote:
I'm using access 2000. how can i see if there such a property?


Check the Object Browser (VBA window, View menu):
Library: Access
Search: ListBox

I don't have A2K so I can't check it for you.
Click to expand...
Click to expand...
Click to expand...
 
Dirk said:
PMFJI. Another possibility might be to use a user-defined rowsource
function, and use the recordset as the source of data within that
function. The recordset would have to be declared with a scope such
that it would be visible to the function.
Click to expand...


Thanks Dirk and Brendan. It's very comforting to have such
powerful forces at my back ;-)

Is it really necessary to use a call back function? Like I
said, I'm not qualified to comment on this, but I would have
thought there would be a way to get to the stored procedure
through some kind of RowSource query(?)
 
Is it really necessary to use a call back function? Like I
said, I'm not qualified to comment on this, but I would have
thought there would be a way to get to the stored procedure
through some kind of RowSource query(?)
Click to expand...

I'm not qualified to advise on that either - the only work I've done with
Access and SQL Server sprocs has been in ADPs - and I think I've forgotten
most of that now! Perhaps Dirk may know, or someone else might like to
comment. But if no one answers here, it may be worth asking the question in
the odbcclientsvr newsgroup.
 
Marshall Barton said:
Is it really necessary to use a call back function? Like I
said, I'm not qualified to comment on this, but I would have
thought there would be a way to get to the stored procedure
through some kind of RowSource query(?)
Click to expand...

If it's okay to start with the stored procedure, and not with the
recordset that was opened from it, then *if* the stored procedure
doesn't take any parameters, there's no reason I can think of not to
create a pass-through query that executes the stored procedure, and use
that as the RowSource for the list box.

If the stored procedure requires parameters, though, I don't know any
way to specify those parameters on the fly short of modifying the .SQL
property of the pass-through query. Still, that may be feasible,
depending on Jason's situation.
 
Hi Brendan,

Thanks for replying. However i saw a AddItem method in the listbox in access
2000 if you referenced msforms 2.0 in the library.

Do you have a example to fill the listbox as you suggested.

Thnx

Brendan Reynolds said:
The bottom line is that you can't use a recordset as the rowsource for a
list box in Access 2000, therefore you need to choose one of the other
methods available for getting data into the rows of a list box. If I
remember correctly, listboxes didn't have an AddItem method in Access 2000
either, right? So there are only three ways - a value list, a table/query,
or a call-back function.

If the recordset contains a relatively small number of rows, you could use a
value list - just loop through the recordset and concatenate values to a
semi-colon delimited string, and assign that string to the RowSource
property. Don't forget to change the RowSourceType property to Value List if
you go that route.

--
Brendan Reynolds (MVP)


Jason said:
Hi Marshall,

How would you do it?


Marshall Barton said:
I don't think so, but I don't use ADO so I won't swear to
it.

Ought to be a way for you to munge things around and use the
RowSource though.
--
Marsh
MVP [MS Access]


Jason wrote:
There is no recordset property in a2k. Can it be done in another way?


Jason wrote:
I'm using access 2000. how can i see if there such a property?


Check the Object Browser (VBA window, View menu):
Library: Access
Search: ListBox

I don't have A2K so I can't check it for you.
Click to expand...
Click to expand...
Click to expand...
 
The AddItem method you're seeing is a property of an MSForms 2.0 listbox
control, not of an Access forms listbox control. Access listbox controls do
now have an AddItem property, but if I remember correctly I think it was new
in Access 2002.

Here's the example you asked for ...

Private Sub Form_Open(Cancel As Integer)

Dim rst As ADODB.Recordset
Dim strValueList As String

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Source = "SELECT LastName FROM Employees ORDER BY LastName"
rst.Open
Do Until rst.EOF
strValueList = strValueList & rst.Fields("LastName") & ";"
rst.MoveNext
Loop
rst.Close
Me.Combo0.RowSourceType = "Value List"
Me.Combo0.RowSource = strValueList

End Sub

--
Brendan Reynolds (MVP)

Jason said:
Hi Brendan,

Thanks for replying. However i saw a AddItem method in the listbox in
access
2000 if you referenced msforms 2.0 in the library.

Do you have a example to fill the listbox as you suggested.

Thnx

Brendan Reynolds said:
The bottom line is that you can't use a recordset as the rowsource for a
list box in Access 2000, therefore you need to choose one of the other
methods available for getting data into the rows of a list box. If I
remember correctly, listboxes didn't have an AddItem method in Access
2000
either, right? So there are only three ways - a value list, a
table/query,
or a call-back function.

If the recordset contains a relatively small number of rows, you could
use a
value list - just loop through the recordset and concatenate values to a
semi-colon delimited string, and assign that string to the RowSource
property. Don't forget to change the RowSourceType property to Value List if
you go that route.

--
Brendan Reynolds (MVP)


Jason said:
Hi Marshall,

How would you do it?


I don't think so, but I don't use ADO so I won't swear to
it.

Ought to be a way for you to munge things around and use the
RowSource though.
--
Marsh
MVP [MS Access]


Jason wrote:
There is no recordset property in a2k. Can it be done in another way?


Jason wrote:
I'm using access 2000. how can i see if there such a property?


Check the Object Browser (VBA window, View menu):
Library: Access
Search: ListBox

I don't have A2K so I can't check it for you.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top