Solved

access variable not defined error.

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

747 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

9 Experts available now in Live!

Get 1:1 Help Now