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
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
"robertfuschetto via AccessMonster.com" wrote:
> 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()
-----SNIP
-----SNIP
> End Sub
>
> --
> Message posted via http://www.accessmonster.com
>
Similar Threads
