Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 925
  • Last Modified:

Error 3420 (Object invalid ...) when using combo box to go to another record (after opening and closing other recordsets)

I get "Run-Time Error '3420': Object invalid or no longer set" when I make any selection in the form's combo box. However, this only happens AFTER I click a command button on the form that calls a procedure.

Here are the details.

I am using Access 2002. References include, among others, ADO 2.1 library and DAO 3.6 library. All tables are linked tables (in another Access 2002 database).

The combo box (cboSessionId) on the form is used to go to a different record in the form's recordsource (a query based on table tblSessions). Here's the code that does that.

Private Sub cboSessionId_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[SessionId] = " & Str(Nz(Me![cboSessionId], 0))
    If Not rs.EOF Then
        Me.Bookmark = rs.Bookmark
    Else
        MsgBox "Session " & Me.cboSessionId & " not found."
    End If
    rs.Close
End Sub


The combo box works fine (goes to the selected record) UNTIL I click the command button on the form. Clicking the button calls a procedure (PickPeerPairs) stored in a class module. The procedure is over 900 lines long, so I'll include just the relevant portions below.

Sub PickPeerPairs(strCriteria As String)

    Dim strQuery As String
    Dim rst As ADODB.Recordset
    Dim wrk As DAO.Workspace
    Dim dbExternal As DAO.Database
    Dim rstLink As DAO.Recordset
       
    '===== Open tblSessions =====
   
    Set rst = New ADODB.Recordset
   
    strQuery = _
        " SELECT tblSoldiers.ID" & _
        " FROM tblSessions"
   
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.LockType = adLockReadOnly
    rst.Open strQuery, Options:=adCmdText
   
    lngNumRecords = rst.RecordCount
   
    If lngNumRecords < 1 Then
        rst.Close
        Set rst = Nothing
        Exit Sub
    End If
    ...
    rst.Close
    Set rst = Nothing
   
   
    '=== Open table tblRateeByRater =======
   
    'Because Seek doesn't work with linked tables, you must run the
    'seek from the database containing the linked table. To do so,
    'create a DAO workspace, open the other database in it, and open
    'the table within that database.
   
    Set wrk = DBEngine.Workspaces(0)
    Set dbExternal = _
        wrk.OpenDatabase(acbGetLinkPath("tblRateeByRater"), False, False, "")
       
    'Open the ratee by rater table so it's ready to search
    Set rstLink = dbExternal.OpenRecordset("tblRateeByRater", dbOpenTable)
    rstLink.Index = "PrimaryKey"
    ...
     rstLink.Seek "=", lngRateeId, lngRaterId
     If rstLink.NoMatch Then
        ...
     End If
           
    rstLink.Close
    Set rstLink = Nothing
    dbExternal.Close
    Set dbExternal = Nothing
    wrk.Close
    Set wrk = Nothing
...
    '====Write the new final pairs back to the RaterByRatee table ========    
    'Because Seek doesn't work with linked tables, you must run the
    'seek from the database containing the linked table. To do so,
    'create a DAO workspace, open the other database in it, and open
    'the table within that database.
   
    Set wrk = DBEngine.Workspaces(0)
    Set dbExternal = _
        wrk.OpenDatabase(acbGetLinkPath("tblRateeByRater"), False, False, "")
       
    'Open the ratee by rater table so it's ready to search
    Set rstLink = dbExternal.OpenRecordset("tblRateeByRater", dbOpenTable)
    rstLink.Index = "PrimaryKey"
    ...
    rstLink.Seek "=", lngRateeId, lngRaterId
    If Not rstLink.NoMatch Then
        ...
    End If
    ...
    rstLink.Close
    Set rstLink = Nothing
    dbExternal.Close
    Set dbExternal = Nothing
    wrk.Close
    Set wrk = Nothing
End Sub
0
gordonwwaugh
Asked:
gordonwwaugh
  • 3
  • 3
1 Solution
 
stevbeCommented:
Here is a shortcut to your combo afterupdate

Private Sub cboSessionId_AfterUpdate()
    Me.Recordset.FindFirst "[SessionId] = " & Me.cboSessionId.Value & vbNullString
    If Me.Recordset.NoMatch = True Then
        MsgBox "Session " & Me.cboSessionId.Value & " not found."
    End If
End Sub

I think if you are messing with the form's recordset you should probably reset the combobox also at the end of your procedure ...

Me.cboSessionID.Requery

Steve
0
 
gordonwwaughAuthor Commented:
Thanks for the cool shortcut for the combo box.

Unfortunately, the requery doesn't fix the error.
0
 
stevbeCommented:
It sounds like th error is on this line ...

Set rs = Me.Recordset.Clone

If this is so then ... are you messing with the recordset the form is bound to in your procedure?
You may need to requery the form itself. Don't forget to capture the PK of the current record before your procedure gets run because requering will reset to the first record so you will need to do a Me.Recordset.FindFirst to get back to where you were.


Looking at your code again ... I am not sure but you may be causing the probelm because you are closing the workgroup the current database it part of so you may be goofing the entire connection.

wrk.Close

comment out that line but leave Set wrk = Nothing is your cleanup code.

Steve
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gordonwwaughAuthor Commented:
I still get the same error after removing "wrk.Close" and adding "me.Requery" on the form right after the line that calls the procedure. I tried three times: once removing wrk.Close, once adding me.Requery, and once doing both things.

You are correct in assuming that "Set rs = Me.Recordset.Clone triggers the error.

I have also tried changing the combo box's AfterUpdate event code to DAO. In that case, there is no problem with the line "set rs = me.recordsetclone". Rather the subsequent line, "rs.MoveFirst",  triggers the error.

You asked whether the called procedure messes with the form's recordset. As shown in my original code, the procedure does refer to the same table (tblSessions) as the main table (tblSessions) in the form's underlying recordsource query. The procedure merely reads some records in the query.

- Gord -
0
 
stevbeCommented:
I am trying to narrow things down ... so if you don't process your procedure you can many more that 1 naivagtion using cboSessionID right?

If this is not the case I would almost think it would be time to try a /decompile and then import into a new database.

Is it the Recordset.FindFirst itself or perhaps there is an issue with cboSessionID? Does the code work if you hardcode a value in the findfirst rather than use the cbo value?

Is cboSessionID control on a subform and the parent form's recordset might be changed or navigated to a record that has no children. I ask because I have done this to myself in the past and I was getting the same error but the circumstances were slightly different.

Steve
0
 
gordonwwaughAuthor Commented:
Oops. I just realized I have not one, but two, wrk.Close statements. I had only removed the first one. When I remove both of them, the error disappears.

Sorry about that.

And thanks so much! You saved my bacon!

This was my first adventure creating a workspace.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now