Populate an Array using Get Reocrds

I'm tryin gto populate an array with data from a recordset, using Get Records. Here is my code:

Delcarations
Type PSQueryDef
    ID As String
    startdate As Date
    stopdate As Date
End Type

Procedure:
Public Function PopulateMBArray()
Dim arrQueries() As PSQueryDef
Dim db As Database
Dim rs0 As Recordset
Dim qdf0 As QueryDef
Dim ID As String, startdate As Date, stopdate As Date
Set db = CurrentDb
Set qdf0 = db.QueryDefs("qryMBArray")
Set rs0 = qdf0.OpenRecordset()
rs0.MoveLast
rs0.MoveFirst
ReDim Preserve arrQueries(rs0.RecordCount - 1, 3)

    If Not rs0.EOF Then
        arrQueries = rs0.GetRows(rs0.RecordCount - 1)
    End If
rs0.Close

The code produces an error "Type Mismatch". There may be other problems too, I'm not familiar with arrays.
Also, can anyone recommend a book that addresses MS Access VBA /programming?
Thanks
RHK
rhk6446Asked:
Who is Participating?
 
Jokra_the_BarbarianConnect With a Mentor Commented:
RHK,
Everything looks good, just a couple modifications...
    Dim arrQueries()
    Dim db As Database
    Dim rs0 As Recordset
    Dim qdf0 As QueryDef
    Dim id As String, startdate As Date, stopdate As Date
   
    Set db = CurrentDb
    Set qdf0 = db.QueryDefs("qryMBArray")
    Set rs0 = qdf0.OpenRecordset()
    rs0.MoveFirst
    ReDim Preserve arrQueries(rs0.RecordCount - 1, 3)
            If Not rs0.EOF Then
            arrQueries = rs0.GetRows(rs0.RecordCount - 1)
        End If
    rs0.Close
0
 
Bat17Connect With a Mentor Commented:
Just a minor tweak. It will still need to move last to avoid a record count of 1
If working above A97 it will need a reference to DAO

 Dim arrQueries()
    Dim db As Database
    Dim rs0 As DAO.Recordset
    Dim qdf0 As QueryDef
    Dim id As String, startdate As Date, stopdate As Date
   
    Set db = CurrentDb
    Set qdf0 = db.QueryDefs("qryMBArray")
    Set rs0 = qdf0.OpenRecordset(, dbOpenDynamic)
    rs0.MoveLast
    rs0.MoveFirst
    ReDim Preserve arrQueries(rs0.RecordCount - 1, 3)
            If Not rs0.EOF Then
            arrQueries = rs0.GetRows(rs0.RecordCount - 1)
        End If
    rs0.Close

HTH

Peter
0
 
shanesuebsahakarnCommented:
Bear in mind that GetRows returns a *two dimensional* array irregardless of the dimensions of the array passed to it. That is, the dimensions of arrQueries does not matter.

If your data contains:
ID: 10        Start Date: 01/01/2004      End Date: 10/10/2004
ID: 11        Start Date: 02/02/2004      End Date: 03/03/2004

the array returned will have:
Element:    Value:
0,0             10
1,0             01/01/2004
2,0             10/10/2004
0,1             11
1,1             02/02/2004
2,1             03/03/2004
0
 
RichardCorrieCommented:
why do you need an array?

can you not manipulate using the recordset?

/Richard
0
All Courses

From novice to tech pro — start learning today.