Binding Data to Controls with MS Access and ADO - Populating a combo box
(Page 4 of 6 )
As mentioned previously, the combo box will be populated in order to use it as a constraint to filter the data from the database for displaying to the user. Typically this may be the column "last name" in a database containing contact information, or it could be the telephone number in a video store database example. In the present example it will be the shipper's name as shown in the picture. Again the combo box is unbound in the design.
In a combo box you want to dump all of the data, and you do not require it to be scrollable. In order to get this functionality with reduced record locks, you need to use the read-only, forward-only type of cursor and close it immediately. The code for achieving this is shown here. The CursorType adOpenForwardOnly achieves a fast read of the data, and the recordset is closed immediately after retrieval. Although the code has been written to the click event of a button, it could have been a procedure called by the from load event itself.
In order to populate the combo box, row after row of data is collected in a string with each row separated by a semi-colon and the aggregated string is assigned to the combo box's row source property. The syntax for the value list is < value1;value2;value3;...>. At design time, make sure that the row source type of the combo box is set to value list, as shown in this picture. The other option for row source type is Table/Query, which is not applicable in this case.
Private Sub Command6_Click()
'-----fast data retrieval block----
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "C:Program FilesMicrosoft OfficeOFFICE11SAMPLESNorthwind.mdb"
End With
strsql = "Select * from Shippers"
With rst
Set .ActiveConnection = cnn
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open strsql
End With
'--------Aggregating the semi-colon delimited string from recordset--
Me.Combo9.RowSource = ""
Dim strCombo
strCombo = ""
While Not rst.EOF
'------generate [value list for row source] here----
strCombo = strCombo + rst.Fields.Item(1) & ";"
rst.MoveNext
Wend
'------Assigning aggregate string to row source---
Me!Combo9.RowSource = strCombo
Set rst = Nothing
Set cnn = Nothing
End Sub
Next: Displaying the combo box >>
More ASP.NET Articles
More By Jayaram Krishnaswamy
/ 11 
