RE: loop and recordset

Thank you for your tip. It is very helpful!!

"SteveS" wrote:

PMFJI,

The problem is *when* you execute "rst.MoveNext". Look at the following code
snip:

'---
rst.MoveFirst
A_ID = rst.Fields("Original_Recip_Id")
A_EndDt = rst.Fields("Last_Dt_Of_Service")

While Not rst.EOF

rst.MoveNext
B_ID = rst.Fields("Original_Recip_Id")
B_StartDt = rst.Fields("first_dt_of_service")

Wend
'---

OK, assume there are three records in the recordset and the current record
is the third one. The next statement to be executed is "Wend"; next statement
will be at the top of the loop "While Not rst.EOF". So it checks for EOF -
which is false. The next statement is "rst.MoveNext" which moves to "EOF".
And the next statement tries to assign "B_ID" a value when there is no valid
record - which results in an error.

The "rst.MoveNext" statement should be the line before "Wend". You would
need to add a line, "rst.MoveNext", just above "While Not rst.EOF". Delete
the "rst.MoveNext" after "While Not rst.EOF".


I rewrote your sub (a little)..... see if this will work for you. (on a copy!)
You should also have some error handling......


'-------------------------
Sub myfunction()

'fields in the enrollment tbl: ID, Original_Recip_Id,
First_Dt_Of_Service, Last_Dt_Of_Service

Dim myConnection As ADODB.Connection
Set myConnection = CurrentProject.Connection
Dim rst As New ADODB.Recordset
rst.ActiveConnection = myConnection
rst.Open "enrollment_2", , adOpenStatic

Dim A_ID As String
Dim B_ID As String
Dim A_B_elapse As Double
Dim A_EndDt As Date
Dim B_StartDt As Date

rst.MoveFirst
A_ID = rst.Fields("Original_Recip_Id")
A_EndDt = rst.Fields("Last_Dt_Of_Service")

rst.MoveNext

Do 'loop thru recordset
B_ID = rst.Fields("Original_Recip_Id")
B_StartDt = rst.Fields("first_dt_of_service")

If A_ID = B_ID Then
A_B_elapse = DateDiff("d", A_EndDt, B_StartDt)
If A_B_elapse <> 1 Then
Debug.Print A_ID, B_ID, A_EndDt, B_StartDt, A_B_elapse
End If
End If

A_ID = B_ID
A_EndDt = rst.Fields("Last_Dt_Of_Service")

rst.MoveNext

Loop Until rst.EOF

'clean up
rst.Close
Set rst = Nothing
Set myConnection = Nothing

End Sub
'-------------------------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"Lan" wrote:

Hi

below is my recordset. They are enrollment records. I want to identify
those members whose membership lapsed. In this case, ID 222, his membership
elaped three months from May to July. ID 333's membership elapsed 1 month.
I am trying to write the code that will generate me that information

(member ID)
Original_Recip_Id First_Dt_Of_Service Last_Dt_Of_Service
111 11/9/2006 11/30/2006
111 12/1/2006 12/31/2006
222 4/1/2006 4/30/2006
222 8/1/2006 8/26/2006
333 4/1/2006 4/30/2006
333 6/1/2006 6/10/2006

Here is what I have so far. It gives me the right information. But it
cannot detect the end of recordset. Error message: "either EOR or EOF is
True, or the current record has been deleted. Request operation requires a
current record."


Sub myfunction()

'fields in the enrollment tbl: ID, Original_Recip_Id, First_Dt_Of_Service,
Last_Dt_Of_Service

Dim myConnection As ADODB.Connection
Set myConnection = CurrentProject.Connection
Dim rst As New ADODB.Recordset
rst.ActiveConnection = myConnection
rst.Open "enrollment_2", , adOpenStatic

Dim A_ID As String
Dim B_ID As String
Dim A_B_elapse As Double
Dim A_EndDt As Date
Dim B_StartDt As Date


rst.MoveFirst
A_ID = rst.Fields("Original_Recip_Id")
A_EndDt = rst.Fields("Last_Dt_Of_Service")

While Not rst.EOF

rst.MoveNext
B_ID = rst.Fields("Original_Recip_Id")
B_StartDt = rst.Fields("first_dt_of_service")


If Not A_ID = B_ID Then
Do Until A_ID = B_ID
A_ID = B_ID
A_EndDt = rst.Fields("Last_Dt_Of_Service")
rst.MoveNext
B_ID = rst.Fields("Original_Recip_Id")
B_StartDt = rst.Fields("first_Dt_of_service")
A_B_elapse = B_StartDt - A_EndDt
Loop
End If

A_B_elapse = B_StartDt - A_EndDt
If A_B_elapse <> 1 Then
Debug.Print A_ID, B_ID, A_EndDt, B_StartDt, A_B_elapse
End If

A_ID = B_ID
A_EndDt = rst.Fields("Last_Dt_Of_Service")

Wend

rst.Close
Set rst = Nothing
Set myConnection = Nothing

End Sub


Please help.

Thank you!!

.

Relevant Pages
Loading