Update second record

[Heidestrand]

Hello,
I want to update a table and created an update procedure to do that:

                If Not rstDaten.NoMatch Then
                    DoEvents
                    Do While (varOrderNumber = ![sapsys_SAPNr] And ![sapsys_KMATID] = varMatID)

                        If ![sapsys_date] <> rstDaten![Kalendertag] And ![sapsys_KMATID] = rstDaten![MaterialID] Then
                            .Edit
                            ![sapsys_date] = rstDaten![Kalendertag]
                            .Update
                        End If
                            
                    DoEvents
                    .MoveNext

The only thing is: I want to start updating with the second record set after I found a match. Is there a function to do that? I already tried .MoveNext, .FindNext, .NextRecordSet before .Edit, but nothing worked
So when he finds a record set, he should move on to the second one and start updating from here.

Any ideas?

 

[Cronk]

Could you supply more of the code, showing all of the loop(s) and the source of the recordsets.

 

[arnelgp]

hello, create a a variable:

Dim bolSecondRecord As Boolean
edit this part:

    With rstSapSys
        If .RecordCount > 0 Then .MoveFirst
        While Not .EOF
            ' save order number to variable, well use it later
            varOrderNumber = ![sapsys_SAPNr]
            varMatID = ![sapsys_KMATID]
            ' find order number in tblDatenAusExcelNeu
            rstDaten.FindFirst "[AngelegtAm] = " & varOrderNumber & " And [Verkaufsbeleg] = " & varMatID
            If Not rstDaten.NoMatch Then
                ' if we found it were lucky!
                DoEvents
                [COLOR=Blue]bolSecondRecord = False
[/COLOR]                Do While (varOrderNumber = ![sapsys_SAPNr] And ![sapsys_KMATID] = varMatID)
                    ' compare the dates
                    ' we need to update only if:
                    ' 1. sapsys_date = AnglegtAm and
                    ' 2. AnglegtAm date < Kalendartag ?
                    If ![sapsys_date] = rstDaten![AngelegtAm] And rstDaten![AngelegtAm] <> rstDaten![Kalendartag] Then
                        [COLOR=Blue]If bolSecondRecord Then[/COLOR]
                            .Edit
                            ![sapsys_date] = rstDaten![Kalendartag]
                            .Update
                        [COLOR=Blue]End If[/COLOR]
                    End If
                    DoEvents
                    .MoveNext
                    ' exit loop if we are end of file pointer
                    If .EOF Then Exit Do
                    rstDaten.FindNext "[AngelegtAm] = " & varOrderNumber & " And [Verkaufsbeleg] = " & varMatID
                    ' we also exit if there no more match
                    If rstDaten.NoMatch Then Exit Do
                   [COLOR=Blue] bolSecondRecord = True[/COLOR]
                Loop
            Else
                .MoveNext
                DoEvents
            End If
            
        Wend
    End With

 

[Heidestrand]

@arnelgp: Thanks a lot again for your help, I will test it tomorrow and tell you if it worked