iterators (pointers)

  • Thread starter Thread starter Craig
  • Start date Start date
C

Craig

I am trying to loop through a table, so I do a loop like:

do while not tbl.eof
'check something/do something
loop

However, when doing that, you can only run one iterator
through a table. I was wondering how you can run multiple
iterators through the same table. If anyone can help, or
has any questions, please e-mail me at
(e-mail address removed)

Thanks for any help!!

Craig
 
hi

Use DCount to find the number of records in the table then you can use a For
Next loop to iterate to your hearts content.

Regards, Wesley Kendrick
 
You could use ADO and load the table into a recordset.
You then have 2 options:then clone the recordset:

1) Have several variables to use as pointers and use the
AbsolutePosition to navigate to the corresponding records

2) Create disconnected clones of the recordset and iterate
through each separately.

The first option probably involves more complex code since
you need to keep track of each of your pointers and the
recordset navigation. The second option is nice but will
use lots of memory for large recordsets.

For option 1, refer to the ADO documentation (you can find
it by searching the MSDN library for ADO Recordset or
AbsolutePosition). For option 2, refer to the code below
for how to create cloned recordset copies - after creating
the clones you can iterate through each of them
independently:

Dim MyTable as ADODB.Recordset
Dim MyTable1 as ADODB.Recordset
Dim MyTable2 as ADODB.Recordset

Set MyTable = New ADODB.Recordset
MyTable.CursorLocation = adUseClient
MyTable.Open "SELECT * FROM [TABLE NAME]",
CurrentProject.Connection, adOpenDynamic, adLockReadOnly

Set MyTable.ActiveConnection = Nothing

Set MyTable1 = MyTable.Clone
Set MyTable2 = myTable.Clone
 
Wesley...

Thank you for the response, but I am looking for how to
keep track of a record with one iterator while scanning
the rest of the table with the other. I would need 2
pointers. One stationary and one that loops so that the
stationary pointer only moves when the looping iterator
goes around once. This is so I can compare one record to
every other record in the table. Is this what you were
talking about? If so, can you give an example of a
for/next loop? If not, can you give me some advice?
Thanks again...

Craig
 
This is so I can compare one record to
every other record in the table.
Click to expand...

Use a self join:

SELECT a.Thingy AS AThing,
b.Thingy As BThing,
FROM ThingTable AS a, ThingTable AS b
ORDER BY a.Thingy, b.Thingy

note there is no JOIN clause or WHERE...

HTH


Tim F
 
Craig said:
I am trying to loop through a table, so I do a loop like:

do while not tbl.eof
'check something/do something
loop

However, when doing that, you can only run one iterator
through a table. I was wondering how you can run multiple
iterators through the same table.
Click to expand...


Depending on what you want to do when you find a matching
record, you could open a second recordset (read only) on the
same table.

Note that it would be a heck of a lot faster to use a query
to find matching records and open a recordset on the query.
This way you woud know immediately which record matched what
record and eliminate the loop altogether.
 
Back
Top