iterators (pointers)

[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

 

[Guest]

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

 

[K Dales]

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

 

[Craig]

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

 

[Tim Ferguson]

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

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

 

[Marshall Barton]

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.

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.