VBA Programming -bind data with Combo Box

Posted on 2008-11-04
Last Modified: 2013-11-28
Give some ideas on how to bind data with combo box . I need Codings too.
Question by:logicslab
    LVL 46

    Accepted Solution

    Hi logicslab,

    Have a look at the attached code

    Good Luck!

    Public Sub PopulateLBWithData(DBPath As String, _
    TableName As String, FieldName As String, _
    oListControl As Object,Optional Distinct As Boolean = False, _
    Optional OrderBy As String) 
    'PURPOSE: Populate a list box, combo box
    'or control with similar interface with data
    'from one field in a Access Database table 
    'Parameters: DBPath: FullPath to Database
    'TableName: The Name of the Table
    'FieldName: Name of the Field
    'Distinct: Optional -- True if you want distinct value
    'Order By:  Optional -- Field to Order Results by 
    'Must have reference to DAO in your project 
    Dim sSQL As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim td As DAO.TableDef
    Dim f As DAO.Field 
    Dim sTest As String
    Dim bIsNumeric As Boolean
    Dim i As Integer 
    On Error Resume Next 
    'validate all parameters
    oListControl.AddItem "a"
    If Err.Number > 0 Then Exit Sub 
    sTest = Dir(DBPath)
    If sTest = "" Then Exit Sub 
    Set db = Workspaces(0).OpenDatabase(DBPath)
    If Err.Number > 0 Then Exit Sub 
    Set td = db.TableDefs(TableName)
    If Err.Number > 0 Then
        Exit Sub
    End If 
    Set f = td.Fields(FieldName)
        If Err.Number > 0 Then
            Exit Sub
        End If 
    If Len(OrderBy) Then
        Set f = td.Fields(OrderBy)
        If Err.Number > 0 Then
            Exit Sub
        End If
    End If
    sSQL = "SELECT "
    If Distinct Then sSQL = sSQL & "DISTINCT "
    sSQL = sSQL & "[" & FieldName & "] FROM [" & TableName & "]" 
    If OrderBy <> "" Then sSQL = sSQL & " ORDER BY " & OrderBy 
    Set rs = db.OpenRecordSet(sSQL, dbOpenForwardOnly) 
    With rs
        Do While Not .EOF
            oListControl.AddItem rs(FieldName)
    End With 
    End Sub

    Open in new window

    LVL 46

    Expert Comment


    Also look at the following


    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Suggested Solutions

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now