Solved

access variable not defined error.

Posted on 2014-09-08
2
217 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

831 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