Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Mar 2004
    Location
    Arizona
    Posts
    36
    Thanks
    2
    Thanked 1 Time in 1 Post

    Trouble updating a field within a recordset

    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
    Dell Precision Mobile Workstation M6500, Intel Core i7-940XM Extreme Edition, 2.13GHz, 8MB cache, 16GB RAM, 500GB HD, 1GB NVIDIA Quadro FX 3800M graphics
    Windows 7 Pro 64-bit, Office 2010 64-bit
    www.CaptainKen.us

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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.
    Regards
    John



  3. #3
    Lounger
    Join Date
    Mar 2004
    Location
    Arizona
    Posts
    36
    Thanks
    2
    Thanked 1 Time in 1 Post
    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
    Dell Precision Mobile Workstation M6500, Intel Core i7-940XM Extreme Edition, 2.13GHz, 8MB cache, 16GB RAM, 500GB HD, 1GB NVIDIA Quadro FX 3800M graphics
    Windows 7 Pro 64-bit, Office 2010 64-bit
    www.CaptainKen.us

  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,287
    Thanks
    130
    Thanked 1,154 Times in 1,063 Posts
    What value has GetSQL?

  5. #5
    Lounger
    Join Date
    Mar 2004
    Location
    Arizona
    Posts
    36
    Thanks
    2
    Thanked 1 Time in 1 Post
    GetSQL are the 197 records I need to loop through and then modify the Note field in the records within rsNote that match.
    Dell Precision Mobile Workstation M6500, Intel Core i7-940XM Extreme Edition, 2.13GHz, 8MB cache, 16GB RAM, 500GB HD, 1GB NVIDIA Quadro FX 3800M graphics
    Windows 7 Pro 64-bit, Office 2010 64-bit
    www.CaptainKen.us

  6. #6
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    222
    Thanks
    0
    Thanked 21 Times in 20 Posts
    Quote Originally Posted by CaptainKen View Post
    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!

  7. #7
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,287
    Thanks
    130
    Thanked 1,154 Times in 1,063 Posts
    Quote Originally Posted by jeremybarker View Post
    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.

  8. #8
    Lounger
    Join Date
    Mar 2004
    Location
    Arizona
    Posts
    36
    Thanks
    2
    Thanked 1 Time in 1 Post
    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
    Dell Precision Mobile Workstation M6500, Intel Core i7-940XM Extreme Edition, 2.13GHz, 8MB cache, 16GB RAM, 500GB HD, 1GB NVIDIA Quadro FX 3800M graphics
    Windows 7 Pro 64-bit, Office 2010 64-bit
    www.CaptainKen.us

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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")?
    Last edited by johnhutchison; 2012-04-04 at 17:53.
    Regards
    John



  10. #10
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,287
    Thanks
    130
    Thanked 1,154 Times in 1,063 Posts
    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?

  11. #11
    Lounger
    Join Date
    Mar 2004
    Location
    Arizona
    Posts
    36
    Thanks
    2
    Thanked 1 Time in 1 Post
    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
    Dell Precision Mobile Workstation M6500, Intel Core i7-940XM Extreme Edition, 2.13GHz, 8MB cache, 16GB RAM, 500GB HD, 1GB NVIDIA Quadro FX 3800M graphics
    Windows 7 Pro 64-bit, Office 2010 64-bit
    www.CaptainKen.us

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •