Conditional looping with ADO Recordset in VBA

0
votes

In the past, I've always generated reports by querying a database, dropping it on a worksheet, sorting, reading into an array, and then looping through to create the documents. What I'm trying to do now is generate the reports directly from the recordset.

The problem I have though is when using arrays I had an absolute record number, so I could check if a field in the previous record was the same as in the current record.

What is the best way of going about doing this with a recordset? Store the field value in a variable? Use nested While loops? Get a total record count and use absolute positions to move back and forth (which doesn't seem like a good idea)?

UPDATE The recordset contains multiple records from 20 different companies. I sort the query by the company name and then start loop through it. I then need to check each record to see if it's from the same company as the previous and if not create a new worksheet for that company.

1
vbaexcelado
Some of those seem like reasonable approaches - why not try one out ? – Tim Williams
I felt this was most likely a common task and someone would be able to quickly answer it. More importantly though, I was interested in finding out what the best approach was so I don't end up with something that works but shouldn't be used for whatever reason. – pheeper
It's difficult to know what the best approach is without knowing your exact needs as far as how you need to construct your reports. If you just need to track when the value of one or more specific fields changes then using a variable to track the previous value works fine. – Tim Williams
I agree with Tim, Use variables to track whatever, it's better than going back and forth with the recordset. The latter should be the last choice. – A.S.H
After reading your update, it's perfectly fit for a VBA variable, let's call it currentCompany. In each iteration on the Recorset, just if rs.fields("company") <> currentCompany then ...create new WS – A.S.H

1 Answers

0
votes

I usually do the sort in the query using an "Order By" clause. Then just loop through the sorted records using a variable to track the current sort order name and detect the name change by comparing the current record name field to the variable value.