Recordset / Listbox help

[CraigDiver]

First of all i'm a novice, so take it easy on me!

I have an access 2000 database with multiple tables and forms.

within one of my forms I wish to populate a listbox (listbox1) with values from a recordset (myRS) that I have created from a SQL query.

Function PopList()
    Dim dbs As Database
    Dim strSQL As String
    Dim myRS As Recordset
    
    Set dbs = CurrentDb
    strSQL = "SELECT * FROM customer"
    Set myRS = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
    
    'Populate listbox with recordset 'myRS'
    ' ???
    
End Function

All I want to do is populate the listbox with values from the recordset (binding or snapshot - I don't really mind, either will do)

How difficult can this be

Any help much appreciated to get me over this hurdle

CraigDiver

 

[KenHigg]

Can't you simply do this in using the 'row source' property of the list box?

 

[Banana]

Why are you doing this in a function? Usually that'd go in Form's open or button's onclick or other event, depending on how you want to update the listbox.

But if you want to use a function, you're missing one thing; the listbox itself.

Public Function PopList(AnyLst As Listbox)

then in the line where you're stuck, add this:

AnyLst.Recordsource = myRS

and when you call the function, you need to pass the listbox-

Call PopList(Me.MyListbox)

HTH.

 

[CraigDiver]

AnyLst.Recordsource = myRS

I think I have found the problem, when I am programming 'listbox1' recordsource is not an available.

listbox1.recordsource = myRS

the above is not recognised as valid syntax.

Do I need to go in to Tools-References and add something for this to work?

Getting there, eternally grateful!

Regards

CraigDiver

 

[KenHigg]

Still not sure why you're doing it this way, but...

Looks like:

listbox1.recordsource = myRS

May need to be something like:

me!listbox1.recordsource = myRS

???

 

[Pauldohert]

I don't see whats wrong with

!LisBox1.Rowsource = "SELECT * FROM customer;"

as Ken says.

 

[CraigDiver]

I don't see whats wrong with

!LisBox1.Rowsource = "SELECT * FROM customer;"

as Ken says.

Wow - works! you're a genius. However, only shows first field, I need it to display multiple fields like a table, how can I tweak it to do this?

Cheers

 

[Pauldohert]

If you go into the properties of the lisbox - you can set the number of columns and their widths.

You coulkd also alter the SQL to bring out the columns you want rather than *

 

[CraigDiver]

If you go into the properties of the lisbox - you can set the number of columns and their widths.

You coulkd also alter the SQL to bring out the columns you want rather than *

Thank you so much - it's amazing how such a simple problem can cause so much grief. specially when I find out the syntax is quite simple!

Thanks again

 

[KenHigg]

My bad with recordsource thing - Should have been the rowsource property like Paul posted

 

[Banana]

KenHigg, I'll join with you on confusing rowsource with recordsource. I now realized that I got it mixed. Only if they wouldn't make it so similar!

 

[kimosavi]

Hi. I have a similar problem and would like your help...

Been a access user/dev for years and my question is more about performance.

I am developing a tool in access that serves as a front end for the users. the data is store in SQL server.

the user will create project, each project is a database.

i need to load a listbox with usually 3000+ vendors.

this is how i am doing it...

mSQL = "SELECT dbo.PAYABLES_DETAILS_COMPLETE.VENDOR_CODE, dbo.PAYABLES_DETAILS_COMPLETE.VENDOR_NAME"
mSQL = mSQL + " FROM dbo.PAYABLES_DETAILS_COMPLETE LEFT OUTER JOIN"
mSQL = mSQL + " dbo.SUPPLIERS_SCOPE ON dbo.PAYABLES_DETAILS_COMPLETE.VENDOR_CODE = dbo.SUPPLIERS_SCOPE.VENDOR_CODE"
mSQL = mSQL + " GROUP BY dbo.PAYABLES_DETAILS_COMPLETE.VENDOR_CODE, dbo.PAYABLES_DETAILS_COMPLETE.VENDOR_NAME, dbo.SUPPLIERS_SCOPE.VENDOR_CODE"
mSQL = mSQL + " HAVING (dbo.SUPPLIERS_SCOPE.VENDOR_CODE IS NULL)"
mSQL = mSQL + " ORDER BY dbo.PAYABLES_DETAILS_COMPLETE.VENDOR_CODE"

Dim DB As NEW ADODB
Dim rs As ADODB.Recordset

Set rs = DB.SQL.Execute(mSQL) <---- takes a fraction of a second

.... after connected and RS loaded then I populate my list

If Not rs.EOF Then
Do Until rs.EOF
lst_vendor.AddItem rs.Fields("VENDOR_CODE").Value & ";" & rs.Fields("VENDOR_NAME").Value
rs.MoveNext
Loop
End If

.... this takes about 20-30 sec with 1500 records

I tried the same look using an array from GetRows() for the performance is the same. Oviously the problem is when executing the AddItem.

any thoughts on how to improve the performance loading the list???

I thought about bringing the data to a temp table and loading the list from the table... but, isn't it the same as loading from the RecSet?

let me know ideas or suggestions?

Thanks!