Best way to pass array?

[chobo321321]

Anyway to pass array this?

Hi, I was wondering what is the best way to pass the contents of an array from one procedure to another. Any help is appreciated, thanks.

** I think the title of the post was misleading, so I changed it.

 

[sfreeman@co.mer]

If I understand your question, I'd just pass the array itself.

Here's an example:

Public Function ReceiveArray(strArray() As String)
Dim i As Integer

For i = LBound(strArray()) To UBound(strArray())
Debug.Print strArray(i)
Next i

End Function

'********************************************
Public Function SendArray()
Dim strArray(10) As String
Dim i As Integer

For i = LBound(strArray()) To UBound(strArray())
strArray(i) = "Subscript # " & i
Debug.Print strArray(i)
Next i

Call ReceiveArray(strArray())

End Function

HTH

 

[chobo321321]

Here's what I am trying to do. Your way might work for this, but I find it confusing (logically) because I can't figure out how to use that to make strArray available to the lstCustSearch procedure. All I want is just to have access to the array (like a global variable).

The array values are set in the cmdSearch_click procedure, and I want to
use all the array values (whole thing) in another procedure called lstCustSearch_Click(). Is there a way to pass the whole finsihed array to the other procedure? I tried making the array a global variable, but that didn't work.

Private Sub cmdSearch_Click()
Dim strArry() as string
.
. //connection code. Code also returns result of a search (can contain 
many names)
.
.
intsize = 0
Do Until rst.EOF
        ReDim strArray(intSize)
        strArray(intSize) = rst.Fields("CustID").value & ";" & rst.Fields("LName") 
        & ";" & rst.Fields("FName")
        lstCustSearch.AddItem strArray(intSize)
        rst.MoveNext
        intSize = intSize + 1
    Loop

End sub

Private Sub lstCustSearch_Click()

//Trying to use array in this procedure so when an item is clicked in the
//listbox I can figure out which record it was, and load it up on a different 
//form

some-other-form = strArray(0).value

End Sub

 

[sfreeman@co.mer]

Looks to me like you have two separate events happening here. By the time you fire the second procedure, the first is out of scope and all variables in the first have lost their values.

If you were to call the second procedure FROM the first, then you could pass the array just as shown in my example, but I suppose that is not what you are trying to accomplish.

I have to leave, so I do not have anymore time to think about this, but it occurs to me that one 'sloppy' way to do this would be to punch those array values into a table [after clearing out the table before setting new values] and then the values will persist, and you can then pick them up when the second procedure fires.

 

[chobo321321]

Thanks for the help. I'll keep trying your way, if I can't get it, I'll have to find another way (without arrays). I though passing values between two event procedures would have been a little easier, but after searching around it seems to be a nightmare.

 

[chobo321321]

I finally got it to work using a module. For some reason it doesn't allow me to declare an array in the general declaration sections, but I could declare it in a module, and it holds the value of the array. Are modules ok to use for VB or is that bad coding practice?

 

[The_Doc_Man]

Don't take this as a harsh criticism, but if you have to use an array you are thinking in a way that isn't consistent with Access (usually). You want everything you are searching to be disk-resident (i.e., make its way to a record in a table, eventually) though it is OK to use arrays for parsing or something like that.

RE your question about modules: Modules are fine for VBA in Access. I think you want to look up keyword "STATIC" (???) as a way to lock down an array in memory. (I.e. even if I disagree with the approach I will answer the question...)

The reason arrays aren't quite right for Access is that it is based on SQL (primarily) which is SEQUENTIAL in nature. One element at a time. An array is not inherently sequential. Using an array, you DOOM yourself to more VBA code than might have been necessary if using a more linear method. Let your computer do the work where possible. Simplify where possible. Let SQL do some of your searching for you. Or judiciously chosen JOIN queries.