ACCESS FEED: [MS_AccessPros] Error in Function That Puts Data From Table Into Array

Rabu, 17 September 2014

[MS_AccessPros] Error in Function That Puts Data From Table Into Array

Hi,
I have a function (below) that fetches values from a table and puts them in an array.
The problem is that I cannot set the size of the array to match the number of records in the table.
I tried the following (Lines 8 to 11 of the code below)
intArraySize = rstData.RecordCount
ReDim arrPlaceH(intArraySize)
intArraySize returns '1' even while RecordCount correctly shows '5'

I also tried
ReDim arrPlaceH(rstData.RecordCount)

In either case, I get a 'Subscript out of range' error at line 18

The function only works when I hardcode the size of the array (Line 12)
ReDim arrPlaceH(50) 'resize the array *** This one works

What is causing this?
I am using Access 2003. (Yes :-) )

Thanks.

Abraham

===Begin Code===
Function FFetchPlaceHolders() As Variant
    Dim rstData As DAO.Recordset
    Dim arrPlaceH As Variant
    Dim intArraySize As Long, intPos As Integer, strFieldValue As String
    Set rstData = CurrentDb.OpenRecordset("SELECT PlaceHolderText FROM tblTenderDocPlaceholders")

    'The size of the array should be equal to the number of rows in the table
    'intArraySize = rstData.RecordCount '- 1
    intArraySize = Val(rstData.RecordCount) ' *** Does not work. intArraySize is always 1, even while RecordCount is 5
    'ReDim arrPlaceH(intArraySize) 'resize the array *** Does not work
    'ReDim arrPlaceH(rstData.RecordCount) 'resize the array *** Does not work
    ReDim arrPlaceH(50) 'resize the array *** This one works

    rstData.MoveFirst
    intPos = 0
    Do Until rstData.EOF
        strFieldValue = rstData.Fields("PlaceHolderText")
        arrPlaceH(intPos) = strFieldValue 'Subscript out of range error
        rstData.MoveNext
        intPos = intPos + 1
    Loop

    rstData.Close
    Set rstData = Nothing
    Close

    FFetchPlaceHolders = arrPlaceH

End Function

===End Code ===