Please help filling a list box using ADO connection.

[ribhead]

I was just starting to figure out recordsets and ADO stuff in Excel when I thought I would try it in Access. I want to start out slow by simply adding a list of partnumbers to a list box from an AS400 database. Can someone bump me in the right direction. I'm trying to use the Object Browser but so far that's over my head and I'm totally lost.

Private Sub Command2_Click()
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String

cnt.Open "DSN=AMES01"
strSQL = "SELECT MFRFMR02 FROM DPNEW"
Me.List0.RowSource = cnt.Execute(strSQL)
MsgBox "Done."
End Sub

Thanks, Rib

Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm a frayed knot.

 

[PHV]

Something like this ?
cnt.Open "DSN=AMES01"
strSQL = "SELECT MFRFMR02 FROM DPNEW"
Set rst = cnt.Execute(strSQL)
Set Me.List0.Recordset = rst

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ribhead]

PHV,

Recordset is not a member of listbox is it? I get an alarm when I run this. Method or data member not found.

I kept trying to set the RowSource to rst but I don't know what I'm doing.

Option Compare Database

Private Sub Command2_Click()
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
cnt.Open "DSN=AMES01"
strSQL = "SELECT MFRFMR02 FROM DPNEW"
Set rst = cnt.Execute(strSQL)
Set Me.List0.Recordset = rst
End Sub

Thanks, Rib

Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm a frayed knot.

 

[PHV]

Property Recordset As Object
Membre de Access.ListBox

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ribhead]

No abla or however you spell it. I get a Method or data member not found alarm when I run the code and it highlights Recordset. Any thoughts?

Thanks, Rib

Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm a frayed knot.

 

[ribhead]

By the way I'm using Access 2000 I don't know if that makes a difference or not. I checked again in my Object Browser under Listbox and the members go ReadingOrder to RowSource and I don't see Recordset anywhere. Uhhhhhhhh Uncle?

Thanks, Rib

Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm a frayed knot.

 

[PHV]

And this ?
Private Sub Command2_Click()
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
cnt.Open "DSN=AMES01"
strSQL = "SELECT MFRFMR02 FROM DPNEW"
Set rst = cnt.Execute(strSQL)
While Not rst.EOF
Me.List0.AddItem = rst(0)
rst.MoveNext
WEnd
rst.Close
End Sub
You have to change the RowSourceType property of List0

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ribhead]

PHV

Man you got me baffled. I thought .AddItem was only used in Excel? Again I get a Method or Data Member not found alarm and it highlights .AddItem. I tried setting the RowSourceType to "Value List" then to "Field List" and finally to "Table/Query" No Lucky Lucky. HeHeHe Perhaps I'll take a break and shoot the person adjacent to me.

Thanks, Rib

Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm a frayed knot.

 

[PHV]

BTW, which version of access ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ribhead]

Access 2000. Is that what you mean.

Thanks, Rib

Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm a frayed knot.

 

[PHV]

Seems that the AddItem method appeared with access XP (aka 2002), sorry.
Another try, with RowSourceType set to "Value List":
Private Sub Command2_Click()
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strList As String
cnt.Open "DSN=AMES01"
strSQL = "SELECT MFRFMR02 FROM DPNEW"
Set rst = cnt.Execute(strSQL)
While Not rst.EOF
strList = strList & rst(0) & ";"
rst.MoveNext
WEnd
rst.Close
Me.List0.RowSource = strList
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ribhead]

O.K. Now we are getting somewhere. I put the dooby down and tried this and I got an alarm. "The setting for this property is too long." I feel like crack addict trying to score.

Thanks, Rib

Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm a frayed knot.

 

[PHV]

I think you're limited to circa 2000 bytes...
Either find a more decent version access or use a saved query based on a linked table or take a look in the VBA help on the RowSourceType topic to see how create a user defined function to populate the list.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

Yup - listbox rowsources in Access 2000 has a limit of 2048 characters. Some improvements in 2002 (xp), I tink the rowsource limit is 32 750 characters, the .AddItem and .RemoveItem methods also became available, and also the combos and listbox got a .recordsource property.

This link provides some info on using a callback function to populate the list (sample filling a list with files in a folder) List Files in a Folder. Such function circumvent the rowsource limitation of access 2000 list boxes.

Roy-Vidar

 

[RoyVidar]

Gee - meant "also the combos and listbox got a .recordset property" (xp that is)

Roy-Vidar