error in code

[azhar2006]

Good evening, guys.
Is it possible to fix the error in this code?

Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsTarget As DAO.Recordset
Dim intCounter As Integer
Dim intCount As Integer
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rsSource = db.OpenRecordset("tblmastrtow", dbOpenSnapshot, dbReadOnly)
Set rsTarget = db.OpenRecordset("tblmastr", dbOpenTable)

With rsSource
    If Not (.BOF And .EOF) Then
        .MoveFirst
        'add index to table2 for faster searching
        rsTarget.Index = "FullName"
    End If
    Do Until .EOF
        If Not IsNull(!FullName) Then
            rsTarget.Seek "=", !FullName
            If rsTarget.NoMatch Then
                'new record
                rsTarget.AddNew
                rsTarget!FullName = !FullName
            Else
                'update the record
                rsTarget.Edit
            End If
            rsTarget!Rtb = !Rtb
            rsTarget.Update
             
            rsTarget!NumberUpgradeAfter = !NumberUpgradeAfter
            rsTarget.Update

           
            rsTarget!DateUpgradeAfter = !DateUpgradeAfter
            rsTarget.Update
        End If
       
        .MoveNext
intCount = rs.RecordCount
intAnswer = MsgBox("You are about to update " & intCount & " records." & vbCrLf & _
                   "Do you want to continue?", vbInformation + vbYesNo + vbDefaultButton2, "Confirm Update!")
                   If intAnswer = vbYes Then
    Loop
    .Close

End With

Set rsSource = Nothing
rsTarget.Close
Set rsTarget = Nothing
Set db = Nothing
'MsgBox "Complete"

End Sub

 

[The_Doc_Man]

@azhar2006 - your question is perfectly legit, but you need to help us. What is the symptom that makes you think there is something wrong? What do you want this to do that it does not do? Give us a hint, please.

 

[LarryE]

Maybe if you'll tell us which line the error is occurring on. Set a breakpoint and step through (F8). When the error happens it will stop on the line.

 

[azhar2006]

@azhar2006 - your question is perfectly legit, but you need to help us. What is the symptom that makes you think there is something wrong? What do you want this to do that it does not do? Give us a hint, please.

Thank you very much, my dear friend. Two fields have been added (NumberUpgradeAfter) and (DateUpgradeAfter) I have encountered an error in the code as well as in the word Loop

 

[MajP]

You can simply do this with an "Upsert Query". In access this is done with an update query with a left join.
sql - Upserting in MS-access - Stack Overflow

 

[MajP]

I try not to break code blocks. You seem intent in writing the most short hand for your code, but sometimes being a little more detailed is more readable. I would keep the addnew/update and edit/update together

If rsTarget.NoMatch Then
                'new record
                rsTarget.AddNew
                rsTarget!FullName = !FullName
                rsTarget.update
            Else
                'update the record
                rsTarget.Edit
                    rsTarget!Rtb = !Rtb
                 rsTarget.Update
            End If

YOU HAV AN EXTRA UPDATE HERE. GET RID OF IT
            rsTarget.Update
            rsTarget!NumberUpgradeAfter = !NumberUpgradeAfter
            rsTarget.Update

         
            rsTarget!DateUpgradeAfter = !DateUpgradeAfter
            rsTarget.Update
        End If

 

[MajP]

actually you have 2 extra updates.
should simply be

rs.Edit
  change1
  change2
  change3
  ........
rs.update

 

[azhar2006]

actually you have 2 extra updates.
should simply be

rs.Edit
  change1
  change2
  change3
  ........
rs.update

Thank you dear MajP

 

[The_Doc_Man]

MajP has given you excellent examples. I will therefore take the other direction by explaining what happened.

Access and VBA like "bracketing" as a way to set apart things to be done or things to be taken together. Just like you need [] around certain syntax elements, or ## around dates, or () around function arguments, there are things in CODE that must be used to bracket things that are to be handled as a logical unit. For example, If and End If bracket code to be run under certain conditions. For Each x and Next x bracket loops.

So... if you put recordset field updates in your code, you must bracket those updates with a recordset.AddNew or recordset.Edit at the start, and a recordset.Update at the end of the sequence. If Access cannot see the correct bracketing steps, it won't do anything to the recordset - and that was the source of your error.