looping through all records with VBA

[Michael Goerz]

Hi,

I'm trying to write a loop that cycles through all the records. This I use
for doing comparisons between all records or to export all records to a text
file. I'm using the following code:

'*************************************
....
'Finding the count of all records

DoCmd.GoToRecord , , acLast 'Goto the last record
CountOfRecords = Form_Input.CurrentRecord 'save position

'now the loop

For RecordNr = 1 To CountOfRecords

DoCmd.GoToRecord , , acGoTo, datensatz 'Load the record

'Now, I do things like writing the values of the record to a text file
'or using a second similar for-next-loop way to compare each record
'with every other one.

Next RecordNr
....
'************************************************* *

This however yields very strange results. Most of the time, the value of
CountOfRecords is incorrect, especially if I just added new records, the new
records are not taken into account. But there are other errors too. In fact,
it seems very much unpredictable what happens. In the case where I export to
the textfile, I frequently get the values of the same record written
CountOfRecords-times.
What code would you use to loop through all the records? Is there any other
way to find the total count of records that always yields the correct
result? Any other hints?

Thanks in advance,
Michael Goerz

[fp]

Rather than use looping through records, why not use the power of a
relational database? Select distinct values for each field or field
combination you are looking for, save those then do your export based on the
saved information. Rather than save the search values in a text file, use a
temporary table. It should make your coding a lot easier.

--
******************************
Fred Parker
Lynn Consulting Group, L.L.C.
http://www.lynnconsultinggroup.com
******************************

[rkc]

"Michael Goerz" <mg********@gsp-online.org> wrote in message
news:bs************@ID-167374.news.uni-berlin.de...

Hi,

I'm trying to write a loop that cycles through all the records. This I use
for doing comparisons between all records or to export all records to a text file. I'm using the following code:

If you really want to loop through all the records of a form's recordsource,
it would probably be easier to use DAO and the form's recorsetclone.

[Ryan]

Cut down a bit from some code I wrote a while back, but it may be of some help.

Ryan

Function FRED()
On Error Resume Next
Dim DB As Database
' Microsoft DAO 3.6 Object Library MUST be available for this
' in the references
Dim RSValues As DAO.Recordset
Set DB = CurrentDb()
Set RSValues = DB.OpenRecordset("myTableName", dbOpenDynaset, dbSeeChanges)
RSValues.MoveLast
RSValues.MoveFirst ' Must do this to set the recordset properly.
While Not RSValues.EOF
' Now step through all available records and do whatever you need to do
Wend
MsgBox "Finished", vbInformation, "Whatever your app is called"
End Function

[Lyle Fairfield]

ry********@hotmail.com (Ryan) wrote in
news:78**************************@posting.google.c om:

Cut down a bit from some code I wrote a while back, but it may be of
some help.

Ryan

Function FRED()
On Error Resume Next
Dim DB As Database
' Microsoft DAO 3.6 Object Library MUST be available for this
' in the references
Dim RSValues As DAO.Recordset
Set DB = CurrentDb()
Set RSValues = DB.OpenRecordset("myTableName", dbOpenDynaset,
dbSeeChanges) RSValues.MoveLast
RSValues.MoveFirst ' Must do this to set the recordset properly. No While Not RSValues.EOF
' Now step through all available records and do whatever you need to
do
Wend
MsgBox "Finished", vbInformation, "Whatever your app is called"
End Function

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Pink Panther]

Just some minor clarifications - not sure if Lyle was trying to make
them, his comments are rather sparse...

Function FRED()
On Error Resume Next
Dim DB As Database
' Microsoft DAO 3.6 Object Library MUST be available for this
' in the references
Dim RSValues As DAO.Recordset
Set DB = CurrentDb()
Set RSValues = DB.OpenRecordset("myTableName", dbOpenDynaset, dbSeeChanges)
RSValues.MoveLast
RSValues.MoveFirst ' Must do this to set the recordset properly.
You only need to do this if you wish to get the recordsets recordcount
property otherwise it's an extra overhead.
While Not RSValues.EOF
' Now step through all available records and do whatever you need to do
rsValues.MoveNext 'otherwise you'll just sit there and never
reach EOF
Wend
I thought I read somewhere where While and Wend were being dropped
from VBA (sorry can't find the reference so treat as hearsay). I tend
to use do While|Until..loop, it's a little more flexible for loops.

'in-line error handling as using On Error Resume Next; unless you
really
'do want to ignore any error (not a good habit IMO)
If err.Number = 0 then MsgBox "Finished", vbInformation, "Whatever your app is called" else
msgbox err.number & " : " & err.description
end if End Function

Peter

[Ryan]

It was only an example, if I posted all of the code it would have
overcomplicated the matter. The code I wrote does have all of this in,
but I cut it down for the example as it wasn't particularily relevant
IMHO (and was trying to get the point across quickly), however they
are still valid points and should be used.

Interesting about the While...Wend comment. Will look for more info on
that as I've not heard anything about that.

Ta

R

Pi*************@mail.com (Pink Panther) wrote in message news:<ec**************************@posting.google. com>...

Just some minor clarifications - not sure if Lyle was trying to make
them, his comments are rather sparse...

Function FRED()
On Error Resume Next
Dim DB As Database
' Microsoft DAO 3.6 Object Library MUST be available for this
' in the references
Dim RSValues As DAO.Recordset
Set DB = CurrentDb()
Set RSValues = DB.OpenRecordset("myTableName", dbOpenDynaset, dbSeeChanges)
RSValues.MoveLast
RSValues.MoveFirst ' Must do this to set the recordset properly.

You only need to do this if you wish to get the recordsets recordcount
property otherwise it's an extra overhead.

While Not RSValues.EOF
' Now step through all available records and do whatever you need to do

rsValues.MoveNext 'otherwise you'll just sit there and never
reach EOF

Wend

I thought I read somewhere where While and Wend were being dropped
from VBA (sorry can't find the reference so treat as hearsay). I tend
to use do While|Until..loop, it's a little more flexible for loops.

'in-line error handling as using On Error Resume Next; unless you
really
'do want to ignore any error (not a good habit IMO)
If err.Number = 0 then

MsgBox "Finished", vbInformation, "Whatever your app is called"

else
msgbox err.number & " : " & err.description
end if

End Function

Peter