Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2011
    Posts
    31

    Unanswered: Multiple cascading comboboxes and programming them

    I currently have a form consisting of 4 comboxes which i would like to control inputs to a query and a 'Generate' button which will take those inputs and generate a report corresponding to the selected parameters.

    I would like to program the combo boxes such that the first of the 4 lists is enabled and the rest disabled until a selection is made and after which the next box becomes enabled and so forth for each of the remainding boxes which should display data only relevant depending on the previous selection.

    I tried the method of 'Multiple Row source tables' and 'A Single row source table' from http://www.fontstuff.com/access/acctut10.htm, however it doesnt seem as if the rowsource for the subsequent list is being updated for either method because i get a blank droplist.

    Any alternatives or suggestions for solving this problem or working around it?

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I have done this and this is the code I came up with:

    Code:
    Private Sub cboBatch_AfterUpdate()
    
        UpdateComboBoxes
        UpdateControls
        
    End Sub
    
    Private Sub cboClient_AfterUpdate()
    
        cboMailing = ""
        cboBatch = ""
        
        UpdateComboBoxes
        UpdateControls
        
    End Sub
    
    Public Sub UpdateComboBoxes()
    
        Dim strSQL As String
        
        If cboClient & "" = "" Then
        
            cboClient.SetFocus
            
            cboMailing = ""
            cboMailing.RowSource = ""
            cboMailing.Enabled = False
            
            cboBatch = ""
            cboBatch.RowSource = ""
            cboBatch.Enabled = False
            
        Else
        
            strSQL = "SELECT MailingID, MailingName FROM tblMailing WHERE ClientID=" & cboClient
            strSQL = strSQL & " ORDER BY MailingName"
            
            cboMailing.RowSource = strSQL
            
            If cboMailing & "" = "" Then
            
                cboMailing.Enabled = True
                cboMailing.SetFocus
                
                cboBatch = ""
                cboBatch.Enabled = False
                
            Else
                
                strSQL = "SELECT BatchID, Batch FROM tblBatch WHERE ClientID=" & cboClient
                strSQL = strSQL & " AND MailingID=" & cboMailing & " ORDER BY BatchID DESC"
                
                cboBatch.RowSource = strSQL
                
                cboBatch.Enabled = True
                cboBatch.SetFocus
                
            End If
            
        End If
        
    End Sub
    
    Public Sub UpdateControls()
    
        Dim strSQL As String
        
        If cboBatch & "" = "" Then
        
            Me.cmdSelectAllPrograms.Enabled = False
            Me.cmdSelectAllReports.Enabled = False
            Me.chkDumps.Enabled = False
            Me.lstPrograms.Enabled = False
            Me.lstReports.Enabled = False
            
            Me.lstPrograms.RowSource = ""
            
        Else
        
                  
                strSQL = "SELECT tblProgram.ProgramID,CAST(tblProgram.ProgramID AS nvarchar(10)) + ' - ' + "
                strSQL = strSQL & "tblProgram.ProgramName As Program "
                strSQL = strSQL & "FROM tblPrintQueueCount INNER JOIN tblProgram ON tblPrintQueueCount.ProgramID "
                strSQL = strSQL & "= tblProgram.ProgramID WHERE (((tblPrintQueueCount.BatchID)=" & cboBatch & "))"
                strSQL = strSQL & "GROUP BY tblProgram.ProgramID, CAST(tblProgram.ProgramID AS nvarchar(10)) + ' - ' "
                strSQL = strSQL & "+ tblProgram.ProgramName ORDER BY tblProgram.ProgramID"
            
            
            Me.lstPrograms.RowSource = strSQL
            
            Me.cmdSelectAllPrograms.Enabled = True
            Me.cmdSelectAllReports.Enabled = True
            Me.chkDumps.Enabled = True
            Me.lstPrograms.Enabled = True
            Me.lstReports.Enabled = True
            
            Me.cmdProcess.Enabled = True
            Me.cmdArchive.Enabled = True
            
        End If
        
    End Sub
    
    Private Sub cboMailing_AfterUpdate()
    
        cboBatch = ""
            
        UpdateComboBoxes
        UpdateControls
        
    End Sub
    I have three combo boxes Client, Mailing and Batch. Client determines which Mailings to show and Mailings determines which Batches to show. When all of the information has been selected then I enable som other controls on the form. UpdateComboBoxes manages the combo boxes and their data. UpdateControls turns the other controls on or off.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •