Loop Not Advancing to Next Record

[Robert M]

The program bellow is designed to go from record to record incressing the number in the Cyc Field by one. What is Happening is that it is incressing the same record by one so that the end result is the number of records I have in the query and not incrementing the cyc field from 1 to 2 or 3 to 4

Private Sub RollCyc_Click()
Dim DB As DAO.Database
Dim RS As DAO.Recordset

Set DB = CurrentDb
Set RS = DB.OpenRecordset("CPEHrs_Data-qry")
RS.MoveFirst
Do Until RS.EOF
Debug.Print Cyc; " Begin Cyc #"
Debug.Print ID_Data; " Rec #"
Cyc = Cyc + 1
Debug.Print Cyc; " End Cyc #"
RS.MoveNext
Loop

End Sub

This is a ending sample of my debug print

3086 Begin Cyc #
48 Rec #
3087 End Cyc #
3087 Begin Cyc #
48 Rec #

It is working but not advancing to the next record.

Thank you for any information and help you have to offer.

Robert M

 

[Steve R.]

I did not see an obvious error.

In theory this is not absolutely necessary but I use:

Dim RS As DAO.Recordset

The syntax format that I have been using is:

Set RS = CurrentDb.OpenRecordset("SELECT * FROM CPEHrs_Data-qry", dbOpenDynaset)

You may need "dbOpenDynaset", in order to cycle through the recordset.

 

[Robert M]

Thanks Steve. And Yes I do need the "dbOpenDynaset" to cycle through along with a Me.recordset.Movenext rather than a RS.Movenext. Here is my resulting Program

Dim DB As DAO.Database
Dim RS As DAO.Recordset

Set DB = CurrentDb
Set RS = DB.OpenRecordset("CPEHrs_Data-qry", dbOpenDynaset)
RS.MoveFirst
Do Until RS.EOF
Me.Recordset.MoveNext
Debug.Print Cyc; " Begin Cyc #"
Debug.Print ID_Data; " Rec #"
Cyc = Cyc - 1
Debug.Print Cyc; " End Cyc #"
Loop

Works Great but does not recognize when the EOF is. So back to the Drawing Board and Googling for solutions.

Thanks for your help and suggestions you are greatly appreciated.

Robert M

 

[Steve R.]

You moved "Me.Recordset.MoveNext" to the beginning of the loop which means that you will be starting with the second record, not the first. Move "Me.Recordset.MoveNext" to the line immediately preceding "loop".

I assume, even though it should be obvious, that you have more than one record. I usually test for the number of records just before entering the loop. (Actually before "movefirst") That way if there are zero records I can adjust the program to deal with that situation, such as skipping the loop.

intFormRecordCount = RS.RecordCount

 

[Robert M]

Thanks for the information Steve. Got it up and running with the following program


Dim DB As DAO.Database
Dim RS As DAO.Recordset

Set DB = CurrentDb
Set RS = DB.OpenRecordset("CPEHrs_Data-qry", dbOpenDynaset)
Me.Recordset.MoveFirst
Do Until Me.Recordset.EOF
Me.Recordset.MoveNext
Cyc = Cyc - 1
Loop

And your right in that the Me.Recordset.Movenext should be just before the loop. Will Change it and see if it starts crashing again.

Thanks for your help and input on this problem.

Robert M