Passing a parameter to represent the number of columns to be added to combobox

[anavagomez]

I am working on MS Access 2013/ VBA. function that initializes all the combo-boxes that are on a form. This function is called in the Load event of the form.


I am adding an initialize function for each of the combo boxes on the form, and this is very redundant, no mentioning that there are over 35+ combo-boxes on the form and the multiple tabs that the form also has.


My ideal is to have only one initialize function, and call it still from the load event, perhaps multiple times, but passing the necessary parameters to process each individual combo-box.


What I have right now (each call is calling a function that does basically the same):

    Private Sub Form_Load()
        
        Call InitializePriceCategory
        Call InitializePublisher    
        
        On Error GoTo errhandler


    eofit:
        
        Exit Sub


    errhandler:


        z = ErrorFunction(Err, Err.Description, Erl, "Form_Load")
        
        Err = 0
        
        Select Case z
            Case 0: Resume Next
            Case 1: GoTo eofit
        End Select
    End Sub

The only thing that changes for each function, is the select query that gets executed, the combo-box, and the number of columns that each specific combo-box needs to show.


Example of the function InitializePriceCategory:

    Public Function InitializePriceCategory()
        
        Dim ADOCon As ADODB.Connection
        Dim ADORS As ADODB.Recordset
        Dim avarRecords As Variant
        Dim avarTransposedArray As Variant
        Dim avarOriginalArray As Variant
        Dim intRecord As Integer
        Dim strSQL As String
        
        On Error GoTo errhandler
        
        strSQL = "SELECT DISTINCT" & _
                 "  [Category]" & _
                 ", [ProductDescription]" & _
                 ", [BasePrice]" & _
                 ", [AdditionalPrintPrice]" & _
                 ", [MinimumPurchaseAmount]" & _
                 ", [isChoral]" & _
                 ", [isScoringBasedMinAmount]" & _
                 ", [isTierBased] " & _
                 "FROM [dbo].[z_PriceCategories] " & _
                 "ORDER BY [Category]"
        
        Set ADOCon = New ADODB.Connection
        With ADOCon
            .ConnectionString = GetConnectionString("Conn")
            .Open
        End With
        
        Set ADORS = New ADODB.Recordset
        With ADORS
            .ActiveConnection = ADOCon
            .Open strSQL, , adOpenStatic, adLockReadOnly
            .MoveLast
            .MoveFirst
            avarRecords = .GetRows(.RecordCount)
        End With
        
        For intRecord = 0 To UBound(avarRecords, 2)
             
            ' Check for commas within the string on column 1 (description),
            ' otherwise the value gets truncated
            If InStr(avarRecords(1, intRecord), ",") > 0 Then
                avarRecords(1, intRecord) = """" & avarRecords(1, intRecord) & """"
            End If


            PriceCategory.AddItem (avarRecords(0, intRecord) & ";" & _
                                   avarRecords(1, intRecord) & ";" & _
                                   avarRecords(2, intRecord) & ";" & _
                                   avarRecords(3, intRecord) & ";" & _
                                   avarRecords(4, intRecord) & ";" & _
                                   avarRecords(5, intRecord) & ";" & _
                                   avarRecords(6, intRecord) & ";" & _
                                   avarRecords(7, intRecord))
                
        Next intRecord
    eofit:
        
        On Error Resume Next
        
        ADOCon.Close: Set ADOCon = Nothing
        ADORS.Close: Set ADORS = Nothing


        Exit Function


    errhandler:
        
        z = ErrorFunction(Err, Err.Description, Erl, "InitializePriceCategory", , True)
        
        Err = 0
        
        Select Case z
            Case 0: Resume Next
            Case 1: GoTo eofit
        End Select
        
    End Function

The biggest difference and most challenging for me to figure out how to work out, among each of the initialize functions so that I can have only one initialize function, is the number of columns.


My goal is to have something simple on the form load code:

    Private Sub Form_Load()
    
        On Error GoTo errhandler
        
        Call InitializeCombo(Me.PriceCategory, "SELECT col1, col2, col3, col4, col5, col6, col7, col8 FROM PriceCategory ", 8, ",")
        Call InitializeCombo(Me.PublisherName, "SELECT col1, col2 FROM Publishers ", 2, """")


    eofit:
        
        Exit Sub


    errhandler:


        z = ErrorFunction(Err, Err.Description, Erl, "Form_Load")
        
        Err = 0
        
        Select Case z
            Case 0: Resume Next
            Case 1: GoTo eofit
        End Select
    End Sub

And then the InitializeCombo function code to be (incomplete idea):

    Public Function InitializeCombo(pCombo As ComboBox, pQuery As String, pCols As Integer, Optional pSpecialCharacter As String)
        
        Dim ADOCon As ADODB.Connection
        Dim ADORS As ADODB.Recordset
        Dim avarRecords As Variant
        Dim avarTransposedArray As Variant
        Dim avarOriginalArray As Variant
        Dim intRecord As Integer    
        
        On Error GoTo errhandler
        
        Set ADOCon = New ADODB.Connection
        With ADOCon
            .ConnectionString = GetConnectionString("Conn")
            .Open
        End With   
            
        Set ADORS = New ADODB.Recordset
        With ADORS
            .ActiveConnection = ADOCon
            .Open pQuery, , adOpenStatic, adLockReadOnly
            .MoveLast
            .MoveFirst
            avarRecords = .GetRows(.RecordCount)
        End With
        
        ' ON THIS PART I AM NOT SURE HOW TO STILL BE ABLE TO DO THE SPECIAL CHARACTER CHECK 
        If InStr(avarRecords(1, intRecord), """") > 0 Then
            avarRecords(1, intRecord) = "'" & avarRecords(1, intRecord) & "'"
        End If
        
        For intRecord = 0 To UBound(avarRecords, 2)
            
            ' ON THIS PART, I DO NOT KNOW HOW TO INSTRUCT/ LOOP TO USE THE NUMBER OF COLUMNS PARAMETER 
            ' AND ADD THE NUMBER OF COLUMNS NEEDED; WHETHER ONE COMBO-BOX NEEDS 8 AND THE NEXT ONE ONLY NEEDS 2.
            pCombo.AddItem (avarRecords(0, intRecord) & ";" & _
                            avarRecords(1, intRecord) & ";")
                
        Next intRecord


    eofit:
        
        On Error Resume Next
        
        ADOCon.Close: Set ADOCon = Nothing
        ADORS.Close: Set ADORS = Nothing


        Exit Function


    errhandler:
        
        z = ErrorFunction(Err, Err.Description, Erl, "InitializeCombo", , True)
        Err = 0
        Select Case z
            Case 0: Resume Next
            Case 1: GoTo eofit
        End Select
        
    End Function

I am thinking that what I need is to add another loop inside the first for loop that iterates through the records array, but I am not sure how to add this part.


I hope I can get some help on this issue as I think that I am close to get it right, I just ran into a roadblock I have not been able to figure out.

[davegri]

If you know what the query is for each combobox, you don't need ANY of this code. Just set the rowsource property for the combobox to that query. It can be a named query or the SQL of the query.