I have created the following function:

Public Function GetProjectInfo(pid) As Variant()

strProjectQuery = "SELECT P_NAME, P_FUND, P_BORG FROM Projects " & _
"WHERE P_ID=" & pid
Set prs = CurrentDb().OpenRecordset(strProjectQuery)
If Not prs.BOF And Not prs.EOF Then
p = Array(prs!P_NAME, prs!P_FUND, prs!P_BORG)
Else
p = Array("No Data")
MsgBox "No matching Project record. " & _
"Please contact your Database Administrator."
End If
End Function

I invoke the function with:

Dim ProjInfo As Variant
ProjInfo = Utils.GetProjectInfo(strRecNo)


When I invoke it, I get an array that is filled (determined with the IsArray
and IsEmpty methods), but when I try to loop through the array or use UBound
or LBound, I get a "subscript is out of range" error.

What do I need to do to fix it.

In your function where do you set the return value... The last line should be
something like...

GetProjectInfo = p

HTH

"Andy Dorph" wrote:

I have created the following function:

Public Function GetProjectInfo(pid) As Variant()

strProjectQuery = "SELECT P_NAME, P_FUND, P_BORG FROM Projects " & _
"WHERE P_ID=" & pid
Set prs = CurrentDb().OpenRecordset(strProjectQuery)
If Not prs.BOF And Not prs.EOF Then
p = Array(prs!P_NAME, prs!P_FUND, prs!P_BORG)
Else
p = Array("No Data")
MsgBox "No matching Project record. " & _
"Please contact your Database Administrator."
End If
End Function

I invoke the function with:

Dim ProjInfo As Variant
ProjInfo = Utils.GetProjectInfo(strRecNo)


When I invoke it, I get an array that is filled (determined with the IsArray
and IsEmpty methods), but when I try to loop through the array or use UBound
or LBound, I get a "subscript is out of range" error.

What do I need to do to fix it.