Looping thru a recordset

[Richard Schollar]

Utterly simple question I'm sure, but how do I loop thru a recordset and process individual records? I could do this in Excel with my eyes closed, but I'm so wedded to the Excel Object Model, I can't fathom it in Access.

Does it make any difference if the recordset is sourced from a Table or a Query?

Thanks for your time!

Richard

 

[Joe4]

What exactly do you need to do to your data?

The VBA code in Access to loop through data can be a bit more complex than it is for Excel, but many times this is not necessary. Often times, you can write an Action Query (Update, Append, Delete) in Access that will do what you want without having to use VBA loops.

 

[Richard Schollar]

Hi J

Unfortunately, it's rather involved:

1. I need to compare the first record to the next record, and if certain conditions are fulfilled, then I need to copy both records to a separate table.

2. If only some conditions are fulfilled, then I need to copy both records to a second separate table (because these records require further processing).

3. If the two records don't match at all, then I need to copy only the first record to the second separate table.

In all cases above, I then need to move onto the next non-copied record in the next loop ie for cases 1. and 2. this will be record number 3, but in case 3. this will be record number 2 as only record number 1 was copied.

The resulting 2 tables will basically provide me with a set of records that require no further processing at all, and a second set which will require further processing.

I hope this makes sense and I haven't lost you!

Thanks.

Richard

 

[oric]

Have you tried assigning to variables the values of your recordset as you move along? Then do your comparison based on the variables.

I.E.
------------
Dim var1, var2
..
..
var1=recordset.fields("fieldname").value
bla

If var1 bla bla Then

instructions
----------
Or something like that.

 

[Joe4]

It looks like it will definitely require VBA.

Have you worked much with DAO or ADO recordsets?

I learned Excel and Excel VBA first, and coming over to Access without much knowledge regarding DAO or ADO recordsets, I found it to be complex, and there was a lot to learn. And your problem seems complex, in and of itself, which probably will require a fair amount of "educating" on your part, unless you can get someone to write it for you.

The best advice I can give you to purchase a book that discusses these subjects and/or search the web.

I like a book called "Learn Microsfot Access 2000 Programming by Example" by Julitta Koral, mainly because it has a lot of good examples which are easy to follow. But I am sure that there are many good books out there on the subject.

Hope this was helpful!

 

[Norie]

Richard

This is how I generally loop through a recordset.

 rstWide.MoveFirst
    
    While Not (rstWide.EOF)
        ' code to deal with recordset
        rstWide.MoveNext
    Wend

 

[Richard Schollar]

Guys

Thanks - all the posts are helpful. My knowledge of ADO/DAO is limited to moving data back and forth between Excel and Access - this is something different therefore I must do some further background investigation.

Norie, I take it the recordset has an index of some kind so rstWide.MoveFirst accesses the first record. If I want to then compare this record with the next record, how would I go about doing this? How would I reference the next record? Something like:

rstWide(record2)

?

Cheers - I'm a learnin'!

Richard

 

[Norie]

Richard

I think you will need to work with variables as suggested by oric.

What exactly are you doing?

Are you 100% sure you need to loop through the recordset?

 

[Richard Schollar]

Unfortunately I do because I need to compare each record to the next. I am happy enough using variables (such as an array) to hold all the field information for a record.

I really need to go home and read thru some of the Access programming books I have for assigning field data of a particular record to a variable.

I think I can take it from here - you guys have seeded the flame which will hopefully lead to a roaring fire (interesting metaphor, I know).

Ordinarily, I would just have done all this in Excel, but I'm currently dealing with such large recordsets (initially 135,000 ish) which is then pared down to 28,000 records (on which I then need to do the looping) that I wanted to do it all in Access. Another approach is to shunt the 28,000 records into Excel to perform the looping, but like I say, I would like to do it all in Access and not touch Excel (I may potentially have a pared recordset in excess of 65,000 records some months too).

Thank you people for all your advice.

Norie - you've been most useful again!

Richard

 

[Joe4]

Parsnip,

Here is also a good link of traps to avoid when looping through recordsets in Access VBA.

http://www.everythingaccess.com/tutorials.asp?ID=VBA-Traps:-Working-with-Recordsets

(see the tips below the book info and adds)