none
After code runs, I cannot reference the forms Recordset object anymore. Error '3420' RRS feed

  • Question

  • I have a form with multiple records in Datasheet view.

    I use Me.Recordset.Fields("Id").Value to get values from the Recordset at the current cursor position.
    I use this method very much to access Recordset data of my forms throughout my application.

    In this case I use: DoCmd.OpenForm "frmCustomerAdvertDesignRequests_Add", , , , , acDialog, Me.Recordset.Fields("Id").Value

    If I just close that Dialog, life is peachy, control returns to the Datasheet and I still have reference to the Me.Recordset. But if I fire the code on the dialog, I can't referencing the Me.Recordset of the form when control returns to the Datasheet.

    I am baffled.
    The code that fires is quite long. But basically, it opens a DAO.Workspace, DAO.Database, 2 different DAO.Recordset, makes some changes, commits or rolls back.
    Does anyone have any clues? I can try and replicate the error with as little code as possible so I can post it. But before that, perhapse this is fimiliar to someone already?
    Monday, January 30, 2017 11:15 PM

Answers

  • Well, I think I'm on the right track, but need some input. The error is happening because I'm using the DefaultWorkspace for the transaction, then I close that workspace, but Access still needs it. Which solution is best?: Using a reference to the default Workspace and just NOT CLOSING it when i'm done, just set the reference = Nothing, but don't call wks.Close? Or: Create a new workspace, which requires me to create or open a database in that workspace.

    If I have to open a database within a new workspace, how does that work on a deeper level? I mean, does it open in memory another copy of the current accde file? Does it use the same one? If it opens a new Accde file, I am using Linked tables to Azure DB and I don't save the password for the table links.
    • Marked as answer by HTHP Tuesday, January 31, 2017 11:34 PM
    Tuesday, January 31, 2017 4:38 PM
  • I could be wrong, but I suspect the problem is with these statements:

        db.Close

        wks.Close

    You didn't open either of those objects, and you shouldn't close them.  They are the objects currently being used by Access, so if you close them, you invalidate any existing recordsets derived from them.

    As I said, I could be wrong, but try it without those statements.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by HTHP Tuesday, January 31, 2017 11:34 PM
    Tuesday, January 31, 2017 5:49 PM

All replies

  • Why not inspect Me.Id instead of Me.Recordset.Fields("Id").Value ?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, January 30, 2017 11:26 PM
  • I would prefer to solve this problem if possible. Why? I use Form Recordset objects hundreds of times throughout my application. Its a pretty big app for MS Access, from my experience, with hundreds of forms, over 100 concurrent users, Azure SQL Backend. I tend to pass around Recordset object references, and clones quite a bit.

    Because it is so big, the Access App and Database is constantly being scaled, the Schema changes as I normalize or denormalize depending on the requirements. I attempt to map field names to global variables in many places, to reduce searching hundreds of code modules when I have to make these changes.

    If I can store a field name as tfnCompanyName__CustomersTABLE, then I can use Me.Recordset.Fields(tfnCompanyName__CustomersTABLE).Value in 20 forms and if I ever change that field name, I only have to change it in the one location(In theory). It really helps.



    Another good reason, is because when control returns, Alot of times I have to run a custom code to requery the form and stay on the same record, which requires reference to the Recordset
    • Edited by HTHP Monday, January 30, 2017 11:47 PM
    Monday, January 30, 2017 11:44 PM
  • Hi HTHP,

    you had mentioned that when you open dialog then you are able to fetch the value from Recordset.

    if you close the dialog then also you can refer that value.

    but if you fire a code then after that you are no longer to fetch the value.

    so why don't you try to save the value in temporary variable then fire a code.

    so after fire the code you can fetch the value from temporary variable.

    if I misunderstand something from your above description then please try to post a code that will help us to better understand the issue.

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 31, 2017 4:21 AM
    Moderator
  • Deepak, I may not have explained myself well, the point is not the value of any field(s). I need a reference to a Recordset itself.  The reason this error came up in the first place was because the Dialog is called from a Datasheet with many records and when control goes back to the Datasheet, values change on the tables that the Datasheet uses, so I need to requery it.  But I have to keep the user on the same record after the requery and the only way to do that is to use a reference to the Recordset object of the Datasheet.

    Below, if I set success = true, Skipping SaveRequestToDatabase call, I don't get the error. So I think(not sure) that SaveRequestToDatabase is causeing the issue:

    Dim success As Boolean
        success = SaveRequestToDatabase(request, listOfAttachments)
        If (success) Then
            MsgBox "Request Sent!!", vbInformation, "Request Submitted"
            Application.Forms!frmCustomerAdvertDesignRequests_Add.SetFocus
            DoCmd.RunCommand acCmdCloseWindow
        Else
            MsgBox "Something went wrong. Request could not be sent.", vbInformation, "Oops..."
        End If

    Public Function SaveRequestToDatabase(ByVal DesignRequest As clsCustomerAdvertisementDesignRequests_DTO, ByRef filePaths As String) As Boolean
    On Error GoTo Rollback_Changes
        Dim wks As DAO.Workspace
        Dim db As DAO.Database
        Dim rst1 As DAO.Recordset
        Dim sql1 As String
        Dim rst2 As DAO.Recordset
        Dim sql2 As String
        Dim NewDesignRequestId As Long
        Set wks = DBEngine(0)
        wks.BeginTrans
            Set db = wks.Databases(0)
            sql1 = "CustomerAdvertisementDesignRequests"
            Set rst1 = db.OpenRecordset(sql1, dbOpenDynaset, dbSeeChanges, dbOptimistic)
            With rst1
                .Edit
                .AddNew
                .Fields("CustomerId").Value = DesignRequest.CustomerID
                .Fields("CustomerAdvertisementId").Value = DesignRequest.CustomerAdvertisementId
                .Fields("Design_Request_Type").Value = DesignRequest.DesignRequestType
                .Fields("Subject").Value = DesignRequest.Subject
                .Fields("MessageBody").Value = DesignRequest.MessageBody
                .Fields("Reviewed_By_Csr").Value = DesignRequest.ReviewedByCsr
                .Fields("Reviewer_Feedback").Value = DesignRequest.ReviewerFeedback
                .Fields("Requested_Date").Value = Date
                .Update
                .Bookmark = .LastModified
                NewDesignRequestId = .Fields("Id").Value
            End With
            
            If Len(filePaths) > 0 Then
                Dim filePathsOfAttachments As Variant
                filePathsOfAttachments = Split(filePaths, "|")
                Dim i As Integer
                sql2 = "CustomerAdvertisementDesignRequestAttachments"
                Set rst2 = db.OpenRecordset(sql2, dbOpenDynaset, dbSeeChanges, dbOptimistic)
                With rst2
                    For i = 0 To UBound(filePathsOfAttachments)
                        .AddNew
                        .Fields("CustomerId").Value = DesignRequest.CustomerID
                        .Fields("CustomerAdvertisementDesignRequestsId").Value = NewDesignRequestId
                        .Fields("File_Name").Value = Dir(filePathsOfAttachments(i))
                        .Fields("Record_Created").Value = Date
                        modFileIO_UploadFromFile .Fields("File_Data"), .Fields("File_Size"), CStr(filePathsOfAttachments(i))
                        .Update
                    Next
                End With
            End If
        wks.CommitTrans dbForceOSFlush
        SaveRequestToDatabase = True
    GoTo Dispose_Exit
    Rollback_Changes:
        Dim x As Variant
        For Each x In DAO.Errors
            Debug.Print x.Description
        Next
        SaveRequestToDatabase = False
        wks.Rollback
    Dispose_Exit:
        '' TODO: trying to close rst2 if it wasn't initialized causes error, and using 'On Error Resume Next' returns control to error handler
        '' in calling method(not this one!), which then skips a bunch of code that needs to run.
        '' Need to figure out how to test if rst2 can be closed... or a way to skip it on error that returns control
        '' to the next line of code HERE.
        Set rst2 = Nothing
        rst1.Close
        Set rst1 = Nothing
        db.Close
        Set db = Nothing
        wks.Close
        Set wks = Nothing
    End Function

    I have a second problem in that code you can ignore the TODO as it has nothing to do with this issue I don't think.

    Tuesday, January 31, 2017 1:42 PM
  • I'm not sure how Clones work. Correct me if I'm wrong, but I expect they should use a different chunk in memory and copy the Recordset into that chunk of memory and use a completely different reference on the stack to point to this new chunk in memory? So essentially there are 2 independent objects in memory??

    With this assumption(which I may be wrong), as a test I cloned the Datasheet's Recordset into a new variable just before calling the Dialog and firing the code. Then after control returns to the Datasheet view, I attempt to set the Datasheet's Recordset to the clone. This still gives same error.

    Tuesday, January 31, 2017 2:14 PM
  • Another baffling thing I just noticed. This Datasheet is in a Subform. The Subform object itself is unbound, and is used to load any of 5 different Forms when the User clicks 1 of 5 Buttons(Orders, Ads, TvSpots, WebBanners, WebListings). The parent form is a Customer form. Anyways, its the Ads Subform I am working with here in this post. If I click Ads buton, setting child form, and force the error in the child form, then click a button to display a different child form in the Subform, then go back to Ads, I get the Error still. This is confusing because I would think by setting the SourceObject to another form would unload the Datasheet. Until I close the parent form and reopen it again, I can't go back to the Ads subform without that error.

    Shouldn't the Ads form be closed when I set Me.child0.SourceObject to another form? Why when I try to set the SourceObject back to the Ads form do I still get this "Object invalid or No longer set" error? The form loads into the Subform??
    Tuesday, January 31, 2017 2:43 PM
  • I've narrowed down the issue to the DAO.Workspace, but still don't know why its happening or how to prevent it. If I don't use the workspace in method SaveRequestToDatabase(), and I set db = CurrentDb, I don't loose reference to the Datasheet's Recordset.

    Any clue how to address this? Using workspaces to commit/rollback transactions on multiple tables is very important. Just FYI, my tables are not in the FE or even in Ms Access file, they are linked to Azure SQL Db.
    Tuesday, January 31, 2017 3:48 PM
  • Well, I think I'm on the right track, but need some input. The error is happening because I'm using the DefaultWorkspace for the transaction, then I close that workspace, but Access still needs it. Which solution is best?: Using a reference to the default Workspace and just NOT CLOSING it when i'm done, just set the reference = Nothing, but don't call wks.Close? Or: Create a new workspace, which requires me to create or open a database in that workspace.

    If I have to open a database within a new workspace, how does that work on a deeper level? I mean, does it open in memory another copy of the current accde file? Does it use the same one? If it opens a new Accde file, I am using Linked tables to Azure DB and I don't save the password for the table links.
    • Marked as answer by HTHP Tuesday, January 31, 2017 11:34 PM
    Tuesday, January 31, 2017 4:38 PM
  • I could be wrong, but I suspect the problem is with these statements:

        db.Close

        wks.Close

    You didn't open either of those objects, and you shouldn't close them.  They are the objects currently being used by Access, so if you close them, you invalidate any existing recordsets derived from them.

    As I said, I could be wrong, but try it without those statements.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by HTHP Tuesday, January 31, 2017 11:34 PM
    Tuesday, January 31, 2017 5:49 PM