Thread: Combobox via Select Case

Hello everyone. I have a userform in an excel spreadsheet that has two combo boxes on it (for now, more stuff willl be added). In the first combo box, the user may select from two options: Customer or Terminal. (I have that part up-and-running). For the second combobox what needs to get done is trickier.

Depending on what the user selects I need the following to occur:

1. If the user selects "Customer", then the second combo box will contain a list of Customers. That list is in an Access databse table called Customers.

2. If the user selects "Terminal", then the second combobox will contain a list of terminals. That list is in an Access databse table called Terminals.

I am using this Private sub to create the database connection:

Public Sub CreateConnection()
    Dim DataSource As String
    Dim DataFolder As String
    Dim DS As String

    On Error GoTo ConnectionError
    Set conn = New ADODB.Connection

    DataSource = Sheets(VALIDATIONS).Range("W2").Value
    If DataSource = "" Or Dir(DataSource) = "" Then
        DataSource = ThisWorkbook.Path & "\PIPELINETRACKER.MDB"
        Sheets(VALIDATIONS).Range("W2").Value = DataSource
    End If
    DataFolder = VBA.Left(DataSource, InStrRev(DataSource, "\"))

    conn.Provider = "Microsoft.Jet.OLEDB.4.0;"
    conn.ConnectionString = "Data Source=" & DataSource & ";Jet OLEDB:Database Password=ProtectMDB;"
    conn.Mode = adModeShareDenyNone
    
    conn.Open
    
    Exit Sub
    
ConnectionError:
    
    If DataFolder <> "" Then
        Err.Clear
        DataSource = DataFolder & "PIPELINETRACKER.MDB"
        On Error Resume Next
        conn.Provider = "Microsoft.Jet.OLEDB.4.0;"
        conn.ConnectionString = "Data Source=" & DataSource & ";Jet OLEDB:Database Password=ProtectMDB;"
        conn.Mode = adModeShareDenyNone
        conn.Open
        If Err.Number = 0 Then Exit Sub
    End If
    If Err.Number <> 0 Or DataFolder = "" Then
        Err.Clear
        DS = Application.GetOpenFilename("Access Database (*.mdb), *.mdb")
        If DS <> "False" Then
            DataSource = DS
            DataFolder = VBA.Left(DataSource, InStrRev(DataSource, "\"))
            Sheets(VALIDATIONS).Range("W2").Value = DataSource
            Set conn = New ADODB.Connection
            conn.Provider = "Microsoft.Jet.OLEDB.4.0;"
            conn.ConnectionString = "Data Source=" & DataSource & ";Jet OLEDB:Database Password=ProtectMDB;"
            conn.Mode = adModeShareDenyNone
            conn.Open
        Else
            Application.Quit
        End If
    End If
    
End Sub

The userform is launched via:

Option Explicit
Private Sub Button4_Click()

Application.ScreenUpdating = False

TrackerManagementForm.Show

End Sub

Any help is greatly appreciated. I am developing using Access and Excel 97.