Using ADO to populate a List Box

[Joanne]

Hello, I'm a little new to using VBA to access other applications but I'd
like to populate a list box in a VBA user form with a column of names from a
field in Access. I opened the connection fine - it's just that I'm not sure
how to get the information (just a list of names) into the list box. Thank
you in advance for any help you can provide.

 

[Anne Troy]

Perhaps this will help you, Jo:
http://vbaexpress.com/kb/getarticle.php?kb_id=490
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

 

[Doug Robbins - Word MVP]

Private Sub UserForm_Initialize()

Dim db As DAO.Database

Dim rs As DAO.Recordset

Dim NoOfRecords As Long

' Open the database

Set db = OpenDatabase("D:\Access\ResidencesXP.mdb")

' Retrieve the recordset

Set rs = db.OpenRecordset("SELECT * FROM Owners")

' Determine the number of retrieved records

With rs

.MoveLast

NoOfRecords = .RecordCount

.MoveFirst

End With

' Set the number of Columns = number of Fields in recordset

ListBox1.ColumnCount = rs.Fields.Count

' Load the ListBox with the retrieved records

ListBox1.Column = rs.GetRows(NoOfRecords)

' Cleanup

rs.Close

db.Close

Set rs = Nothing

Set db = Nothing

End Sub


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Greg]

Doug,

I have no need to do this now, but got curious and so looked at your
method. I noticed that if the Access db has say 3 fields "First name,
middle name, last name" that the resulting ListBox will have three
columns and contain "First name, middle name, last name."

How would you code this if you only wanted tthe Listbox to display one
of the three fields?

Thanks.

 

[Doug Robbins - Word MVP]

I would be inclined to use the method previously given and control the
display of the data by setting the column width to zero for the fields that
you do not want to see by using the ColumnWidths attribute of the Listbox.

Alternatively, you can use

Private Sub UserForm_Initialize()

'allocate memory for the database object as a whole and for the active
record

Dim myDataBase As Database

Dim myActiveRecord As Recordset

'Open a database

Set myDataBase = OpenDatabase("E:\Access97\Ely\ResidencesXP.mdb")

'Access the first record from a particular table

Set myActiveRecord = myDataBase.OpenRecordset("Owners", dbOpenForwardOnly)

'Loop through all the records in the table until the end-of-file marker is
reached

Do While Not myActiveRecord.EOF

ListBox1.AddItem myActiveRecord.Fields("Owner")

'access the next record

myActiveRecord.MoveNext

Loop

'Then close the database

myActiveRecord.Close

myDataBase.Close

End Sub


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Greg]

Thanks Doug. I give both a try this weekend.

 

[Joanne]

thank you to everyone for all of your help. I think this is almost working.
However, I keep getting a "runtime 429" error at the point when it's trying
to open the database. It says, "ActiveX Component can't create object". I
thought that perhaps I'd loaded the wrong reference library. I loaded MS DAO
3.51 object library.

 

[Joanne]

I figured out the runtime 429 problem by putting in a provider for the ODBC
connection but unfortunately now I have a different problem.

Set cnMP = New ADODB.Connection
cnMP.Provider = "Microsoft.Jet.OLEDB.4.0"
stPath = "C:\program files\macpac\macpac90\app\"
Set rsAttyNames = New ADODB.Recordset
cnMP.Open stPath & "mppeople.mdb"
rsAttyNames.Open "tblPeopleHub", cnMP

This will open (I hope!) the correct table but now in order to choose names
from the field "fldfullname" whose attorney code is "-1", I'm guessing that I
have to add a SQL statement. But do I open the recordset again in order to
add the SQL statement? I'm not sure what the next step is

 

[Joanne]

Well I thought I fixed this runtime error by putting in the database jet
engine as the provider, but the code still errors out. Thanks for any help.
I feel incredibly frustrated by this.

 

[Joanne]

Thank you again. I figured the rest out.

Well I thought I fixed this runtime error by putting in the database jet
engine as the provider, but the code still errors out. Thanks for any help.
I feel incredibly frustrated by this.

 

[Greg Maxey]

Doug,

Both methods work equally well. Thanks.