Solved

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

Posted on 2004-08-03
6
857 Views
Last Modified: 2007-11-27
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
Comment
Question by:gordonwwaugh
  • 3
  • 3
6 Comments
 
LVL 39

Expert Comment

by:stevbe
ID: 11710666
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
 

Author Comment

by:gordonwwaugh
ID: 11710757
Thanks for the cool shortcut for the combo box.

Unfortunately, the requery doesn't fix the error.
0
 
LVL 39

Accepted Solution

by:
stevbe earned 125 total points
ID: 11711514
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:gordonwwaugh
ID: 11712368
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
 
LVL 39

Expert Comment

by:stevbe
ID: 11714168
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
 

Author Comment

by:gordonwwaugh
ID: 11717874
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now