SQL Pivot not working (1 Viewer)

Public Sub CreatePivot()
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim rs As DAO.Recordset
    Dim strSource As String
    Dim strSQL As String
    Dim bolNext As Boolean
    Set db = CurrentDb
    Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT CardID FROM MTDN ORDER BY CardID", dbOpenDynaset)
    With rs
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            If Not bolNext Then
                strSource = " FROM MTDN WHERE CardID='" & ![CardID] & "';"
                strSQL = "SELECT MAX([axle_serial]) As [" & ![CardID] & "],"
                bolNext = True
            Else
                strSQL = strSQL & "(SELECT MAX([axle_serial]) FROM MTDN WHERE CardID='" & ![CardID] & "') AS [" & ![CardID] & "],"
            
            End If
                
            .MoveNext
        Wend
        .Close
    End With
    Set rs = Nothing
    If Len(strSQL) > 0 Then strSQL = Left(strSQL, Len(strSQL) - 1)
    strSQL = strSQL & strSource
    On Error Resume Next
    Set qd = db.QueryDefs("zzMTD")
    If Err.Number <> 0 Then
        On Error GoTo 0
        db.QueryDefs.Append db.CreateQueryDef("zzMTD", strSQL)
        db.QueryDefs.Refresh
        Application.RefreshDatabaseWindow
    Else
        qd.SQL = strSQL
    End If
    Set qd = Nothing
End Sub