user defined function as source for combobox 2

[sub5]

Hi all

I am using this code (see below under valuelist to create a string to create a ValueList to fill the rowsource of a combobox. It works fine, except if the number of characters exceeds 2048.

To avoid the 2048 problem I thought I'd try filling the combobox using a userdefined function. I'm floundering with the code below under User defined function:. I am getting the error 9;subscript out of range and debugger goes to line:
inst(entries) = inst1.mstrContractDetlkey

Also how do I make it so that all four fields are included in the cbo list?
ie
mstrContractDetlkey
mstrRateSubFleetID
mstrEngAndRateDesc
mstrAircraftDesc

Thankyou in advance for any comments

ValueList:
Private Function fncAssigne dRateAcraftList()
Dim inst As Variant, strValueList As String
For Each inst In clnCDetlRateList
strValueList = strValueList & inst.mstrContractDetlkey & ";" & inst.mstrRateSubFleetID & ";" & inst.mstrEngAndRateDesc & ";" & inst.mstrAircraftDesc & ";"
Next
Me!lstRateSubFleetID.RowSource = strValueList
End Function

User Defined function:
Private Function fncARAList(fld As control, id As Variant, row As Variant, col As Variant, code As Variant) As Variant

Static inst() As String, entries As Integer
Dim returnVal As Variant
returnVal = Null
Dim inst1 As Variant

Select Case code
Case acLBInitialize
entries = 0
For Each inst1 In clnCDetlRateList
entries = entries + 1
inst(entries) = inst1.mstrContractDetlkey
Next
returnVal = entries
Case acLBOpen
returnVal = timer
Case acLBGetRowCount
returnVal = entries
Case acLBGetColumnCount
returnVal = 4
Case acLBGetColumnWidth
returnVal = -1
Case acLBGetValue
returnVal = inst(row)
Case acLBEnd
Erase inst
End Select
fncARAList = returnVal
End Function

 

[sub5]

Semi-eureka! The code below works...
...but is there a better way which doesn't involve creating the array ie works directly with the collection?

Private Function fncARAList(fld As control, id As Variant, row As Variant, col As Variant, code As Variant) As Variant

Static arrList() As String, entries As Integer
Dim ReturnVal As Variant, rows As Integer
ReturnVal = Null
rows = clnCDetlRateList.Count
Debug.Print rows

Select Case code
Case acLBInitialize
entries = 0
ReDim Preserve arrList(3, rows)
Dim instL As Variant
For Each instL In clnCDetlRateList
arrList(0, entries) = instL.mstrContractDetlkey
arrList(1, entries) = instL.mstrRateSubFleetID
arrList(2, entries) = instL.mstrEngAndRateDesc
arrList(3, entries) = instL.mstrAircraftDesc
entries = entries + 1
Next
ReturnVal = entries
Case acLBOpen
ReturnVal = timer
Case acLBGetRowCount
ReturnVal = entries
Case acLBGetColumnCount
ReturnVal = 4
Case acLBGetColumnWidth
ReturnVal = -1
Case acLBGetValue
ReturnVal = arrList(col, row)
Case acLBEnd
Erase arrList
End Select
fncARAList = ReturnVal
End Function

 

[MajP]

What version of Acess are you using? Fortunately, they have drastically increased the limit beyond 2048. I forgot what it is. I think it was changed in A2k maybe in 2003.

I have been using something like this to convert a query row source to a value list so that I can use the item add and item remove features. I have used it with some big strings. Not that it helps you if your bound by the limit, but something to look for.

Private Sub convertToValueList(theListBox As Access.ListBox)
  Dim rs As DAO.Recordset
  Dim strSql As String
  Dim fldField As DAO.Field
  Dim strLstValue As String
  Dim intColCount As Integer
  Dim intColCounter As Integer
  Dim intRowCounter As Integer
  If theListBox.RowSourceType = "Table/Query" Then
    intColCount = theListBox.ColumnCount
    strSql = theListBox.RowSource
    theListBox.RowSource = ""
    Set rs = CurrentDb.OpenRecordset(strSql)
    theListBox.RowSourceType = "Value List"
    Do While Not rs.EOF
       For intColCounter = 0 To intColCount - 1
          strLstValue = strLstValue & """" & CStr(Nz(rs.Fields(intColCounter), " ")) & """;"
       Next intColCounter
       intRowCounter = intRowCounter + 1
       rs.MoveNext
       strLstValue = Left(strLstValue, Len(strLstValue) - 1)
       theListBox.AddItem (strLstValue)
       strLstValue = ""
    Loop
 End If
End Sub

 

[MajP]

Running a test it seems like it is about 20k characters.

 

[PHV]

And what about something like this ?
Private Function fncARAList(fld As control, id As Variant, row As Variant, col As Variant, code As Variant) As Variant
Dim ReturnVal As Variant
ReturnVal = Null
Select Case code
Case acLBInitialize
ReturnVal = clnCDetlRateList.Count
Case acLBOpen
ReturnVal = timer
Case acLBGetRowCount
ReturnVal = clnCDetlRateList.Count
Case acLBGetColumnCount
ReturnVal = clnCDetlRateList.ColumnCount
Case acLBGetColumnWidth
ReturnVal = -1
Case acLBGetValue
ReturnVal = clnCDetlRateList.Column(col, row)
End Select
fncARAList = ReturnVal
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sub5]

Hi guys, unfortuneatly i am using 97 so i'm bound by the 2048. I like the look of your code though PH, I'll give that a shot tomorrow. Thanks again for your input.

 

[sub5]

Hi guys,

I've tried PH's code and got runtime error 438; Object doesn't support this property or method and debugger goes to this line:

ReturnVal = clnCDetlRateList.Column(col, row)

I tried

ReturnVal = clnCDetlRateList(col, row)

and got compile error, wrong number of arguments or invalid property assignment.

Any ideas?

 

[PHV]

What is clnCDetlRateList ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sub5]

What is in clnCDetlRateList:
a collection of any number of rows, but on average below ten. Three columns of seperate data. All strings as defined in a class module.

 

[PHV]

Can't you implement a Column property in your class module ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sub5]

I don't know how to do that?
Do I just declare:
public column as string?
What then...how do I set it when I'm adding instances to the collection...sorry for my ignorance...this is all pretty new to me and I'm having trouble visualising it

 

[PHV]

OK, the class module is not your ?
Sorry, not knowing how your collection of rows is built I can't help more.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sub5]

Hi PH,
This is the general principle (don't have the specific code to hand)
I have a class module clsProcess.
In it I have these statements:
Public mstrEngID as string
Public mstrContractID as string

On a form I have a tab control.
On the first tab there are two unbound controls. One selects the engine. The other the contract.
There is an update cmdbutton. When the user clicks it code in the form module updates the relevant tables with the data in the controls.

It then populates the collection like this:

dim inst as variant
for each inst in clsprocess
inst.mstrengid = ctl1.column(1)
inst.mstrcontractid = ctl2.column(1)
clnIDs.add inst
next

Does that make anything clearer?

 

[sub5]

Can a user defined function like this be put in an independant module and be referenced from a controls rowsourcetype property, or does the code have to be in the forms module?

 

[MajP]

I am confused by your for each.
If your class is clsProcess, what is the name of the collection? Or is the collection called clsProcess.

dim inst as variant
for each inst in clsprocess
inst.mstrengid = ctl1.column(1)
inst.mstrcontractid = ctl2.column(1)
clnIDs.add inst
next

This looks like the collection is called clsProcess but you look like you are adding variants not objects to the collection. Does this code work?

 

[sub5]

Hi MajP,
apologies for my late response, I've been away. Apologies also because I have posted the code incorrectly.

it should be:
dim inst as new clsprocess
inst.mstrengid = ctl1.column(1)
inst.mstrcontractid = ctl2.column(1)
clnIDs.add inst
set inst = nothing