Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2003
    Posts
    123

    Cool Unanswered: How to pass a collection as an argument

    How would I pass a collection (or array) from one sub routine to another sub or function? Let's say in the code on a form, I create the following collection:

    CollectionABC:
    item1 oldvalue1 newvalue1
    item2 oldvalue2 newvalue2
    item3 oldvalue3 newvalue3

    I then want to pass it to FunctionXYZ in the argument. FunctionXYZ then cycles through each item in the collection and acts on it, such as writing it to a table.

    Function XYZ(CollectionABC)
    for each item in CollectionABC
    write item to table
    next
    End Function

    And is there a limit on the size of the collection being passed?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Define it as a variant in the function declaration, that's the only way I know of that you can pass arrays back and forth. The other approach is to wrap your array up in an object and pass the object instead. Sometimes I'll use a little "ArrayList" object that functions very similar to the ArrayList in .NET, it has a generic array and a variety of methods for accessing and sorting elements within the array. It can be handy.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Dunno about a collection but I pass one dimension string arrays from one sub to another quite merrily in several of my apps.
    Code:
    Sub PassArray()
     
        Dim TheArray(2) As String
        
        TheArray(0) = "Pass"
        TheArray(1) = "The"
        TheArray(2) = "Array"
        
        Call RecieveArray(TheArray)
        
    End Sub
     
    Sub RecieveArray(TheArray() As String)
     
        Debug.Print Join(TheArray, " ")
        
    End Sub
    Is there something about multi dimension arrays? Is that what you are alluding to Teddy?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by pootle flump
    Is there something about multi dimension arrays?
    Evidently not:
    Code:
    Sub PassMultiArray()
     
        Dim TheArray() As String
        
        ReDim TheArray(1, 1)
        
        TheArray(0, 0) = "Pass"
        TheArray(0, 1) = "The"
        TheArray(1, 0) = "Array"
        TheArray(1, 1) = "Again"
     
        Call RecieveMultiArray(TheArray)
        
    End Sub
     
    Sub RecieveMultiArray(TheArray() As String)
     
        Debug.Print TheArray(0, 0) + " " + TheArray(0, 1) + " " + TheArray(1, 0) + " " + TheArray(1, 1)
        
    End Sub
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by pootle flump
    Dunno about a collection
    I do now

    Some pilfering and alterations of the Access 2003 help example on collection objects:
    Quote Originally Posted by Access Help
    Collection Object Example

    This example creates a Collection object (MyClasses), and then creates a dialog box in which users can add objects to the collection. To see how this works, choose the Class Module command from the Insert menu and declare a public variable called InstanceName at module level of Class1 (type Public InstanceName) to hold the names of each instance. Leave the default name as Class1. Copy and paste the following code into the General section of another module, and then start it with the statement ClassNamer in another procedure. (This example only works with host applications that support classes.)
    Code:
    Sub ClassNamer()
     
        Dim MyClasses As New Collection    ' Create a Collection object.
        Dim Num    ' Counter for individualizing keys.
        Dim Msg As String    ' Variable to hold prompt string.
        Dim TheName, MyObject, NameList    ' Variants to hold information.
     
        Do
            Dim Inst As New Class1    ' Create a new instance of Class1.
            Num = Num + 1    ' Increment Num, then get a name.
            Msg = "Please enter a name for this object." & Chr(13) _
             & "Press Cancel to see names in collection."
            TheName = InputBox(Msg, "Name the Collection Items")
            Inst.InstanceName = TheName    ' Put name in object instance.
            ' If user entered name, add it to the collection.
            If Inst.InstanceName <> "" Then
                ' Add the named object to the collection.
                MyClasses.Add Item:=Inst, Key:=CStr(Num)
            End If
            ' Clear the current reference in preparation for next one.
            Set Inst = Nothing
     
        Loop Until TheName = ""
     
        Call RecieveCollection(MyClasses)
     
    End Sub
     
    Sub RecieveCollection(MyClasses As Collection)
     
        Dim MyObject As Variant, NameList As Variant
     
        For Each MyObject In MyClasses    ' Create list of names.
            NameList = NameList & MyObject.InstanceName & Chr(13)
        Next MyObject
     
        ' Display the list of names in a message box.
        MsgBox NameList, , "Instance Names In MyClasses Collection"
     
    End Sub
    I do know you can't pass a Type as an argument - is this where the original question comes from?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by jrn0074
    And is there a limit on the size of the collection being passed?
    There will defo be no limit (besides any normal limit you might expect). All you actually pass is a pointer to the collection, not the collection itself.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Aug 2003
    Posts
    123
    Thanks, these are all very helpful. Just for kicks, would these solutions work for the following?

    Let's say I have a form with 4 text or combo controls on it, loaded with a single record. And the user edits the data in two of the controls. The process to commit the changes to the database is in a separate routine, and I want to be able to send the routine a list of the fields being changed and their old/new values.

    Control Old Value New Value
    Text1 ABC ABCCD
    Text2 1500 (no change, don't send)
    Text3 35.78 35.85
    Text4 -1 (no change, don't send)

    I want to pass an array/collection with the control name and values each in a column. So there will be one or more items in the array with three columns. The receiving routine than takes each item and acts on it.

    For Each item In ListOfChanges()
    DBField=ListOfChanges(0)
    OldValue=ListOfChanges(1)
    NewValue=ListOfChanges(2)

    use a SQL statement to update the DBField with NewValue
    write a log or debug.print DBField/Old/New

    Next

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Check out post 4. It is identical to what you describe.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Aug 2003
    Posts
    123
    One more question, how do I check if something was not included in the array but should have been, i.e. how do I cycle through the items in the array to verify what is there/missing?

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    here is a box of things.
    please list the things that are missing.

    no definitive solution!

    partial guesses are possible: if the box contains things 1,2,3,4,6,7,8,9 many people would guess that 5 is missing - no way to tell if that make physical sense. and what about -2, -1, 0 and 10 (or is it 0) on the other end?

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Aug 2003
    Posts
    123
    So let's say there is one "required" field which means I know ahead of time that out of 1,2,3,4,5,6 that 1 has to be there. So if the array comes across with 2,3,4,5,6 how do I look into the array to see if 1 is missing? Is there a NotIn or NotFoundIn statement or something?

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Nope - you are cycling through unless the value is supposed to be in a specific row in which case you can look there. If you have a few requirements like this then you might want to think about the sort of wrapper class Teddy mentioned earlier.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    if you have one thing that needs to be there, the primary key for example, pass it separately:

    public function ReceiveCollection(myPK, myCollection) as boolean
    if isnull(myPK) then
    ReceiveCollection = false
    exit function
    endif

    izy
    currently using SS 2008R2

Posting Permissions

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