what does rx.MoveNext really do (really only rx?) 2

[stefanhab]

hi i have a code, that doesnt work - but i don't know why, since the structure seems logical to me.

in the following i will post the code and make some annotations on it (so i try to communicate, what i think the code means for the compiler)

Sub COPY()

    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim var_pre_trim As String
    Dim var_post_trim As String
            
    Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM Working_Table_36 ORDER BY PROFIL_ID")  'opens recset ordered by PROFIL_ID this is important
    Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM 1_02_01_01bis2_05_01_08")  ' opens empty recset

    rs2.AddNew  ' in the first run adds new row to r2
    rs2.Fields("PROFIL_ID") = rs1("PROFIL_ID")  ' with same PROFIL_ID as r1
    rs2.Fields("KATALOG_ID") = rs1("KATALOG_ID")  ' and same KATALOG_ID
    rs2.Update  ' now it creates the row
    
    Do Until rs1.EOF  ' go step by step through the follwoing lines until the cursor reaches the edn of recset1
         
     var_pre_trim = rs1("CONCAT_ID")  ' read CONCAT_ID from recset1
     var_post_trim = Trim(var_pre_trim)  ' trim it
        
        Select Case var_post_trim  ' look at the content of the var and decide...
          
            Case Is = "1_02_01_01"  ' in this case do...
                rs2.Edit  ' write into the line active line of recset 2
                var = CInt(rs1("SWERT"))  ' create an integer
                rs2.Fields("1_02_01_01") = var  ' write it into r2
                rs2.Update  ' do it
            Case Is = "1_02_01_02"
                rs2.Edit
                var = CInt(rs1("SWERT"))
                rs2.Fields("1_02_01_02") = var
                rs2.Update
            Case Is = "1_03_01_01"
                rs2.Edit
                var = CInt(rs1("SWERT"))
                rs2.Fields("1_03_01_01") = var
                rs2.Update
        End Select  ' end selection, if there is something else in the var - do nothing
        
        rs1.MoveNext  ' jump into the next line BUT ONLY IN RECSET 1
        
        var_profil_1 = rs1("PROFIL_ID")  'read the PROFIL_ID
        var_profil_2 = rs2("PROFIL_ID")  'read the PROFIL_ID of recset2 AND HERE HE MAKES PROBLEMS
        
        If var_profil_2 <> var_profil_1 Then  'now compare both values - if they are different do...
            rs2.AddNew  ' add one new line in recset2
            rs2.Fields("PROFIL_ID") = rs1("PROFIL_ID")
            rs2.Fields("KATALOG_ID") = rs1("KATALOG_ID")
            rs2.Update
        End If
        
    Loop
    Set rs1 = Nothing
    Set rs2 = Nothing
End Sub

he always makes problems with that line:

rs1.MoveNext

var_profil_1 = rs1("PROFIL_ID")
var_profil_2 = rs2("PROFIL_ID") XXXXXXX


and complains that there is no recordset - what i dont unterstand, since in the first run, i force him to create one and later he has to make the decision, after jumping into the next row of recset1 if

the new rows PROFIL_ID of recset1 is different to the old PROFIL_ID of recset2

in this case he should create a new row in recset2


many thanks for any kind of help or recommendation
stefan

 

[stefanhab]

herewith i want to emphasise, what i do not unterstand:

access (the compiler?) complains that there is no recordset in recset2

but i do not tell him, that he should jump into a new line?

if the PROFIL_IDs are equal, access should just edit the old row of recest2 with the values of recset1 and then jump into the next row of recset1...

if the PROFIL_IDs are different, what means, there is a need to create a new row in recset2
i tell him to add a new row ?

 

[stefanhab]

oh besides PROFIL_ID is in both recordsets a numeric column, so If var_profil_2 <> var_profil_1 Then
should work

 

[TheAceMan1]

How are ya stefanhab . . .

I don't see the variables [blue]var_profil_1[/blue] and [blue]var_profil_2[/blue] allocated in your code (Dim . . .)!

See Ya! . . . . . .

Be sure to see thread181-473997

 

[stefanhab]

hi aceman1

thanks for your input - i declared the variables and simplified the code a bit to emphasize the real problem

Sub COPY()

    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim var_pre_trim As String
    Dim var_post_trim As String
    Dim var_profil_1 As Integer
    Dim var_profil_2 As Integer
            
    Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM Working_Table_36 ORDER BY PROFIL_ID")
    Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM 1_02_01_01bis2_05_01_08")

    rs2.AddNew
    rs2.Fields("PROFIL_ID") = rs1("PROFIL_ID")
    rs2.Fields("KATALOG_ID") = rs1("KATALOG_ID")
    rs2.Update
    
    Do Until rs1.EOF
         
        var_profil_1 = rs1("PROFIL_ID")
        var_profil_2 = rs2("PROFIL_ID")
        
        If var_profil_2 <> var_profil_1 Then
            rs2.AddNew
            rs2.Fields("PROFIL_ID") = rs1("PROFIL_ID")
            rs2.Fields("KATALOG_ID") = rs1("KATALOG_ID")
            rs2.Update
        End If
        
        rs1.MoveNext
        
    Loop
    Set rs1 = Nothing
    Set rs2 = Nothing
End Sub

but it still doesnt work

 

[fneily]

You just need to add two lines in your last post:
After the first rs2.Update add rs2.MoveFirst
After the second rs2.Update add rs2.MoveNext

 

[stefanhab]

hi fneily,

i added your recommendation - and it worked well (there occured a mistake nr. 6, but thats due to wrong variable declaration i think [PROFIL_ID is sometimes longer i expect])


here is the full code:

Sub COPY()

    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim var_pre_trim As String
    Dim var_post_trim As String
    Dim var_profil_1 As Integer
    Dim var_profil_2 As Integer
            
    Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM Working_Table_36 ORDER BY PROFIL_ID")
    Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM 1_02_01_01bis2_05_01_08")

    rs2.AddNew
    rs2.Fields("PROFIL_ID") = rs1("PROFIL_ID")
    rs2.Fields("KATALOG_ID") = rs1("KATALOG_ID")
    rs2.Update
    rs2.MoveFirst
    
    Do Until rs1.EOF
         
        var_profil_1 = rs1("PROFIL_ID")
        var_profil_2 = rs2("PROFIL_ID")
        
        If var_profil_2 <> var_profil_1 Then
            rs2.AddNew
            rs2.Fields("PROFIL_ID") = rs1("PROFIL_ID")
            rs2.Fields("KATALOG_ID") = rs1("KATALOG_ID")
            rs2.Update
            rs2.MoveNext
        End If
        
        rs1.MoveNext
        
    Loop
    Set rs1 = Nothing
    Set rs2 = Nothing
End Sub

So thanks a lot for your help
Stefan

 

[stefanhab]

hi fneily,

i delected the

    Dim var_profil_1 As Integer
    Dim var_profil_2 As Integer

it obviously is not integer (maybe i try long or something later)

and now it works perfect!

so thanks a lot
and have a nice evening or what time you'll probably might have
Stefan