Home > How-To Library > Database

How to load the values of a recordset in a combo box

****************************************************************
*       © 2007 CodeItBetter http://www.codeitbetter.com        *
*          This notice MUST stay intact for legal use          *
****************************************************************

'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

If you would like to submit your code here please us. Do not forget to mention your name. We are always thankful to each and everyone of you who submitted their code here.