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

[Home]   [Back to the top]


1