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 ===


__._,_.___

Posted by: abraham@owentechkenya.com
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)

Improvements in Yahoo Groups Search
Searching for new groups to join is easier than ever. We've honed our algorithm to bring you better search results based on relevance and activity. Try it today!


.

__,_._,___

Tidak ada komentar:

Posting Komentar