How to get set of records into array then loop though? (1 Viewer)

[Mr Smin]

Dear AP Forums,

I want to query my Sites table and then loop through some VBA for each of the sites returned by the query.

I imagine I need to do this by putting the query results into an array but I can't figure out how. Please could someone recommend a tutorial, or explain this? (please speak slowly!).

Background:
I am using Access 2003.
tblSites has PK SiteID (integer).
I have a working query to select the sites I need to report on - it's currently filling a ComboBox.
I also have the loop working 'in principle' - just that it's looping 1 to 10 instead of 6, 21, 70, 134... or whatever the query is returning.

 

[Mr. B]

Look into opening a Recordset.

You can open a recordset using a table, a query or an sql statement.
Dim Rs as DAO.Recordset
Dim cntr, varRecCnt

set Rs = currentdb.openrecordset("NameOfTableOrQuery")
'access all records to get an accurate record count
Rs.MoveLast
Rs.MoveFirst
varRecCnt = Rs.RecordCount
'use a "For loop" to move through each of the records
For cntr = 1 to varRecCnt

'do what ever you need to do to the current record here
'you would refer to any field in the record set like:
Rs.Fields("NameOfField).value = SomeValue
Rs.MoveNext
Next cntr

 

[Guus2005]

A query can return a lot of rows. It is not wise to populate an array with a complete recordset. An array is quite static. You can move around a recordset more easily by using the Find method.

Below a sample that prints the contents of a field to the immediate window (control-g)

    dim rs as recordset

    set rs = currentdb.openrecordset("NameOfTableOrQuery")

    with rs
        do while not .eof
            debug.print rs.Fields("NameOfField).value 
            .movenext
        loop
    end with

Enjoy!

 

[Mr Smin]

Thanks guys. Works well.