CodeItBetter Programming Another VB Programming Blog

'Database - How to load the values of a recordset in a combo box
'This code is used for filling your combo boxes with the values of a recordset.
'you include this function in your module and just pass the combobox name and
'query which will fetch the data from recordset.

Public Sub FillListBox(ByRef lstControl As Object, ByRef strQry As String)
    'this routine is used to fill up a list box by passing 'the lstbox name and
    'query
    Dim lstControlObj As Object
    Dim Rst As ADODB.Recordset
    On Error GoTo VBError
    Set Rst = New ADODB.Recordset
    With Rst
        .Source = strQry
        .Open , AdoComp, adOpenDynamic, adLockReadOnly
    End With
    Set lstControlObj = lstControl
    lstControlObj.Clear
    If Rst.EOF = False Then
        Rst.MoveFirst
        While Rst.EOF = False
            lstControlObj.AddItem Rst.Fields(1)
            lstControlObj.ItemData(lstControlObj.NewIndex) = Rst.Fields(0)
            Rst.MoveNext
        Wend
        itsRecCount = 1
        itsProcessClickFlag = True
        lstControlObj.ListIndex = 0
    End If
 
Done:
    Rst.Close
    Set Rst = Nothing
    Exit Sub
 
VBError: ' Non-ADO error handler
    DisplayVBError
    GoTo Done
End Sub