• Trouble updating a field within a recordset

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Trouble updating a field within a recordset

    Author
    Topic
    #482507

    I’m trying to use the rsRen as the recordset that needs to be modified, so my Where clause if you will.

    rsNote is the table with the Notes memo field that requires a strNote to be added to the existing contents.

    What is happening is it is modifying only a single record, and one that is not within the rsRen recordset. The way it is modifying the record is also wrong as it is adding the strNote to this single record equal to the number of records in the rsNote recordset.

    If I use !Notes = strNote instead, then it clears the Notes field and replaces it with the strNote.

    Code:
    Private Sub cmdTest_Click()
    
    Dim db As Database
    Dim rsRen As Recordset
    Dim rsNote As Recordset
    Dim strSQL_Ren As String
    Dim strSQL_Note As String
    Dim strNote As String
    
    Set db = CurrentDb
    
            strNote = “testing 3”
    
            strSQL_Ren = GetSQL
            Set rsRen = db.OpenRecordset(strSQL_Ren)
            rsRen.MoveLast
            rsRen.MoveFirst
            MsgBox “There are: ” & rsRen.RecordCount
    
            strSQL_Note = “Select * From tblCompanyContacts”
            Set rsNote = Nothing
            Set rsNote = db.OpenRecordset(strSQL_Note)
            rsNote.MoveLast
            rsNote.MoveFirst
            MsgBox “There are: ” & rsNote.RecordCount
            
            Do While Not rsRen.EOF
                        With rsNote
                            .MoveLast
                            .MoveFirst
                            Dim strOldNote As String
                            strOldNote = rsNote!Notes
                            .Edit
                            !Notes = strNote & strOldNote
                            .Update
                            Debug.Print strNote, ContactName
                        End With
            rsRen.MoveNext
            Loop
    
    End Sub
    

    Thank you,

    Ken

    Viewing 6 reply threads
    Author
    Replies
    • #1327304

      You move back to the first record each time you go through the loop.
      You don’t need the .movelast and .movefirst within the Do Loop.
      The Dim strOldNote as string line should occur before the loop starts so it only happens once.

    • #1327325

      Thank you John, that made huge difference, but completely there.

      The issue now is that GetSQL has a record set of 197 and rsNote has recordset of 1116. The code is modifying 197 records, but they are random and therefore it appears they are outside of the GetSQL recordset.

      Also I can’t figure out where to put the Debug.Print as it is only showing one record.

      Code:
      Private Sub cmdTest_Click()
      
      Dim db As Database
      Dim rsRen As Recordset
      Dim rsNote As Recordset
      Dim strSQL_Ren As String
      Dim strSQL_Note As String
      Dim strNote As String
      Dim strOldNote As String
                              
      Set db = CurrentDb
      
              strNote = “testing 9”
      
              strSQL_Ren = GetSQL
              Set rsRen = db.OpenRecordset(strSQL_Ren)
              rsRen.MoveLast
              rsRen.MoveFirst
              MsgBox “There are: ” & rsRen.RecordCount
      
              strSQL_Note = “Select * From tblCompanyContacts”
              Set rsNote = Nothing
              Set rsNote = db.OpenRecordset(strSQL_Note)
              rsNote.MoveLast
              rsNote.MoveFirst
              MsgBox “There are: ” & rsNote.RecordCount
      
              Do While Not rsRen.EOF
                          With rsNote
                              strOldNote = rsNote!Notes
                              .Edit
                              !Notes = strNote & strOldNote
                              .Update
                              .MoveNext
                              Debug.Print strNote, ContactName
                          End With
              rsRen.MoveNext
              Loop
              
          rsNote.Close: rsRen.Close: db.Close
          Set rsNote = Nothing: Set rsRen = Nothing: Set db = Nothing
          
      End Sub
      
    • #1327334

      What value has GetSQL?

    • #1327349

      GetSQL are the 197 records I need to loop through and then modify the Note field in the records within rsNote that match.

      • #1327353

        GetSQL are the 197 records I need to loop through and then modify the Note field in the records within rsNote that match.

        I think what ruirib meant was that early on you execute this statement “strSQL_Ren = GetSQL” but there is no indication of what value the variable GetSQL holds at this point!

        • #1327364

          I think what ruirib meant was that early on you execute this statement “strSQL_Ren = GetSQL” but there is no indication of what value the variable GetSQL holds at this point!

          Yes, that was precisely the point of my question.

          • #1327386

            Sorry guys, here’s the GetSQL.

            strSQL = “SELECT tblWarrantyToSend.Include, tblCompanyContacts.ContactName, [tblClients/Prospects].CompName, tblCompanyContacts.ContactID, tblCompanyContacts.Type, tblSubContracts.WarrantyType, tblSubContracts.Status, tblKeyWatcher.KwModelType, tblMainContracts.SN, tblMainContracts.LocationDesc, tblCompanyContacts.Email, tblSubContracts.PrevExpire ” & _
            “FROM ([tblClients/Prospects] INNER JOIN (((tblWarrantyToSend INNER JOIN tblCompanyContacts ON tblWarrantyToSend.ContactID = tblCompanyContacts.ContactID) INNER JOIN tblMainContracts ON tblWarrantyToSend.MainContractId = tblMainContracts.MainContractID) INNER JOIN tblSubContracts ON tblWarrantyToSend.SubContractId = tblSubContracts.SubContractID) ON [tblClients/Prospects].CompID = tblCompanyContacts.CompID) INNER JOIN tblKeyWatcher ON tblMainContracts.MainContractID = tblKeyWatcher.MainContractID ” & _
            ” Where (((tblWarrantyToSend.Archive)=No)) AND ” & strW & _
            ” Order By ” & strOrder

    • #1327393

      The SQL used for rsRen is based on some SQL that joins a whole pile of fields. When you join tables a record is normally returned only if there is a match on both sides of the join. It seems that you have nulls in some of the fields used for the joins.
      Can you build a query in the query designer that select the records you want to use for rsRen? In the query designer you can double click the join line between tables to change the join type.

      You have:

      Debug.Print strNote, ContactName

      StrNote is only assigned a value before the loop starts. What value do you want it to have at this point?
      What type of thing is ContactName? It appears to be an indeclared variable? What do you want it to be? do you means RSRen(“contactName”)?

    • #1327421

      I also fail to see any relation between the two recordsets. What sense does it make to loop over two unrelated recordsets? Or did I miss something in your code that establishes this relationship?

    • #1327528

      Good news, I got it to work and then some!

      GetSQL:
      “SELECT tblWarrantyToSend.Include, tblCompanyContacts.ContactName, [tblClients/Prospects].CompName, tblCompanyContacts.ContactID, tblCompanyContacts.Type, tblSubContracts.WarrantyType, tblSubContracts.Status, tblMainContracts.Model, tblMainContracts.SN, tblMainContracts.LocationDesc, tblCompanyContacts.Email, tblSubContracts.PrevExpire, tblSubContracts.SubContractID, tblCompanyContacts.Notes ” & _
      “FROM [tblClients/Prospects] INNER JOIN (((tblWarrantyToSend INNER JOIN tblCompanyContacts ON tblWarrantyToSend.ContactID = tblCompanyContacts.ContactID) INNER JOIN tblMainContracts ON tblWarrantyToSend.MainContractId = tblMainContracts.MainContractID) INNER JOIN tblSubContracts ON tblWarrantyToSend.SubContractId = tblSubContracts.SubContractID) ON [tblClients/Prospects].CompID = tblCompanyContacts.CompID ” & _
      “WHERE (((tblMainContracts.Model) Like ‘k*’) AND ((tblWarrantyToSend.Archive)=No)) AND ” & strW & _
      ” Order By ” & strOrder

      Code:
          Dim db As Database
          Dim rsRen As Recordset
          Dim strSQL_Ren As String
          Dim strNote As String
          Dim strCurrentContactID As String
      
          Set db = CurrentDb
      
          strSQL_Ren = GetSQL
          Set rsRen = db.OpenRecordset(strSQL_Ren)
          
          ‘SET LOOP PARAMETERS
          strNote = Format(Date, “mm-dd-yy”) & ” The following warranty email(s) were sent:” & vbCrLf
          strCurrentContactID = “XXX”
          
          With rsRen
              .MoveFirst
              Do While Not .EOF
                  ‘HANDLE RECORD NOTE INSERTION
                  If strCurrentContactID  !ContactID Then
                     If strCurrentContactID  “XXX” Then
                          .MovePrevious
                          .Edit
                          !Notes = strNote & !Notes
                          .Update
                          .MoveNext
                          strNote = Format(Date, “mm-dd-yy”) & ” The following warranty email(s) were sent:” & vbCrLf
                      End If
                       strNote = strNote & ” – ” & !Model & ” – ” & !SN & ” – ” & !LocationDesc & ” – ” & !WarrantyType & vbCrLf
                  Else
                       strNote = strNote & ” – ” & !Model & ” – ” & !SN & ” – ” & !LocationDesc & ” – ” & !WarrantyType & vbCrLf
                  End If
                  strCurrentContactID = !ContactID
                  .MoveNext
              Loop
                  
              ‘UPDATE LAST RECORD NOTE
              .MoveLast
              strNote = strNote & ” – ” & !Model & ” – ” & !SN & ” – ” & !LocationDesc & ” – ” & !WarrantyType & vbCrLf
              .Edit
              !Notes = strNote & vbCrLf & !Notes
              .Update
              .Close
          End With
      
          MsgBox “The process has been completed successfully!”
      
          db.Close
          Set rsRen = Nothing: Set db = Nothing
      

      Thanks for all the help!

      Ken

    Viewing 6 reply threads
    Reply To: Trouble updating a field within a recordset

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: