cannot open anymore databases (there ought not be that many open?)

[markjkubicki]

my code runs without issue; EXCEPT that at the end of it, i get the error: "cannot open anymore data bases"

although it ought not to matter, there are
...146 records in tbeFixtureTypeDetails
and


...114 records in tbeSubmittalDetails

(learn something new every day... I'm lost on this one.)

any suggestions would be appreciated in advance,
best, stay well,
m.

Public Sub SubmtlStatusSmry()
    On Error GoTo Err_SmtlTypeStatusSmry
    Dim dbs As Database
    Dim rst As DAO.Recordset
    Dim rst1 As DAO.Recordset
    Dim intDetail_Cnt As Integer
    Dim intTypeSubmtl_Cnt As Integer
    Dim vMAX_ID As Integer
 
    CurrentDb.Execute ("DELETE * FROM tbeSubmittalDetailSmry_temp")
    
    intDetail_Cnt = DCount("*", "tbeSubmittalDetails")
    If intDetail_Cnt > 0 Then
        Set dbs = CurrentDb
        Set rst = _
            dbs.OpenRecordset("SELECT * " & _
            "FROM tbeFixtureTypeDetails WHERE NOT void", _
            dbOpenDynaset)
        With rst
            DoCmd.GoToRecord , "", acFirst
            Do Until .EOF
                intTypeSubmtl_Cnt = DCount("*", "tbeSubmittalDetails", "type = '" & rst!Type & "'")
                If intTypeSubmtl_Cnt > 0 Then
                    Set rst1 = Nothing
                    Set rst1 = dbs.OpenRecordset("SELECT * FROM tbeSubmittalDetails WHERE type = '" & rst!Type & "' ORDER BY SubmittalID DESC")
                    vMAX_ID = rst1!SubmittalID
                    'add the type
                    CurrentDb.Execute _
                        "INSERT INTO tbeSubmittalDetailSmry_temp " & _
                        "(Type, SubmittalID, Action) " & _
                        "SELECT Type, SubmittalID, Action FROM tbeSubmittalDetails WHERE Type = '" & rst!Type & "' AND SubmittalID = " & vMAX_ID
                    rst1.Close
                    Set rst1 = Nothing
                Else
                    ' add code for "no SUBMITTAL yet Received"
                End If
                .MoveNext
            Loop
        End With

        'housekeeping
        rst.Close
        dbs.Close
        Set rst = Nothing
        Set dbs = Nothing
    Else
        MsgBox "There are no entries to summarize"
    End If

    

Exit_SmtlTypeStatusSmry:
    Exit Sub

Err_SmtlTypeStatusSmry:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure SmtlTypeStatusSmry, line " & Erl & "."
    Resume Exit_SmtlTypeStatusSmry:

End Sub

[Bob Fitz]

Have you had a look on google. I believe that fault is often caused by having an infinite loop.

[isladogs]

That is one of the more unhelpful error messages and often means you have run out of available connections due to having too many databases objects and recordsets open.
Have a look at my app (adapted from an idea originally by Ben Sacharich) which monitors this. It may help.
See http://www.mendipdatasystems.co.uk/a...ons/4594418530

[Micron]

This will be interesting to know how many recordsets show up. I notice that this
Set rst1 = Nothing
is done at the end AND the beginning of the loop. Rather than continually creating and destroying recordsets I would have tried creating it once but SET it to a new set of data on each loop. Not sure if that would make a difference (or even if that works), but when we see something like this, sometimes it's because pc speeds are too fast and as a result, we see situations where code lines don't get processed.

[markjkubicki]

I changed the middle of the code to be (see below); getting rid of the rst1, and replacing with dMax() - which gets me to where i wanted to be and is probably what i ought have used in the first place).

...still stuck

With rst
            DoCmd.GoToRecord , "", acLast
            DoCmd.GoToRecord , "", acFirst
            Do Until .EOF
                'check if there are any submittals for this type
                intTypeSubmtl_Cnt = DCount("*", "tbeSubmittalDetails", "type = '" & rst!Type & "'")
                If intTypeSubmtl_Cnt > 0 Then
                    'determine the most recent for the type
                    vLastSubmittalID = DMax("[SubmittalID]", "tbeSubmittalDetails", "[type] = '" & rst!Type & "'")
                    'add the type
                    CurrentDb.Execute _
                        "INSERT INTO tbeSubmittalDetailSmry_temp " & _
                        "(Type, SubmittalID, Action) " & _
                        "SELECT Type, SubmittalID, Action FROM tbeSubmittalDetails WHERE Type = '" & rst!Type & "' AND SubmittalID = " & vLastSubmittalID
                Else
                    ' add code for "no SUBMITTAL yet Received"
                End If
                .MoveNext
            Loop
End With

[markjkubicki]

well....
if you're creating the reports' data while you're in the report (vs. before the report)
(.... there's where the problem was)

"if the problem is persistently not where you expect it to be ...look somewhere else"
problem solved

thnx,
m

[Micron]

With rst
            DoCmd.GoToRecord , "", acLast
            DoCmd.GoToRecord , "", acFirstUsually just

Usually it's just
With rs
.MoveLast
.MoveFirst

and it's not even necessary to do that unless you need the record count.
However, if the rs contains no records, you will raise an error either way. Better (?)
If Not (rs.EOF And rs.BOF) Then 'if both, then there are no records
Do Until rs.EOF
...

[markjkubicki]

officially now: my "best tip gotten May 2020" !!!

big thnx

[ssanfu]

Apparently you have a field name of "Type".
Be aware that "Type" is a reserved word in Access and shouldn't be used for object names.

Set rst = _            
dbs.OpenRecordset("SELECT * " & _            
"FROM tbeFixtureTypeDetails WHERE NOT void", _            
dbOpenDynaset)

I also question the use of "VOID" in the where clause.


Maybe you meant

WHERE NOT IsNULL(SomeFieldName)