Error 3420: Object Invalid or No Longer Set

OK, so I'm not the smartest cookie in the jar and sometimes can't let go.  I have a piece of code that had been working for years that just started generating the error recently.

Dim rs2 As Recordset

Set rs2 = Me!sfrmReviewQuote.Form.RecordsetClone
strRecordCriteria = ("[LOT_NUMBER] = '" & "NEW" & "'")
rs2.FindFirst strRecordCriteria     <--------

(sfrmReviewQuote is based upon query qryQuoteLineItems)

The fist time this code is run it works fine.  The subsequent code adds records to a copule of tables and opens a Purchase order form.  Once the PO form is closed focus is returned to this sales order form.  If the user attempts to generate another PO for additional product the error occurs here

rs2.FindFirst strRecordCriteria

I've worked around the problem by

Dim qdf2 As DAO.QueryDef

Set qdf2 = db.QueryDefs("qryQuoteLineItems")
qdf2.Parameters("[forms]![frmSalesLineItems]![txtQuoteNumber]") = Me!txtQuoteNumber
Set rs2 = qdf2.OpenRecordset()

but I really want to know why the original is now failing.  I've been looking through code for 2 days trying to find anything I have changed (nothing).  A KB search returns one article describing how this error is generated by

Set rs = CurrentDb.OpenRecordset("tblName")

instead of

Set db = CurrentDb
Set rs = db.OpenRecordset("tblName")

but do not have any instances in my project.  Also, I've seen a suggestion that closing the default workspace variable while a RecordsetClone object is open can cause this error but none of the subsequent code (in any of the forms, functions or procedures) sets/closes a workspace variable.

Anyone experience this particular error?  Can anyone shed some light on using RecordsetClone to assign a recordset?

FYI - the sales order form remains open the entire time; the object variables are closed at the end of the procedure where the problem code exists, i.e.  first time through the QueryDef, recordset and database object variables are closed.

Thanks in Advance,
OM Gang
LVL 28
omgangIT ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jadedataMS Access Systems CreatorCommented:
Hi omgang,
  I don't see it right off here, but the error says that some object you are setting is being "unset" with code that runs subsequently.
  Follow your code carefully through the process.  Note the "scope" of the variable that is raising this error.

regards
-j-
0
Rey Obrero (Capricorn1)Commented:
try this
Tools>Database Utilities>Compact and Repair ...

then do a decompile

http://www.granite.ab.ca/access/decompile.htm
0
omgangIT ManagerAuthor Commented:
That's what I've been looking for.  The procedure is a command button Click event so the scope for rs is limited to that procedure.  There are other object variables in the procedure that that continue to work fine - the problem is with the RecordsetClone.  Here's the procedure in it's entriety (sorry for the length - I'm planning on moving some of it to a function call).

Private Sub cmdPO_Click()
On Error GoTo Err_cmdPO_Click

    Dim db As Database
    Dim qdf As DAO.QueryDef, qdf2 As DAO.QueryDef
    Dim rs As Recordset, rs2 As Recordset, rs3 As Recordset
    Dim Response As Integer, intRecordCount As Integer
    Dim strMsg As String, txtSuffix As String, PONum As String
    Dim strRecordCriteria As String
    Dim Existing As Variant
   
        'make sure this is a pending sale
    If Me![cboStatus] <> "Pending" Then
        strMsg = "Product can only be ordered for 'Pending' sales orders"
        MsgBox strMsg, , "Action not allowed"
        GoTo Exit_cmdPO_Click
    End If
   
        'make sure user has marked items to be purchased
        'function call - this module
    If GoToPO = "No" Then
        strMsg = "You must select the items you wish to purchase by entering 'NEW'"
        strMsg = strMsg & vbCrLf & "into the Lot# field"
        MsgBox strMsg, vbInformation, "No Items Selected"
        GoTo Exit_cmdPO_Click
    End If

        'added routine 10/16/03
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblSuffix")
   
    rs.MoveFirst
    Do Until rs.EOF
        txtSuffix = rs![PO_Suffix]
        PONum = "P" & Right(Me![txtSaleNumber], 7) & txtSuffix
                    'look to see if a PO has already been created
        Existing = DLookup("[PO_STATUS]", "tblPODescription", "[PO_NUMBER] = '" _
                & PONum & "'")
        If Not (IsNull(Existing)) Then
            rs.MoveNext
        Else
            Exit Do
        End If
    Loop
   
        'if no existing PO, create one for the line items marked 'New'
    If IsNull(Existing) Then
            'inform user that colors need to be selected prior to PO
        strMsg = "Colors for the items you are purchasing should be entered on " _
                & "this sales"
        strMsg = strMsg & vbCrLf & "form prior to generating a PO.  If necessary, " _
                & "choose 'No' at the next"
        strMsg = strMsg & vbCrLf & "message box to return to the form and enter colors."
   
        MsgBox strMsg, vbInformation, "Enter Colors First"
   
            'ask user to confirm
        strMsg = "You are about to create a Purchase Order for all line items with"
        strMsg = strMsg & vbCrLf & "'NEW' in the Lot field.  Choose 'Yes' to continue, " _
                & "'No' to cancel."
        Response = MsgBox(strMsg, vbYesNo, "Write Purchase Order?")
        If Response = vbNo Then
            GoTo Exit_cmdPO_Click
        End If
       
            'check for duplicate line items, advise user if any are found


        'Set rs2 = Me!sfrmReviewQuote.Form.RecordsetClone                    <------Here's the line that was causing the prob.


        Set qdf2 = db.QueryDefs("qryQuoteLineItems")
        qdf2.Parameters("[forms]![frmSalesLineItems]![txtQuoteNumber]") = Me!txtQuoteNumber
        Set rs2 = qdf2.OpenRecordset()
        Set qdf = db.QueryDefs("qrySalesLineItemsFindDuplicates")
        qdf.Parameters("[Forms]![frmSalesLineItems]![txtQuoteNumber]") = Me![txtQuoteNumber]
        Set rs3 = qdf.OpenRecordset()
       
            'count the number of line items with NEW in the lot number field
        strRecordCriteria = ("[LOT_NUMBER] = '" & "NEW" & "'")
        intRecordCount = 0
        rs2.FindFirst strRecordCriteria
        Do Until rs2.NoMatch
        intRecordCount = intRecordCount + 1
        rs2.FindNext strRecordCriteria
        Loop
       
        rs3.MoveLast
       
            'compare to the number of records returned by query
            '(qry will return more records if there are duplicate line items)
        If intRecordCount <> rs3.RecordCount Then
            strMsg = "A PO cannot be generated because of duplicate line items on your"
            strMsg = strMsg & vbCrLf & "order.  Look for line items with identical part " _
                    & "numbers"
            strMsg = strMsg & vbCrLf & "and identical color fields.  You can do either:"
            strMsg = strMsg & vbCrLf & "change the part number for one of the duplicate " _
                    & "items, Or"
            strMsg = strMsg & vbCrLf & "change one, or both, of the color fields for one " _
                    & "of the"
            strMsg = strMsg & vbCrLf & "duplicate line items."
            MsgBox strMsg, vbOKOnly, "Duplicate Line Items"
            GoTo Exit_cmdPO_Click
        End If
       
            'assign value to control on form for use in queries
        Me![txtPONum] = PONum
       
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "qrySaleCreateBid"          'create bid record
        DoCmd.OpenQuery "qrySaleCreatePO"           'create PO record
        DoCmd.OpenQuery "qrySaleAppendPOLineItems"  'add PO line items
        DoCmd.OpenQuery "qrySaleAppendInventory"    'add inventory line items
        DoCmd.OpenQuery "qrySaleUpdateLotNum"       'update lot# and colors on frmSalesLineItems
        DoCmd.OpenQuery "qryLotsGroup"              'update tblLotsTemp
        DoCmd.OpenQuery "qryLotsGroup_sale"         'update tblLotsTemp_sale
        DoCmd.SetWarnings True
       
    End If
   
        'open PO form to correct record
    DoCmd.OpenForm "frmPODesc", , , "[PO_NUMBER] = '" & PONum & "'"
    Forms![frmPODesc]![txtVendorNumber].SetFocus
   
        'instruct user to choose vendor and enter pricing
    strMsg = "You will need to select a Vendor for this PO and also enter the unit"
    strMsg = strMsg & vbCrLf & "costs for the items you are purchasing"
   
    MsgBox strMsg, , "Enter Vendor, Set Pricing"
   
Exit_cmdPO_Click:
        'clear object variables
    If Not rs Is Nothing Then rs.Close
        Set rs = Nothing
    If Not rs2 Is Nothing Then rs2.Close
        Set rs2 = Nothing
    If Not rs3 Is Nothing Then rs3.Close
        Set rs3 = Nothing
    If Not qdf Is Nothing Then qdf.Close
        Set qdf = Nothing
    If Not qdf2 Is Nothing Then qdf2.Close
        Set qdf2 = Nothing
    If Not db Is Nothing Then db.Close
        Set db = Nothing
    Exit Sub
   
Err_cmdPO_Click:
    MsgBox Err.Number & ", " & Err.Description, , "Error"
    Resume Exit_cmdPO_Click

End Sub




As I mentioned, this procedure is now working correctly again with the changes you see.
I also forgot to mention - the problem was first reported by a user.  Users access the program via Citrix on a Win2k Server.  I checked it out on my development machine, i.e. not version on the server, and got the same error (Win2k Pro) so it doesn't seem realted to a particular machine.
OM Gang
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

omgangIT ManagerAuthor Commented:
Cap, thanks for the suggestion.  No joy though.  Database file is much smaller and loads faster but still generates the 3420 error when I switch back to my original code.  It definitely seems to be related to the RecordsetClone ref being lost - although this code has been in place for two years now and has worked this way until recently.  The rs object variable is cleared at the end of the procedure and then redeclared and re-assigned the next time the through.  I hate it when I can't figure something out!  The procedure does work correctly with the replacement code using a QueryDef but I'd like to keep trying on this for a while longer.  Any other suggestions?

OM Gang
0
omgangIT ManagerAuthor Commented:
Oops!  on my last post I meant

The rs2 object variable is cleared at ........

OM Gang
0
jadedataMS Access Systems CreatorCommented:
If using the querydef to get around it is all it took...

  go with the Force young Luke!
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'd try a couple of things:

1) Declare your variables explicitly ... use Dim rs As DAO.Recordset, Dim dbs As DAO.Database etc etc

2) Try different variations of the the "get the subform" syntax: Set rs2 = Me.sfrmReviewQuote.Form.RecordsetClone    

3) Set a breakpoint BEFORE the trouble line and ensure that the subform's Recordset is indeed available ... type something like this in the Immediate window:  ?Me.sfrmReviewQuote.Form.RecordsetClone.Recordcount                
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
omgangIT ManagerAuthor Commented:
?Me.sfrmReviewQuote.Form.RecordsetClone.RecordCount returns a count of records first time through but generates the 3420 run-time error the second time through.
?rs3.RecordCount returns a count of records for this recordset though so the ref to rs2 is what's missing.

I'll try suggestions 1 & 2 next
OM Gang
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
When you say "the second time through", are you referring to one of the loops within your code block? If so, which one? Or do you mean if you call the entire code block multiple times?
0
omgangIT ManagerAuthor Commented:
When I call the entire code block a second time.  The procedure is a command button click event.  Users click the button to purchase product for a sales order they are writitng.  The first time the user clicks the button, the code executes correctly and they are taken to a purchase order form.  Once the user has completed the PO, they are returned to the sales order form (which is open the entire time).  It's when the user attempts to purchase additional product for the SO by clicking the command button a second time that the error is generated.  If the user were to exit the SO form after generating the first PO and then re-open the SO form and click the button to generate a second PO everything works fine.  It's when the code is run a second time during the same 'session' that the error generates.  Additionally, all users report that the error began popping up recently, i.e. the procedure worked fine until recently.  Also, the error occurs both in the production environment as well on my dev. machine so it doesn't appear to be related to a PC or changes made to a PC.

BTW, explicit declaration of the object variables did not solve the problem.  On to suggestion #2.
OM Gang
0
omgangIT ManagerAuthor Commented:
Changing syntax also didn't help.
One other thing - my exit routine also generates an error.  The error handler display the 3420 message and then Resumes to the exit routine.  This statement

If Not rs2 Is Nothing Then rs2.Close

generates the same error and goes into an endless loop.

Correct me if I am wrong but doesn't this indicate that rs2 is NOT NOTHING, i.e. something is assigned to it?
OM Gang
0
omgangIT ManagerAuthor Commented:
Update:  The close event of the purchase order line items form includes a function call that writes/updates the PO line items to the inventory table.  I have the recordset update wrapped in a transaction using a workspace object variable (see, I lied to you earlier).  I have stepped through all the code, checking the value of

?Forms.frmSalesLineItems.sfrmReviewQuote.Form.RecordsetClone.RecordCount

in the Immediate Window as I went.  In the exit statement of the function, when I clear the workspace object variable

    If Not ws Is Nothing Then ws.Close
        Set ws = Nothing

the check of the RecordCount generates the 3420 error.  It appears that a RecordsetClone of any open object disappears when a workspace object variable is cleared.
OM Gang
0
omgangIT ManagerAuthor Commented:
I tried clearing and then reassigning the recordsource for the subform

        Me.sfrmReviewQuote.Form.RecordSource = ""
        Me.sfrmReviewQuote.Form.RecordSource = "qryQuoteLineItems"

immediately before the recordset variable (rs2) is assigned to see if that would help but, alas, it did not.  Same result as before, first time it works, second time (after the workspace object variable is cleared in the remote function) it gives the 3420 error.

OM Gang
0
omgangIT ManagerAuthor Commented:
To test I did this:  created a new database with two tables (one to many) and a query to combine the records of both tables.  Two forms, one based upon one of the tables and the other based upon the query.  A third (main) form with the two other forms as subforms.  A command button with the following

Private Sub cmdTest_Click()

    Dim varPCount As Variant, varACount As Variant
    Dim strMsg As String
   
    varPCount = Me.sfrmPeople.Form.RecordsetClone.RecordCount
    varACount = Me.sfrmAutos.Form.RecordsetClone.RecordCount
   
    If IsNull(varPCount) Then varPCount = "Null"
    If IsNull(varACount) Then varACount = "Null"
   
    strMsg = "People Count: " & varPCount
    strMsg = strMsg & vbCrLf & "Car Count: " & varACount
   
    MsgBox strMsg, , "Test"
   
End Sub

and another command button

Private Sub cmdWS_Click()

    OpenWorkspace
   
End Sub

that calls this function

Public Function OpenWorkspace()

    Dim ws As Workspace
   
    Set ws = DBEngine.Workspaces(0)
   
    If Not ws Is Nothing Then ws.Close
        Set ws = Nothing
       
End Function


Clicking cmdTest pops up a message box with the record counts from the two RecordsetClone objects.  After clicking the othe command button (and calling the function) if I click on cmdTest again I get the 3420 error.

So, simply closing the workspace object variable destroys the RecordsetClone objects of any open forms.  Interesting info. (to me at least).

I really appreciate you taking the time to look at this.  Using the QueryDef of the query instead of the RecordsetClone solved the problem so I'm back to work.  Thanks for the tips and ideas.
OM Gang
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
DBEngine.WorkSpaces(0) is the default workspace ... you should never close it, nor should you use .Close on a variable declared as such.

Instead, you should set it = Nothing ... this simply destroys the variable, while the .Close command would likely invoke some sort of "cleanup" code and destroy objects (including your RecordsetClone, apparently). Try NOT .Close"ing" the workspace and see what happens, if you get some extra time.

Regardless, glad the code is working!!!
0
omgangIT ManagerAuthor Commented:
Not closing the workspace object variable does indeed solve the problem.  I still see code I wrote years ago where I didn't clean up any object variables at all.  Now I'm cleaning too much.  You sound like my wife! (JK)
OM Gang
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Me too ... sometimes I have to revisit code I wrote back "in the day" when I was just learning and it can be humbling, to say the least <grin> ...

There's really only two items you need to watch out for in that respect, and that's the a workspace var pointing to DBEngine.Workspaces(0), and a DAO.DAtabase variable point to CurrentDB ... you should never close either of those, but instead set them = Nothing. There may be more; in general, anytime you "point" to something that Access built (and maintains) you shouldn't close it - if you build and maintain it (like a recordset), then you should close it.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.