Return Array

[irishjoe]

I am writing a function to look in a table and return 2 values.
I want to know how to modify the function below to return a string array rather than a string?

Dim cat
cat = Array(2)
cat = CheckInspection("tblFailure", 5)
Debug.Print "Val1: " & cat(0)
Debug.Print "Val2: " & cat(1)


Public Function CheckInspection(database As String, val1 As Integer) As string

Dim arrMatrix(2) As String
Set dbs = CurrentDb()
Set rstMatrix = dbs.OpenRecordset(database, dbOpenDynaset, dbDenyRead)

rstMatrix.MoveFirst
Do Until rstMatrix.EOF
If val1 = rstMatrix("val1") And val2 = rstMatrix("val2") Then
arrMatrix(0) = rstMatrix("val3")
arrMatrix(1) = rstMatrix("val4")
Exit Do
End If
rstMatrix.MoveNext
Loop
rstMatrix.Close
dbs.Close
CheckInspection = arrMatrix
End Function

 

[modest]

Did you try passing the string by reference?

 

[irishjoe]

I got it. I changed the penultimate line to be
CheckInspection = arrMatrix()

 

[modest]

That could work too If you know you're going to change the value of what you're passing in, it's good to pass by reference.

 

[filo65]

you could also add a "()" to your function definition:

Public Function CheckInspection(...) As string [B]()[/B]
...
CheckInspection = arrMatrix ' no need anymore for "()" here
End Function