PeterBaileyUk
asked on
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
it highlights here: Set qdfNew = Db.CreateQueryDef(QryName,
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER