Creating a simple listbox that will have the same features as a bound datalist box
The purpose of this example is to connect/display database records (using ADO) with a simple listbox. This is a work around to using bound controls. You will find your apps will run faster.
Original Author: Rob deCarle
Inputs
None.
Assumptions
You will need to draw a listbox on a form. You will need to create an Access database and an ODBC DataSource.
Returns
None.
Side Effects
None.
API Declarations
None.
Code
'**********************************************
'*Put the following code in any function.
'*This code opens database connection/recordset.
'*I have connected via datasource
'**********************************************
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cn.ConnectionString = "Provider=MSDASQL.1; _
Persist Security Info=False; _
Data Source=DSG_Input"
cn.Open
'*****************************************
'*SQL Statement to extract all customers*
'*from the database
'*****************************************
sql = "Select First_Name, Cust_ID from Customer _ Order by First_Name"
Set rs = cn.Execute(sql)
'*****************************************
'**Populates the listbox**
'*****************************************
With List1
Do While Not rs.EOF
.AddItem rs("First_Last")
rs.MoveNext
Loop
End With
'**********************************************
'*You now have a listbox containing the records
'*from your database
'**********************************************
'**********************************************
'*You will create an array that is dynamic to
'*your recordset. This will keep track of
'*the primary key as a boundColumn would in a
'*datalist box. This is for the purpose
'*of relational databases.
'*You will create the array the same size as the
'*listIndex count (number of records in
'*listbox).
'**********************************************
rs.movefirst
ReDim array1(List1.ListCount) As String
'*********************************************
'*This will now populate the array which is a
'*mirror image as the listbox, but with the
'*primary key.
'*********************************************
For i = 0 To List1.ListCount - 1
array1(i) = rs("Cust_ID")
rs.MoveNext
Next i
'**********************************************
'*We have now completed the listbox. You can
'*use this listbox the same way as you would a
'*datalist box. The following code will explain
'*how.
'***********************************************
'************************************************
'*To access the primary key relating to each
'*record in the list, put the following code in
'*the listbox "Click()" event. This explains how
'*to access the primary key stored in the array.
'************************************************
'**********************************************
'*list1.listIndex explains with record in the
'*list was clicked on. You use this to find
'*where in the array the primary key is stored.
'**********************************************
Private Sub List1_Click()
Dim Primary_1 as string
Primary_1 = array1(list1.listIndex)
Msgbox Primary_1
End Sub
'***********************************************
'*Conclusion*
'*Although this isn't as convenient as setting up
'*a bound datalist control, you will find it
'*will speed up things when using a large
'*database file.
'************************************************
Loading Comments ...
Comments
No comments have been added for this post.
You must be logged in to make a comment.