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

Rabu, 17 September 2014

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

Abraham-

You have to move to the end of a newly opened recordset for the record count to be correct. Do rstData.MoveLast first. You should also Dim arrPlaceH() As Variant.

Note that you can also change the size of the array as you move through the recordset by using ReDim Preserve.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Sep 17, 2014, at 4:02 PM, abraham@owentechkenya.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

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: John Viescas <johnv@msn.com>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)