Recordset2 (attachments) "no longer set" after Updating outer Recordset (1 Viewer)

Isaac

Lifelong Learner
Local time
Today, 10:55
Joined
Mar 14, 2017
Messages
8,738
Below is my code. I am getting "object invalid or no longer set" on the line rsInner.MoveNext
Despite the fact that I have:
  1. opened an outer recordset as Recordset
  2. set a fld2 variable as outer recordset's Attachment field
  3. set an inner Recordset2 as fld2.value
.... It appears that the inner one loses its reference [due to me Updating the outer one?]

Is this normal/expected? Is there any way around it other than I know I can just use a Currentdb.Execute to make the update, but am curious if there is anything else about this situation I should know.

Code:
Sub UpdateTaxonomyFilenames()
'Purpose: loop through outer recordset.  loop through each one's Attachments
'Figure out which one's [short] filename is not equal to any one of 3 existing outer recordset's columns, AND exists in a certain folder,
'and then make an update to the outer recordset

Dim rsOuter As DAO.Recordset, db As DAO.Database, rsInner As DAO.Recordset2, fld As DAO.Field2
Dim strFileName As String
Set db = CurrentDb

Set rsOuter = db.OpenRecordset("select * from [distribution portal]")

Do Until rsOuter.EOF = True
    Set fld = rsOuter.Fields("Attachments")
    Set rsInner = fld.Value
    Do Until rsInner.EOF = True
        strFileName = rsInner.Fields("Filename").Value
        strFileName = Right(strFileName, Len(strFileName) - InStrRev(strFileName, "/"))
      
        'if it's not ccepfilename, and not filename_useruploaded1, and not *useruploaded2, and exists in that folder...then it's our originally uploaded taxonomy file
        If (strFileName <> rsOuter.Fields("CCEPFilename").Value) And _
            (strFileName <> rsOuter.Fields("Filename_UserUploaded_1").Value) And _
            (strFileName <> rsOuter.Fields("Filename_UserUploaded_2").Value) And _
            (Dir("\\server.com\folder\folder\folder\folder\folder\folder\folder\" & strFileName) <> "") Then
                'then it's the originally uploaded taxonomy file:
                rsOuter.Edit
                rsOuter.Fields("Filename_TaxonomyOriginal").Value = strFileName
                rsOuter.Update
        End If
      
        rsInner.MoveNext
    Loop
    rsInner.Close
    Set rsInner = Nothing
  
    rsOuter.MoveNext
Loop

rsOuter.Close
Set rsOuter = Nothing


End Sub

1613689247255.png
 
Solution
maybe Close rsInner before making update to the parent recordset.
then re-open it:
Code:
Sub UpdateTaxonomyFilenames()
'Purpose: loop through outer recordset.  loop through each one's Attachments
'Figure out which one's [short] filename is not equal to any one of 3 existing outer recordset's columns, AND exists in a certain folder,
'and then make an update to the outer recordset

Dim rsOuter As DAO.Recordset2, db As DAO.Database, rsInner As DAO.Recordset2, fld As DAO.Field2
Dim strFileName As String, strFileNameOrig As String
Set db = CurrentDb

Set rsOuter = db.OpenRecordset("select * from [distribution portal]")

Do Until rsOuter.EOF = True
    Set fld = rsOuter.Fields("Attachments")
    Set rsInner = fld.Value
    Do Until...

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:55
Joined
May 7, 2009
Messages
19,169
maybe Close rsInner before making update to the parent recordset.
then re-open it:
Code:
Sub UpdateTaxonomyFilenames()
'Purpose: loop through outer recordset.  loop through each one's Attachments
'Figure out which one's [short] filename is not equal to any one of 3 existing outer recordset's columns, AND exists in a certain folder,
'and then make an update to the outer recordset

Dim rsOuter As DAO.Recordset2, db As DAO.Database, rsInner As DAO.Recordset2, fld As DAO.Field2
Dim strFileName As String, strFileNameOrig As String
Set db = CurrentDb

Set rsOuter = db.OpenRecordset("select * from [distribution portal]")

Do Until rsOuter.EOF = True
    Set fld = rsOuter.Fields("Attachments")
    Set rsInner = fld.Value
    Do Until rsInner.EOF = True
        strFileNameOrig = rsInner.Fields("Filename").Value
        strFileName = Right(strFileNameOrig, Len(strFileNameOrig) - InStrRev(strFileNameOrig, "/"))
     
        'if it's not ccepfilename, and not filename_useruploaded1, and not *useruploaded2, and exists in that folder...then it's our originally uploaded taxonomy file
        If (strFileName <> rsOuter.Fields("CCEPFilename").Value) And _
            (strFileName <> rsOuter.Fields("Filename_UserUploaded_1").Value) And _
            (strFileName <> rsOuter.Fields("Filename_UserUploaded_2").Value) And _
            (Dir("\\server.com\folder\folder\folder\folder\folder\folder\folder\" & strFileName) <> "") Then
               
                'arnelgp
                'close first rsInner
                Set rsInner = Nothing
               
                'then it's the originally uploaded taxonomy file:
                rsOuter.Edit
                rsOuter.Fields("Filename_TaxonomyOriginal").Value = strFileName
                rsOuter.Update
               
                'arnelgp
                're-open rsinner
               Set rsInner = rsOuter.Fields("Attachments").Value
               rsInner.FindFirst "FileName = '" & strFileNameOrig & "'"
        End If
     
        rsInner.MoveNext
    Loop
    'rsInner.Close
    Set rsInner = Nothing

    rsOuter.MoveNext
Loop

rsOuter.Close
Set rsOuter = Nothing


End Sub
also make sure both recordset are of Recordset2 type.
 
Solution

Isaac

Lifelong Learner
Local time
Today, 10:55
Joined
Mar 14, 2017
Messages
8,738
edit .. I have bigger fish to fry now. Despite that I am using the same type of variable and setting as I do elsewhere, it seems that no matter what I do, rsInner only ends up having 1 item in it........despite there being about 4-5 attachments on each outer record.

Must be doing something wrong...might be time to log off for the day and get a cup of coffee. I solved the first problem by using Currentdb.Execute and not Updating the outer recordset at all, but now have this more important problem
 

Ranman256

Well-known member
Local time
Today, 13:55
Joined
Apr 9, 2015
Messages
4,339
i do basically the same thing but with a WHILE-WEND loop. (it works)

Code:
With rst
   While Not .EOF
         Set rstChild = rst.Fields(0).Value     'get the attachement (aka another rst)
         sName = rst.Fields("Name").Value & ""

           .movenext
  wend
end with
 

Isaac

Lifelong Learner
Local time
Today, 10:55
Joined
Mar 14, 2017
Messages
8,738
Interesting. I mean it looks about like mine. The difference would be if, you expected multiple records inside rstChild (as I do), and needed to go through them, but my rstChild is acting like it always only has one record/attachment.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:55
Joined
Oct 29, 2018
Messages
21,358
Hi. I'm not sure what your code is trying to accomplish, but I think you're right that by issuing the Update on the parent record, you are also closing the child recordset.

Can you tell us what your code is supposed to be doing?
 

Isaac

Lifelong Learner
Local time
Today, 10:55
Joined
Mar 14, 2017
Messages
8,738
Can you tell us what your code is supposed to be doing?
Loop through all records in the outer recordset. (Each of these records has 3-5 attachments in the multi valued field Attachments ... which is actually a linked Sharepoint list). While on each record, open an "inner" recordset of the attachments themselves. Read each attachment's filename (has to be shortened from something like http:/url/url/url/filename.xlsb to filename.xlsb). "filename.xlsb" is assigned to the variable, strFileName. if strFilename is not the same as outer recordset's column CCEPFilename, and also not the same as 2 other columns, and also exists in a folder (using a DIR check), then: Update a particular column on the outer recordset to be the value of strFileName.

Basically I have a Sharepoint list with attachments. Out of these 3-5 attachments per record, I've successfully assigned the name of the attached file to a particular column value......but one attachment (on each record) remains to be assigned and stored. I have special logic (as explained) to determine which of the attachments will be used to assign the value.

As mentioned, everything is working now (given that I switched to currentdb.execute to make the update on the outer recordset, rather than recordset.update).........Except for the inner/child recordset (the attachments) is showing up as only 1 recordcount, rather than looping through all attachments.....there are 3-5 on each one.

It puzzled me a lot, because I use almost the exact same code in another scenario. But I have a feeling I'm staring right at some dumb mistake, not seeing it.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:55
Joined
Oct 29, 2018
Messages
21,358
Hi. Thanks for the additional information. Was the other scenario also involving an Attachment field? If not, then that's the difference.
 

Isaac

Lifelong Learner
Local time
Today, 10:55
Joined
Mar 14, 2017
Messages
8,738
Yes, the other scenario is the exact same outer list, in fact, and the same Attachments field
 

Isaac

Lifelong Learner
Local time
Today, 10:55
Joined
Mar 14, 2017
Messages
8,738
I think it has to be something about how I'm setting the child recordset to the dao.fld2, or how I'm setting the fld2 to the rsOuter(Attachments)...something along those lines?

I'll examine it more for any possible differences and post back..
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:55
Joined
Oct 29, 2018
Messages
21,358
I think it has to be something about how I'm setting the child recordset to the dao.fld2, or how I'm setting the fld2 to the rsOuter(Attachments)...something along those lines?

I'll examine it more for any possible differences and post back..
In case it might shed some light, you might take a look at my MVF and Attachment demo from my website. Good luck!
 

Isaac

Lifelong Learner
Local time
Today, 10:55
Joined
Mar 14, 2017
Messages
8,738
Well, very interesting. You were right, @arnelgp , in that, when I declared the outer recordset also as Recordset2, the whole thing worked fine. Thanks for that!

Naturally, I then went to examine my "other code" - the one that was working fine. As mentioned, though, in there, I loop through an outer recordset which is only declared as a Recordset (not Recordset2) - and that code works fine. Very weird. The only possibility I can see that's different is, the way I set that outer recordset is by opening nothing except [ID] and [Attachments]. Apparently, that made some kind of a difference!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:55
Joined
May 21, 2018
Messages
8,463
To me, the code line
Set rsInner = fld.Value
looks very sus
@Cronk
May look strange, but that is how you return the child recordset of a MVF/attachment

Code:
   ' Instantiate the child recordset.
   Set rsPictures = rsEmployees.Fields("Pictures").Value
 

Users who are viewing this thread

Top Bottom