Loading Multiple ComboBoxes or ListBoxes from the Same Recordset

If you are loading Multiple ComboBoxes or ListBoxes from the same recordset, a fast way to do this is to only loop the recordset once.

Here is a generic function that can load one ore more comboboxes at once:

 'Syntax:'    LoadCombos rsTemp, "UserName", "UserID", Combo1'    LoadCombos rsTemp, "UserName", "UserID", Combo1, Combo2'    LoadCombos rsTemp, "UserName", "UserID", Combo1, Combo2, Combo3'Public Function LoadCombos(Recordset As ADODB.Recordset, TextField As String, DataField As String, ParamArray ComboBoxes() As Variant)Dim fldText As ADODB.FieldDim fldData As ADODB.FieldDim ComboBox As ComboBoxDim Count As LongDim Index As Long    Count = UBound(ComboBoxes)    Set fldText = Recordset.Fields(TextField)    If Len(DataField) > 0 Then        Set fldData = Recordset.Fields(DataField)        Do Until Recordset.EOF            For Index = 0 To Count                Set ComboBox = ComboBoxes(Index)                ComboBox.AddItem fldText.Value                ComboBox.ItemData(ComboBox.NewIndex) = fldData.Value            Next            Recordset.MoveNext        Loop    Else        Do Until Recordset.EOF            For Index = 0 To Count                Set ComboBox = ComboBoxes(Index)                ComboBox.AddItem fldText.Value                Recordset.MoveNext            Next        Loop    End IfEnd Function

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: