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
853 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

758 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

18 Experts available now in Live!

Get 1:1 Help Now