Can a control pass an array to a function?

[Cornishgamehen]

Hi,

In Access 2002, Is it possible to pass an array to a function from inside a control?

For example, if you have a VBA function:

MyFunc(inputArray() as String) as String

can you then have an unbound control on an Access form that passes an array? E.g., set the controlsource to the following:

=MyFunc(Array(" Item1", "Item2"))

I've tried and it seems like you can't, but I wanted to make sure. I'm not sure if the problem is that you can't use the Array() function in a control, or something else. (It seems like you can't use Split() either... is there a list somewhere of VB functions that can't be passed from a control to another function? Or some general rule?)

[NeoPa]

I think this is down to the fact that the Control Source is handled by Jet SQL rather than being within a VBA context. My belief is that, while VBA can handle more complex concepts like arrays, Jet SQL can only handle the basic field types. As soon as the contents of the Control Source attempts to use an array of any sort, it fails. That's my understanding - but I don't have chapter and verse to quote I'm afraid.

[ADezii]

I found this question to be very interesting, so the old wheels started turning. What you can do is to set the Control Source of a Control to a Public Function, passing to it the Fully Qualified Paths to Controls on a Form as a Comma-Delimited String, or any Delimited String that matter. Within the Function, the Absolute References are placed in an Array, then Evaluated using the Eval() Function. Case in point:

1. Control Source of txtFullName
   

   =fMyFunction("Forms!Form1![txtFirstName],Forms!Form1![txtMI],Forms!Form1![txtLastName]")

2. Function Definition:
   

   Public Function fMyFunction(strFakeArray As String) As String
   Dim intCtr As Integer
   Dim strBuild As String
   Dim varRet As Variant
   
   varRet = Split(strFakeArray, ",")
   
   For intCtr = LBound(varRet) To UBound(varRet)
     strBuild = strBuild & Eval(varRet(intCtr)) & " "
   Next
   
   fMyFunction = Trim$(strBuild)
   End Function

3. Sample Result:
   

   Andrew B. Fuller

4. OK, I'm done! You can now file it in the Folder named Useless Information! (LOL).