access variable not defined error.

I had some code which worked on form opening but ive moved the code to call it as a sub and now it squeels variable not defined error.

it highlights here: Set qdfNew = Db.CreateQueryDef(QryName, Qry)

not sure what to do now.

The query is creating a number of queries based on particular tables.

I am in access vba

Dim Db As DAO.Database

Set Db = CurrentDb()

Dim qdef As QueryDef
Dim qdefNew As QueryDef
Dim td As TableDef
  
Dim PositionMarker As Integer
Dim PositionMarker2 As Integer
Dim model As String
Dim QrySubformSelect As String
Dim QrySubformFrom As String
Dim QrySubformGroup As String
Dim ModelArray() As String
Dim Qry As String
Dim QryName As String
Dim namestr As String
Dim clientstr As String

Dim Index As Long
Dim stopit As Boolean
Index = 0
Dim TempIndex As Long
Dim ExtractionStart As Long

'Me.CBModels.RowSource = ""
   For Each td In Db.TableDefs
         PositionMarker = InStr(1, td.Name, "Extractionswithrules")
  
       If PositionMarker > 0 Then
       
       
       Index = Index + 1
       End If
    
   Next td
   ReDim ModelArray(Index - 1)
   Index = 0
   For Each td In Db.TableDefs
    Debug.Print td.Name
   
    PositionMarker = InStr(1, td.Name, "Extractions")
  
       If PositionMarker > 0 Then
       Debug.Print PositionMarker
       
       
       clientstr = Mid(td.Name, 4, (PositionMarker - 1) - 3)
       Debug.Print clientstr
           Select Case clientstr
           
            Case "abi"
                
                model = Mid(td.Name, PositionMarker + 20, Len(td.Name))
                
                If Not model = ModelArray(0) And stopit = False Then
                ModelArray(Index) = model
             
                Else
                stopit = True
               
                End If
                
                
                QrySubformSelect = "SELECT [Tbl" & Mid(td.Name, 4, (PositionMarker - 1) - 3) & "Extractionswithrules" & model & "].decoderesult"
                QrySubformFrom = " FROM [Tbl" & Mid(td.Name, 4, (PositionMarker - 1) - 3) & "Extractionswithrules" & model & "]"
                QrySubformGroup = " GROUP BY [Tbl" & Mid(td.Name, 4, (PositionMarker - 1) - 3) & "Extractionswithrules" & model & "].decoderesult;"
                Qry = QrySubformSelect & QrySubformFrom & QrySubformGroup
                QryName = "QryAbi" & model
                Debug.Print QryName
                Debug.Print Qry
                If IsTableQuery("", QryName) = True Then

                    DoCmd.DeleteObject acQuery, QryName
                End If
                Set qdfNew = Db.CreateQueryDef(QryName, Qry)
                qdfNew.SQL = Qry
             
            
            Case "Cap"
           
                  model = Mid(td.Name, PositionMarker + 20, Len(td.Name))
                
                If Not model = ModelArray(0) And stopit = False Then
                ModelArray(Index) = model
                
                Else
                stopit = True
                TempIndex = Index
                End If
               QrySubformSelect = "SELECT [Tbl" & Mid(td.Name, 4, (PositionMarker - 1) - 3) & "Extractionswithrules" & model & "].decoderesult"
                QrySubformFrom = " FROM [Tbl" & Mid(td.Name, 4, (PositionMarker - 1) - 3) & "Extractionswithrules" & model & "]"
                QrySubformGroup = " GROUP BY [Tbl" & Mid(td.Name, 4, (PositionMarker - 1) - 3) & "Extractionswithrules" & model & "].decoderesult;"
                Qry = QrySubformSelect & QrySubformFrom & QrySubformGroup
                QryName = "QryCap" & model
                If IsTableQuery("", QryName) = True Then

                    DoCmd.DeleteObject acQuery, QryName
                End If
                Set qdfNew = CurrentDb.CreateQueryDef(QryName, Qry)
                qdfNew.SQL = Qry
                
            Case "Glass"
            
                   model = Mid(td.Name, PositionMarker + 20, Len(td.Name))
                
                If Not model = ModelArray(0) And stopit = False Then
                ModelArray(Index) = model
                Else
                stopit = True
                TempIndex = Index
                End If
               QrySubformSelect = "SELECT [Tbl" & Mid(td.Name, 4, (PositionMarker - 1) - 3) & "Extractionswithrules" & model & "].decoderesult"
                QrySubformFrom = " FROM [Tbl" & Mid(td.Name, 4, (PositionMarker - 1) - 3) & "Extractionswithrules" & model & "]"
                QrySubformGroup = " GROUP BY [Tbl" & Mid(td.Name, 4, (PositionMarker - 1) - 3) & "Extractionswithrules" & model & "].decoderesult;"
                Qry = QrySubformSelect & QrySubformFrom & QrySubformGroup
                QryName = "QryGlass" & model
                If IsTableQuery("", QryName) = True Then

                    DoCmd.DeleteObject acQuery, QryName
                End If
                Set qdfNew = CurrentDb.CreateQueryDef(QryName, Qry)
                qdfNew.SQL = Qry
                
            Case "kee"
             
                  model = Mid(td.Name, PositionMarker + 20, Len(td.Name))
                
                If Not model = ModelArray(0) And stopit = False Then
                ModelArray(Index) = model
                Else
                stopit = True
                TempIndex = Index
                End If
               QrySubformSelect = "SELECT [Tbl" & Mid(td.Name, 4, (PositionMarker - 1) - 3) & "Extractionswithrules" & model & "].decoderesult"
                QrySubformFrom = " FROM [Tbl" & Mid(td.Name, 4, (PositionMarker - 1) - 3) & "Extractionswithrules" & model & "]"
                QrySubformGroup = " GROUP BY [Tbl" & Mid(td.Name, 4, (PositionMarker - 1) - 3) & "Extractionswithrules" & model & "].decoderesult;"
                Qry = QrySubformSelect & QrySubformFrom & QrySubformGroup
                QryName = "QryKee" & model
                If IsTableQuery("", QryName) = True Then

                    DoCmd.DeleteObject acQuery, QryName
                End If
                Set qdfNew = CurrentDb.CreateQueryDef(QryName, Qry)
                qdfNew.SQL = Qry
                
            Case "smmt"
             
                   model = Mid(td.Name, PositionMarker + 20, Len(td.Name))
                
                If Not model = ModelArray(0) And stopit = False Then
                ModelArray(Index) = model
                Else
                stopit = True
                TempIndex = Index
                End If
               QrySubformSelect = "SELECT [Tbl" & Mid(td.Name, 4, (PositionMarker - 1) - 3) & "Extractionswithrules" & model & "].decoderesult"
                QrySubformFrom = " FROM [Tbl" & Mid(td.Name, 4, (PositionMarker - 1) - 3) & "Extractionswithrules" & model & "]"
                QrySubformGroup = " GROUP BY [Tbl" & Mid(td.Name, 4, (PositionMarker - 1) - 3) & "Extractionswithrules" & model & "].decoderesult;"
                Qry = QrySubformSelect & QrySubformFrom & QrySubformGroup
                QryName = "QrySmmt" & model
                If IsTableQuery("", QryName) = True Then

                    DoCmd.DeleteObject acQuery, QryName
                End If
                Set qdfNew = CurrentDb.CreateQueryDef(QryName, Qry)
                qdfNew.SQL = Qry
'            Case "vivid"
'
'                 model = Mid(td.Name, PositionMarker + 20, Len(td.Name))
'                If Not model = ModelArray(0) And stopit = False Then
'                ModelArray(Index) = model
'                Else
'                stopit = True
'                TempIndex = Index
'                End If
'                QrySubformSelect = "SELECT [Tbl" & Mid(td.Name, 4, (PositionMarker - 1) - 3) & "Extractionswithrules" & model & "].decoderesult"
'                QrySubformFrom = " FROM [Tbl" & Mid(td.Name, 4, (PositionMarker - 1) - 3) & "Extractionswithrules" & model & "]"
'                QrySubformGroup = " GROUP BY [Tbl" & Mid(td.Name, 4, (PositionMarker - 1) - 3) & "Extractionswithrules" & model & "].decoderesult;"
'                Qry = QrySubformSelect & QrySubformFrom & QrySubformGroup
'                QryName = "QryVivid" & model
'                If IsTableQuery("", QryName) = True Then
'
'                    DoCmd.DeleteObject acQuery, QryName
'                End If
'                Set qdfNew = CurrentDb.CreateQueryDef(QryName, Qry)
'                qdfNew.SQL = Qry
           
           
        End Select
        Index = Index + 1
        
      Else
      
      
      
      End If
       
     
       
    Next td





 Db.Close

Select allOpen in new window