Need help Desperatly! Report to Array

linkkoh · May 20th, 2005, 08:49 AM

Hello,

I hope somone can help me solve this problem!

I need to fill an array with Data from a Report!

For i = 1 To Report_UpdateReport.Count

myarray(i) = ?

Next i

How do i fill the array with the information? People suggest me to use the .EOF command but access dosn't seem to find that command.
I would highly appriciate it if you could help me fill this array. The field in my report i want is called "parentid" and the reports anme is UpdateReport

Thank you very much!

~Daniel

rjweers · May 21st, 2005, 10:30 AM

Your post is rather confusing. Where do you want to fill the array? Are you trying to run code within the report to fill the array? Why would you need the report to fill the array? Can't you just open the recordset (table or query) that is used to generate the report and get the parentid from that recordset?

e.g. code something like this:

Code:

Dim db as database
dim rs as recordset
dim i as integer
set db = CurrentDB()
set rs = db.openrecordset("the_table_or_query_that_the_report_uses")
i=0 ' don't you want to start your array at 0 not 1?
While not rs.eof
    myarray(i)=rs!parentid
    i = i + 1
    rs.movenext
wend
rs.close

Why are you filling the array anyway? What will you use it for after it is filled?

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

mmcdonal · May 23rd, 2005, 06:32 AM

I agree with rjweers. I think a recordset would be more functional, and it is already built when you are retrieving data. A recordset is a multidimensional array, and much more sophisticated than the array you are building.

Perhaps you need help packaging the data in the recordset. What do you want to do with the array data that you can't do with the recordset?

GL

mmcdonal

linkkoh · May 24th, 2005, 03:26 PM

Alright i'm switching over to using the Query instead of the Report.
I get an error though:

im myarray(100) As String

Dim db As Database
Dim rs As Recordset
Dim i As Integer
Set db = CurrentDb()
Set rs = db.OpenRecordset("CostQuery") <------
i = 0 ' don't you want to start your array at 0 not 1?
While Not rs.EOF
    myarray(i) = rs!ParentID
    MsgBox myarray(i)
    i = i + 1
    rs.MoveNext
Wend
rs.Close

<---- I get an "Too-few parameters. Expected 1." error 3061

linkkoh · May 24th, 2005, 04:00 PM

I found on a microsoft website that i need to change some things so i updated it like this:

Dim myvalue As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("CostQuery")
qdf.Parameters(0) = [Forms]![Cost Update].[ComponentID].[Value]

Set rst = qdf.OpenRecordset

    rst.MoveFirst
    Do While rst.EOF = False
        myvalue = rs!ParentID <-------
        MsgBox myvalue
        rst.MoveNext
    Loop
rst.Close
qdf.Close
Set rst = Nothing
Set qdf = Nothing
dbs.Close
Set dbs = Nothing

but i'm still getting errors at the <----- 424 Object Required

rjweers · May 25th, 2005, 07:21 AM

Assuming you copied your code exactly (and using the query with parameters works properly), the line should read

myvalue = rst!ParentID

Not "rs" but "rst".

I never use parameters on queries, so I don't know about the rest of the code. My technique would be to put the [Forms]![Cost Update].[ComponentID].[Value] as criteria in the query. Or to copy the SQL from the query into my VBA and change the string to supply the criteria.