Populating Comboboxes Problem (RESOLVED)

[Avatarp]

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"?


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

[Avatarp]

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



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