Almost Have It! One Part of Code incorrect though---Help!

[robertfuschetto via AccessMonster.com]

This code is suppose to open a table containing about 30 table names. It
should them look through the sql of each query in the db and see if the table
name is referenced. If so, it sets an indicator to true and writes that
query name to a separate table. It then searches for the remainder of the 30
or so tables. Here is the code. As of now I get a Compile error on the
line: For Each QueryNm In .QueryDefs. It says method or data member not
found. To be honest I am uneasy about the whole For / Next loop. Any advise
or 'fix up' code will be greatly appreciated.

Here is all the code........

Private Sub Command12_Click()
Dim QueryNm As QueryDef
Dim TableNm As TableDef
Dim TempTable As TableDef
Dim TempTableName As String
Dim Indicator As String
Dim sqlstr As String
Dim X As Integer
Dim db As Database
Dim rst1 As Recordset

DoCmd.SetWarnings False

Set db = CurrentDb()

'*******
With db
'*******

'Delete Table: tbl_TempList
For Each TableNm In .TableDefs
If TableNm.Name = "tbl_TempList" Then
DoCmd.DeleteObject acTable, "tbl_TempList"
End If
Next TableNm
'Create Table: tbl_TempList
Set TempTable = db.CreateTableDef("tbl_TempList")
With TempTable
.Fields.Append .CreateField("Name1", dbText)
End With
db.TableDefs.Append TempTable

'********
With rst1
'********
Set rst1 = db.OpenRecordset("tblIMPORTUserFieldsTable", dbOpenTable)
.MoveFirst
Do Until .EOF
For Each QueryNm In .QueryDefs
If InStr(QueryNm.SQL, [rst1]) <> 0 Then
Indicator = "Found"
sqlstr = "INSERT INTO tbl_TempList(Name1) VALUES ('"
sqlstr = sqlstr & QueryNm.Name & "');"
DoCmd.RunSQL sqlstr
End If
Next QueryNm
rst1.MoveNext
Loop

'*******
End With
'*******
'*******
End With
'*******
If Indicator = "Found" Then
MsgBox "Done!---Will Now Open the List for you."
DoCmd.OpenTable "tbl_TempList", acViewNormal
Else
MsgBox "Done!---No matches found."
End If

DoCmd.SetWarnings True

End Sub

 

[Martin]

Hi again,

I think you should lose the nested With/End Withs, i.e. get rid of the "With
db" at the start. You then need to expressly put db where it's needed, i.e.:
For Each QueryNm In db.QueryDefs

Secondly, when using recordsets you need a bang (!) before the field name,
i.e.:
If InStr(QueryNm.SQL, rst1![xxx]) <> 0 Then

where xxx refers to the name of the field in tblIMPORTUserFieldsTable that
contains the list of table names you're looking for. In fact, because you've
started with a "With rst1" you only need:
If InStr(QueryNm.SQL, ![xxx]) <> 0 Then
and later:
.MoveNext

but that doesn't really matter as it's only shorthand.

 

[SteveS]

Hi Robert,

I took your code and modified it. I added another field to "tbl_TempList"
and renamed "Name1".

The table names to search for is in a table called "tblNames" that has one
field called "tblName".

**Before the first time you run the code, delete the table "tbl_TempList".
The code will recreate it.

Here is the code:

watch for line wrap.....

'******BEG CODE*******************
Private Sub Command12_Click()
'
'You should add Error handling!!!
'
Dim QueryNm As QueryDef
Dim TableNm As TableDef
Dim TempTable As TableDef
Dim Indicator As String
Dim sqlstr As String
Dim db As Database
Dim rst1 As Recordset
Dim NoTable As Boolean

'Dim TempTableName As String
'Dim X As Integer

Set db = CurrentDb()
NoTable = True

'----------------------------
'check for Table: tbl_TempList
For Each TableNm In db.TableDefs
If TableNm.Name = "tbl_TempList" Then
'found - delete rows
db.Execute "Delete * from tbl_TempList ", dbFailOnError
NoTable = False
End If
Next TableNm

'Create Table if tbl_TempList not found
If NoTable Then
Set TempTable = db.CreateTableDef("tbl_TempList")
With TempTable
.Fields.Append .CreateField("queryName", dbText)
.Fields.Append .CreateField("tableName", dbText)
End With
db.TableDefs.Append TempTable
End If
'----------------------------

'tblNames is a table with one field "tblName" that holds
' the names of the tables you are searching for in the queries


Set rst1 = db.OpenRecordset("tblNames", dbOpenTable)

' need to check to make sure there are records or else it bombs
If Not rst1.BOF And Not rst1.EOF Then
rst1.MoveFirst
For Each QueryNm In db.QueryDefs
'Debug.Print QueryNm.Name
If Left(QueryNm.Name, 1) <> "~" Then
Do Until rst1.EOF
If InStr(QueryNm.SQL, rst1!tblname) <> 0 Then

'tbl_TempList is a table with two text fields:
' queryName - holds the name of the query
' tableName - holds the name of the table found in the
query
sqlstr = "INSERT INTO tbl_TempList(queryName,tableName)
VALUES ('"
sqlstr = sqlstr & QueryNm.Name & "', '"
sqlstr = sqlstr & rst1!tblname & "');"
'MsgBox sqlstr
db.Execute sqlstr, dbFailOnError
Indicator = "Found"
End If
If Not rst1.EOF Then
rst1.MoveNext
End If
Loop
rst1.MoveFirst
End If
Next QueryNm
End If

If Indicator = "Found" Then
MsgBox "Done!---Will Now Open the List for you."
DoCmd.OpenTable "tbl_TempList", acViewNormal
Else
MsgBox "Done!---No matches found."
End If

End Sub
'******END CODE*******************


BTW, it would be easier to see when you get your problem fixed if you posted
to the one thread, instead of having three threads in two forums.

HTH