Recordset Record Movement Issue 1

[ooch1]

Hello,

I am trying to compare and flag a set of data depending on how the values of each row change when moving down the data.

I have created 2 recordsets (rs and rs2). M = the meterpoint reference and m2 = the meterpoint ref of the 2nd recordset as my code hoghloghts below:

 Dim F As Boolean
    Dim C, C2, M, M2, S, S2 As String
    Dim rs2 As Variant
    Dim cs As String
        
    Set rs2 = rs.Clone
    
    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
    
    rs.MoveFirst
[COLOR=red][b]    rs2.Move 2[/b][/color]
         
    Do Until rs2.EOF 'loop from 1st row last row
        
       
        If F And ((M <> M2) Or (M = rs.EOF)) And _
        (C <> 0.1055 And C <> 0.1056 And C <> 0.1089 And C <> 0.0928 And C <> 0.1193) Then............

THe problem i am having is that the code highlighted red, i was hoping to move the rs2 to the 2nd row, but the value of M2 within the if statement is still pulling back the first meterpoint ref.

Please can some let me know how i can get the value of M2 to the that of the 2nd row meterpoint ref??

Also when i have continued through the if statement the values (M,C etc) all seem to remain on the 1st row, so how can i move all these records down to the next row?

I'm sure there is a simple trick that i'm missing, so one you code guru's must know how to put things right and get this list moving!

OOch

 

[PHV]

Put this 2 lines BEFORE (instead of after) assigning values to your variables, ie just after the Set rs2 line:
rs.MoveFirst
rs2.Move 2

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 once again PHV!

OOch