loop and recordset

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

  • RE: loop and recordset
    ... reaches the end of recordset. ... Dim myConnection As ADODB.Connection ... Set myConnection = CurrentProject.Connection ... those members whose membership lapsed. ...
    (microsoft.public.access.modulesdaovba)
  • RE: loop and recordset
    ... assume there are three records in the recordset and the current record ... I rewrote your sub..... ... Dim myConnection As ADODB.Connection ... Set myConnection = CurrentProject.Connection ...
    (microsoft.public.access.modulesdaovba)
  • RE: loop and recordset
    ... assume there are three records in the recordset and the current record ... I rewrote your sub..... ... Dim myConnection As ADODB.Connection ... Set myConnection = CurrentProject.Connection ...
    (microsoft.public.access.modulesdaovba)
  • RE: loop and recordset
    ... be an option to replace the exit do with exit sub. ... reaches the end of recordset. ... Dim myConnection As ADODB.Connection ... Set myConnection = CurrentProject.Connection ...
    (microsoft.public.access.modulesdaovba)
  • RE: loop and recordset
    ... If Err> 0 Then Exit Sub ... Dim myConnection As ADODB.Connection ... Set myConnection = CurrentProject.Connection ... those members whose membership lapsed. ...
    (microsoft.public.access.modulesdaovba)