Solved

access variable not defined error.

Posted on 2014-09-08
2
215 Views
Last Modified: 2014-09-08
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

Open in new window

0
Comment
Question by:PeterBaileyUk
2 Comments
 
LVL 29

Accepted Solution

by:
IrogSinta earned 500 total points
ID: 40310025
Change the following line:
Dim qdefNew As QueryDef
to this:
Dim qdfNew As QueryDef

Also it looks like this statement is not needed since you don't make use of qdef
Dim qdef As QueryDef
Ron
0
 

Author Closing Comment

by:PeterBaileyUk
ID: 40310047
yep i had done something silly worked now. thank you
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now