QueryDef Object ongeldig of niet meer ingesteld.

Option Compare Database
Option Explicit
Option Base 1

Private Type CardRow
    strName As String
    intQty As Integer
End Type

Private arrTypes As Variant
Public CardsArray(8) As CardRow

Public Sub CountCards(curRecord As Long)
    Dim MagicDb As DAO.Database
    Dim qStats As DAO.QueryDef
    Dim qResult As DAO.Recordset
    Dim Index As Integer
    Dim Counter As Integer

    Set MagicDb = CurrentDb
    
    'Fill Variables with names of cards you want to check.
    arrTypes = Array("Creature", "Sorcery", "Instant", "Enchantment", "Artifact", "Land", "Other", "Total")
    For Index = 1 To 8
        CardsArray(Index).strName = arrTypes(Index)
        CardsArray(Index).intQty = 0
    Next
    
    With MagicDb
    
        'Delete previous query if it exists
        If IsTableQuery("", "DeckStatsQuery") Then .QueryDefs.Delete "DeckStatsQuery"
        
        'Create a totalsquery of current record.
        Set qStats = .CreateQueryDef("DeckStatsQuery")
        qStats.SQL = "SELECT Cards.[Card Type].Value AS Category,  Sum(DeckItems.Qty) AS TotalCards " & _
                    "FROM Cards INNER JOIN DeckItems ON Cards.CardId = DeckItems.CardId " & _
                    "WHERE DeckItems.DeckId = " & curRecord & " " & _
                    "GROUP BY Cards.[Card Type].Value;"
        qStats.Close
        
        Set qResult = .OpenRecordset("DeckStatsQuery", dbOpenSnapshot)
        With qResult
        
            If .RecordCount > 0 Then   'Check wether the query is not empty.
                Do Until .EOF    'Loop through all records of the query.
                    For Index = 1 To 7   'Test the Card Type is one of the basic 6 types
                        If .Fields("Category").Value = CardsArray(Index).strName Then
                            CardsArray(Index).intQty = CardsArray(Index).intQty + .Fields("TotalCards")
                            Exit For
                        ElseIf Index = 7 Then
                            'Card Type is other than the 6 basic types
                            CardsArray(Index).intQty = CardsArray(Index).intQty + .Fields("TotalCards")
                        End If
                    Next
                    .MoveNext
                Loop
                'I use Dsum here, because sum gives the wrong total amount of cards
                'because of the multi value fields, wich counts double
                CardsArray(8).intQty = DSum("Qty", "DeckItems", "DeckId = " & curRecord)
            Else
                MsgBox "Table has no records", vbOKOnly, "Empty table"
            End If
        
        End With
        .QueryDefs.Delete qStats.Name
        .Close
        Set qResult = Nothing
        Set qStats = Nothing
        Set MagicDb = Nothing
    End With
End Sub