UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Error Couldn't Open Any More Tables, Access 2016    
 
   
airdata
post May 25 2020, 06:31 PM
Post#1



Posts: 49
Joined: 24-October 09



Hi all

I have an issue with the below code that has me stumped. The code below is adapted from Alan Brown and works as intended. The issue I encounter is that after about 100 or so changes using the form, I receive the error "Couldn't open any more tables". On clicking debug, it highlights "Me.Filter = strWhere". When I close the form and reopen it, it work again as intended and then the cycle repeats itself. I am not a programmer by trade and will try adapt code snippets found on the internet.

Any help will be greatly appreciated.

CODE
Private Sub txtCN_AfterUpdate()
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
    
    '***********************************************************************
    'Look at each search box, and build up the criteria string from the non-blank ones.
    '***********************************************************************
    'Text field example. Use quotes around the value in the string.
    
    If Not IsNull(Me.txtCN) Then
        strWhere = strWhere & "([ConstructionNumber] = """ & Me.txtCN & """) OR "
        strWhere = strWhere & "([ConstructionNumber2] = """ & Me.txtCN & """) OR "
        strWhere = strWhere & "([Serial] = """ & Me.txtCN & """) OR "
        strWhere = strWhere & "([Registration] = """ & Me.txtCN & """) AND "
    End If

      
    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
        
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
        Me.OrderBy = "AircraftType"
        Me.OrderByOn = True
    End If

Me.txtCN = ""
Me.txtCN.SetFocus

End Sub


Thanks

Mark
Go to the top of the page
 
Doug Steele
post May 25 2020, 06:55 PM
Post#2


UtterAccess VIP
Posts: 22,303
Joined: 8-January 07
From: St. Catharines, ON (Canada)


I can't see any reason why that code would be the issue.

Do you have code anywhere that's instantiating recordsets?

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
MadPiet
post May 25 2020, 07:33 PM
Post#3



Posts: 3,777
Joined: 27-February 09



that's what I was thinking, Doug. Isn't that caused by opening but not explicitly closing recordsets and setting the recordset reference to Nothing?

rst.Open
'-- do some stuff

rst.Close
Set rst = Nothing

If you don't close and set them to nothing explicitly, then they hang around in memory, if I remember right. Can't remember if closing the form causes them to unload etc, though.
Go to the top of the page
 
Doug Steele
post May 25 2020, 07:47 PM
Post#4


UtterAccess VIP
Posts: 22,303
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Even if closing the form does cause them to unload, it's still a good practice to unload them explicitly.

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
airdata
post May 25 2020, 07:52 PM
Post#5



Posts: 49
Joined: 24-October 09



Thanks MadPiet and Doug for your insights.

I have had a look at the rest of the code for the form in question and had one instance where a recordset was opened and not explicitly closed. I have adjusted this now so we'll see what happens.

Thanks

Mark
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    4th July 2020 - 07:37 PM