Populating Comboboxes Problem (RESOLVED)

In Access, I am populating a Combobox as a Value List for the Row Source Type and then using the following code to Populate it. How ever I understand there is a 2048 character limit for this property and my String could be many 1000's of times greater. The Combobox must by Dynamic so that it only shows what I want it to show so I created the Recordsets for it with SQL statements in the .OpenRecordset function. Is there a way to still do it this way or must I create an actual Query Object in my Database and make my Combobox Row Source Type "Table/Query"?

Code:

---

Sub PopulateCrudeOilScheduleForm(Mode As String)
    Dim MyDB As Database, rs As Recordset, rs2 As Recordset
    Set MyDB = DBEngine.Workspaces(0).Databases(0)
    Set rs = MyDB.OpenRecordset("PortfolioData", dbOpenDynaset)
    Dim RefNumArray(100000)
    rs.Sort = "ref #, MonthEnd"
    rs.Close
'    MultipleDelivery Open the recordset according to wether the filters are active or not.
    If Not SCHEDULERFILTER = "" Then
'        If Filters are activated
        MySQLString = "SELECT PortfolioData.[ref #], PortfolioData.MonthEnd, PortfolioData.broker, PortfolioDataForScheduler.DeliveredVolume, PortfolioDataForScheduler.QualityAdjustment, PortfolioDataForScheduler.TransportationAmount, PortfolioDataForScheduler.LeaseOrStream, PortfolioDataForScheduler.Transfer, PortfolioDataForScheduler.TransferPipeline_Terminal, PortfolioDataForScheduler.DeliveryMethod, PortfolioDataForScheduler.DeliveryPipeline_Terminal, PortfolioDataForScheduler.Battery, PortfolioDataForScheduler.Pipeline, PortfolioData.class, IIf([Type]=""P"",""Purchase"",""Sale"") AS PurchaseOrSale, PortfolioData.DupTestString, PortfolioDataForScheduler.BasePriceOverRide, PortfolioData.SettleDate, PortfolioData.wghtvol, PortfolioData.NegFx, PortfolioData.Commodity, PortfolioDataForScheduler.Density, PortfolioDataForScheduler.Sulphur, PortfolioDataForScheduler.BasePriceOverRide, PortfolioDataForScheduler.SchedulerVolumeOverRide, PortfolioDataForScheduler.PrimaryOrIntermediate," & _
        " PortfolioData.NegotiatedVolumeString, PortfolioDataForScheduler.MultipleDelivery, PortfolioData.NegotiatedUnits FROM PortfolioData LEFT JOIN PortfolioDataForScheduler ON PortfolioData.DupTestString = PortfolioDataForScheduler.DupTestString WHERE (((PortfolioData.class)=""Position"") AND ((PortfolioData.Commodity)=""CL"") AND ((PortfolioData.[fin/phys])<>""F"")) and ((" & SCHEDULERFILTER & ") ORDER BY PortfolioData.[ref #], PortfolioData.MonthEnd;"
        Set rs2 = MyDB.OpenRecordset(MySQLString)
    Else
        MySQLString = "SELECT PortfolioData.[ref #], PortfolioData.MonthEnd, PortfolioData.broker, PortfolioDataForScheduler.DeliveredVolume, PortfolioDataForScheduler.QualityAdjustment, PortfolioDataForScheduler.TransportationAmount, PortfolioDataForScheduler.LeaseOrStream, PortfolioDataForScheduler.Transfer, PortfolioDataForScheduler.TransferPipeline_Terminal, PortfolioDataForScheduler.DeliveryMethod, PortfolioDataForScheduler.DeliveryPipeline_Terminal, PortfolioDataForScheduler.Battery, PortfolioDataForScheduler.Pipeline, PortfolioData.class, IIf([Type]=""P"",""Purchase"",""Sale"") AS PurchaseOrSale, PortfolioData.DupTestString, PortfolioDataForScheduler.BasePriceOverRide, PortfolioData.SettleDate, PortfolioData.wghtvol, PortfolioData.NegFx, PortfolioData.Commodity, PortfolioDataForScheduler.Density, PortfolioDataForScheduler.Sulphur, PortfolioDataForScheduler.BasePriceOverRide, PortfolioDataForScheduler.SchedulerVolumeOverRide, PortfolioDataForScheduler.PrimaryOrIntermediate," & _
        " PortfolioData.NegotiatedVolumeString, PortfolioDataForScheduler.MultipleDelivery, PortfolioData.NegotiatedUnits FROM PortfolioData LEFT JOIN PortfolioDataForScheduler ON PortfolioData.DupTestString = PortfolioDataForScheduler.DupTestString WHERE (((PortfolioData.class)=""Position"") AND ((PortfolioData.Commodity)=""CL"") AND ((PortfolioData.[fin/phys])<>""F""))ORDER BY PortfolioData.[ref #], PortfolioData.MonthEnd;"
        Set rs2 = MyDB.OpenRecordset(MySQLString)
    End If
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '  Populate the Goto Dropdown with the currently
    '  available reference numbers.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    If Mode = "Open" Or Mode = "OpenToLastRecord" Then
        Form_CrudeOilScheduler.cmboRefNumFind.RowSource = ""
        ArrayCounter = 0
        rs2.MoveFirst
        Do While Not rs2.EOF
            TempRefNumber = rs2![ref #] + ";"
            ValidFlag = True
            For ValidateCounter = 0 To ArrayCounter
                If TempRefNumber = RefNumArray(ValidateCounter) Then
                    ValidFlag = False
                    Exit For
                End If
            Next
            If ValidFlag = True Then
                RefNumArray(ArrayCounter) = TempRefNumber
                ArrayCounter = ArrayCounter + 1
            End If
            rs2.MoveNext
        Loop
        For i = 0 To ArrayCounter - 1
            Form_CrudeOilScheduler.cmboRefNumFind.AddItem RefNumArray(i)
        Next
    End If

---

I can still set the RowSource Property to an SQL statement which gives me all the flexibility I need here is the revised code


Just the last If statement from above

Code:

---

    If Mode = "Open" Or Mode = "OpenToLastRecord" Then
'        Form_CrudeOilScheduler.cmboRefNumFind.RowSource = ""
'        ArrayCounter = 0
'        rs2.MoveFirst
'        Do While Not rs2.EOF
'            TempRefNumber = rs2![ref #] + ";"
'            ValidFlag = True
'            For ValidateCounter = 0 To ArrayCounter
'                If TempRefNumber = RefNumArray(ValidateCounter) Then
'                    ValidFlag = False
'                    Exit For
'                End If
'            Next
'            If ValidFlag = True Then
'                RefNumArray(ArrayCounter) = TempRefNumber
'                ArrayCounter = ArrayCounter + 1
'            End If
'            rs2.MoveNext
'        Loop
'        For i = 0 To ArrayCounter - 1
'            Form_CrudeOilScheduler.cmboRefNumFind.AddItem RefNumArray(i)
'        Next
        If Not SCHEDULERFILTER = "" Then
            RowSourceString = "SELECT DISTINCT PortfolioData.[ref #] FROM PortfolioData LEFT JOIN PortfolioDataForScheduler ON PortfolioData.DupTestString = PortfolioDataForScheduler.DupTestString WHERE (((PortfolioData.class)=""Position"") AND ((PortfolioData.Commodity)=""CL"") AND ((PortfolioData.[fin/phys])<>""F"")) and ((" & SCHEDULERFILTER & ") ORDER BY PortfolioData.[ref #];"
        Else
            RowSourceString = "SELECT DISTINCT PortfolioData.[ref #] FROM PortfolioData LEFT JOIN PortfolioDataForScheduler ON PortfolioData.DupTestString = PortfolioDataForScheduler.DupTestString WHERE (((PortfolioData.class)=""Position"") AND ((PortfolioData.Commodity)=""CL"") AND ((PortfolioData.[fin/phys])<>""F""))ORDER BY PortfolioData.[ref #];"
        End If
        Form_CrudeOilScheduler.cmboRefNumFind.RowSource = RowSourceString
    End If

---