Store SQL result into a Var or Array?

[Darby L]

I am trying to capture the results of a query & store thosee results into a
variable or array.
Example: If the results of a query return 5, 7, & 8, I would like to store
these results into an array, for use in my code.
Any help?
Thanks

 

[Guest]

I am also interested to get advice on a pure code method to manage this.

One non-code method that I use is to put these values into a textbox of a
unique single function Form that is always opened and not-visible....so the
data is available to all other visible forms/reports....

But I need to get better at calling values via code....so hope others will
give an example of this.....

 

[Guest]

A variable will not do if you are expecting to capture multiple values.
An array would work, but would actually take more code to manage it. You
can't just capture the results of a Select query. You can use them a row
source for combo boxes or list boxes, record sources for forms or reports, or
to export data.
Depending on how the logic in your code is constructed, you may want to
either use the query as a recordset and read the values using recordset
processing or you may want to use a DLookup to find the values.

 

[John Nurick]

Something like this should do the job:

Dim rsR As DAO.Recordset
Dim Results As Variant

Set rsR = CurrentDB.OpenRecordset "SELECT * FROM MyTable"

'Get accurate record count
rsR.MoveLast
rsR.MoveFirst

'Maybe check here whether there is an unreasonably large number of rows

'Populate the variant with a 2-dimensional array containing the results
Results = rsR.GetRows(rsR.RecordCount)

rsR.Close

 

[Darby L]

Thank you very much John,
That's exactly what I was looking for. I wasn't aware of the Getrows method
before.
I'm always learning...

BTW: Can you explain to me the reaseon for the .Close statemnet.
I notice that some use it & some don't.
Thanks again
Darby

 

[John Nurick]

Some version or versions of Access, in some circumstances, didn't always
close recordsets when they went out of scope. I can't remember which, so
usually play safe and explicitly close them.