How to Populate a Listbox with a DAO Recordset.
For the sample code to run you will need to do the following
things:
1. Add a Reference to Microsoft DAO 3.5(VB5) or 3.51(VB6)
Object Library.
2. Add a ListBox and 3 Command Buttons.
3. Make sure you have the correct path to the Database. This
uses the sample database Nwind.mdb and the Employee table. If
you want to use a different database just change the
OpenDatabase and the OpenRecordset lines.
Option Explicit
Dim db As Database
Dim rs As Recordset
Dim i As Integer, j As Integer
'This is a sample of the diffrent ways to fill
a list box
Private Sub Command1_Click()
'This populates the listbox
with only one field from each record in the table
rs.MoveLast
rs.MoveFirst
List1.Clear
Label1.Visible = True
Label1.Caption = rs(1).Name 'This displays
the field name of the field displayed in the
Listbox
'This loops thru the records and adds the
field specified to the listbox
For j = 0 To rs.RecordCount - 1
List1.AddItem rs(1).Value & ""
rs.MoveNext
Next j
End Sub
Private Sub Command2_Click()
'This populates the listbox
with all the fields from one record in the table
List1.Clear
Label1.Visible = False
MousePointer = vbHourglass'this changes the
mousepointer so you know that the system is busy.
rs.MoveFirst
'This loops thru the fields in the first
record and adds the field name and value to the
listbox
For i = 0 To rs.Fields.Count - 1
List1.AddItem rs(i).Name & " :" & rs(i).Value
& ""
Next i
MousePointer = vbNormal'this changes the
mousepointer back to normal so you know it is done
proccessing.
End Sub
Private Sub Command3_Click()
'This populates the listbox
with all the fields from all records in the table
MousePointer = vbHourglass'this changes the
mousepointer so you know that the system is busy.
List1.Clear
Label1.Visible = False
'This loops thru the all recordsand all the
fields and adds the field name and value to the
listbox
For j = 0 To rs.RecordCount - 1
For i = 0 To rs.Fields.Count - 1
List1.AddItem rs(i).Name & " :" & rs(i).Value
& ""
Next i
rs.MoveNext
Next j
MousePointer = vbNormal'this changes the
mousepointer back to normal so you know it is done
proccessing.
End Sub
Private Sub Form_Load()
Command1.Caption="One Field"
Command2.Caption="One Record"
Command3.Caption="All"
'This Opens the Nwind.mdb database. You
would change this line if you want a different
database
Set db = DBEngine.Workspaces(0).OpenDatabase("c:/nwind.mdb",
False, False)
'This line opens the table. You would change
this line if you want a different table.
Set rs = db.OpenRecordset("Employees", dbOpenDynaset)
rs.MoveLast
rs.MoveFirst
End Sub