?
Solved

access variable not defined error.

Posted on 2014-09-08
2
Medium Priority
?
225 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 29

Accepted Solution

by:
IrogSinta earned 2000 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

764 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