error in code (1 Viewer)

azhar2006

Registered User.
Local time
Yesterday, 17:13
Joined
Feb 8, 2012
Messages
180
Good evening, guys.
Is it possible to fix the error in this code?
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

Immoderate Moderator
Staff member
Local time
Yesterday, 19:13
Joined
Feb 28, 2001
Messages
22,555
@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

Active member
Local time
Yesterday, 17:13
Joined
Aug 18, 2021
Messages
151
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

Registered User.
Local time
Yesterday, 17:13
Joined
Feb 8, 2012
Messages
180
@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
 

Attachments

  • 33.JPG
    33.JPG
    65.2 KB · Views: 208

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 20:13
Joined
May 21, 2018
Messages
6,270
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

Code:
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

You've got your good things, and you've got mine.
Local time
Yesterday, 20:13
Joined
May 21, 2018
Messages
6,270
actually you have 2 extra updates.
should simply be

Code:
rs.Edit
  change1
  change2
  change3
  ........
rs.update
 

azhar2006

Registered User.
Local time
Yesterday, 17:13
Joined
Feb 8, 2012
Messages
180
actually you have 2 extra updates.
should simply be

Code:
rs.Edit
  change1
  change2
  change3
  ........
rs.update
Thank you dear MajP 🌺
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:13
Joined
Feb 28, 2001
Messages
22,555
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.
 

Users who are viewing this thread

Top Bottom