Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Populate an Array using Get Reocrds

Posted on 2004-10-21
4
Medium Priority
?
288 Views
Last Modified: 2008-03-17
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
0
Comment
Question by:rhk6446
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 11

Accepted Solution

by:
Jokra_the_Barbarian earned 1200 total points
ID: 12377671
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
 
LVL 9

Assisted Solution

by:Bat17
Bat17 earned 400 total points
ID: 12378372
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12378446
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
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 12378525
why do you need an array?

can you not manipulate using the recordset?

/Richard
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question