Code Does Not Recognise EOF of a Recordset Clone?

[ooch1]

Hello, i have also posted this on the end of a previous access thread, but thought it probably best to treat my problem in it's own entity.

Bascially, i have created a clone of a recordset (rs2) so i can test the 2 against each other. For some reason though, my code does not recognise the EOF of rs2. So every time i run it i get the EOF error message.
When i step into the code it and hover over the flag, it does not show anything, when i would expect to show "EOF=False/True".

Does anyone know how can overcome reaching the end of rs2 without it moving any further forwrds???

Does VBA recognise the EOF of a cloned recordset?

My full code is as follows:

Sub T2CUpt()

Dim rs As ADODB.Recordset
Dim strOrderQy As String
Dim i As Integer

'create recordset objest
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic

strOrderQy = "SELECT A.MeterPointRef, A.SiteRefNum, A.[Confirmation Reference], A.[Contract Num], A.PricesIDNum, A.Price," & _
                       "A.StandingCharge, A.[Site status], A.[Effective From Date], A.[Ceased Date], A.NA_SOURCE_STATUS, " & _
                       "a.NA_Ctrt_STATUS " & _
        "FROM [A01-NBS_GAS_ROOTDATA_20040923] as A " & _
        "ORDER BY A.MeterPointRef, A.SiteRefNum, A.[Confirmation Reference], A.[Contract Num], A.PricesIDNum;"

With rs
    .Open strOrderQy, _
    CursorType:=adOpenKeyset, _
    LockType:=adLockOptimistic

 
        
    'Call T2CColUpt
    Dim F As Boolean
    Dim C, C2, M, M2, S, S2, X As String
    Dim rs2 As Variant
    Dim cs As String
        
    Set rs2 = rs.Clone
    
    rs.MoveFirst
    rs2.Move 1
    Do While Not rs.EOF 'loop from 1st row last row
    
        cs = Nz(rs![NA_Ctrt_STATUS], "")
        C = Nz(rs![standingcharge], ""): M = rs![meterpointref]: S = Nz(rs![siterefnum], "") ' store Charge and MeterRef of row 2
        C2 = Nz(rs2![standingcharge], ""):  M2 = rs2![meterpointref]: S2 = Nz(rs2![siterefnum], "") ' store rs2's refs
             
        If F And ((M <> M2) Or (M2 = rs2.EOF)) And _
            (C <> 0.1055 And C <> 0.1056 And C <> 0.1089 And C <> 0.0928 And C <> 0.1193) Then
            ' f is true and either MeterRef changed or we're in last row, so flag preceding row and set f to false
            rs.Fields("NA_Ctrt_STATUS") = "T2C"
            rs.Update
            F = False
        ElseIf Not F And (M = M2) And C <> C2 And _
            (C = 0.1055 Or C = 0.1056 Or C = 0.1089 Or C = 0.0928 Or C = 0.1193) Then ' f is false and Charge changed for same MeterRef, so set f to true
            F = True
        End If
        rs.MoveNext
[COLOR=red yellow]rs.MoveNext
If rs2.EOF Then _
 rs2.MoveLast[COLOR=green](i'm sure there is a better way of stopping it moving forward???)[/color]
 Else rs2.MoveNext[/color]
 Loop ' proceed with next row
   
    rs.Close
    rs2.Close
    Set rs = Nothing
    Set rs2 = Nothing

End With
End Sub

OOch

 

[PHV]

Have you tried to change this ?
Dim rs2 As Variant

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ooch1]

Thanks PHV, i have managed to overcome this problem, but alas the code still does not work as planned. Although i fell the end is in sight.

Do While Not rs.EOF 'loop from 1st row last row
        CS = Nz(rs![NA_Ctrt_STATUS], ""): C = Nz(rs![standingcharge], ""): M = rs![meterpointref]: S = Nz(rs![siterefnum], "") ' store rs refs
        C2 = Nz(rs2![standingcharge], ""):  M2 = rs2![meterpointref]: [COLOR=red yellow]S2 = Nz(rs2![siterefnum], "")[/color] ' store rs2's refs
        If F And ((M <> M2) Or (rs2.AbsolutePosition = rsEnd)) And _
            (C <> 0.1055 And C <> 0.1056 And C <> 0.1089 And C <> 0.0928 And C <> 0.1193) Then _
            ' f is true and either MeterRef changed or we're in last row, so flag preceding row and set f to false
            If S = S2 Then rs.Fields("NA_Ctrt_STATUS") = "T2C" _
            Else rs.Fields("NA_Ctrt_STATUS") = "T2Ccoo"
            rs.Update
            F = False
        ElseIf Not F And (M = M2) And C <> C2 And _
            (C = 0.1055 Or C = 0.1056 Or C = 0.1089 Or C = 0.0928 Or C = 0.1193) Then
            F = True ' f is false and Charge changed for same MeterRef, so set f to true
        End If
        rs.MoveNext
        Select Case M2
            [COLOR=red yellow]Case Is = rs2eof And S <> S2
                S = 999 And rs2.MoveLast[/color]
            Case Is = rsEnd And S = S2
                rs2.MoveLast
            Case Else
                rs2.MoveNext
        End Select
           Loop ' proceed with next row

Essentially, this code now works fine except for when it reaches the last row.
What should happen is that if the Siterefnumber of rs (S) differs from rs2 (S2) then it should be flagged T2Ccoo, else flag it T2C.
When it is reaching the end it is working fine (not erroring), but because the variables are re-set at the top of the loop it thinks that s and s2 are the same amd so the flag is incorrect.

Can you think of a way of overcome this issue??

OOch