ALtering code behind button on a form...

[robertfuschetto via AccessMonster.com]

I tried another forum, perhaps you guys could help. I would think this would
be simple to enhance...

I have some code. You fill in a table name on a form and press a button. It
goes through all the queries in a db and reports back those queries that
contain the table name. I want to alter the code to look for more than one
table at a time. In effect I have a table that contains the table names I
want it to look for. When I hit the button it should open this table and
start searching queries to see if the tables therein are referenced in the
queries. Here is the current code that looks for one table at a time. How
would I modify it?

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

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

'Start looking thru each Query's Sql to see if it contains the table name
desired,
'if so add it to the table: tbl_TempList
For Each QueryNm In .QueryDefs
If InStr(QueryNm.SQL, [Forms]![frmSearchQueriesForTableName]![TableName])
<> 0 Then
Indicator = "Found"
sqlstr = "INSERT INTO tbl_TempList(Name1) VALUES ('"
sqlstr = sqlstr & QueryNm.Name & "');"
DoCmd.RunSQL sqlstr
End If
Next QueryNm
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]

You could use a recordset variable, pointed at your table of table names, and
then you can loop through each record. Add the following to your Dim lines:
Dim rst As Recordset

Then:
Set rst = db.OpenTable("table of names", dbOpenDynaset)

Now you can loop:
rst.MoveFirst
Do Until .EOF
MsgBox rst![field name of list of names]
rst.MoveNext
Loop

This just displays each name in turn but you get the idea.

 

[Martin]

Sorry, missed out an rst (you could also use a With/End With):
rst.MoveFirst
Do Until rst.EOF
MsgBox rst![field name of list of names]
rst.MoveNext
Loop

You could use a recordset variable, pointed at your table of table names, and
then you can loop through each record. Add the following to your Dim lines:
Dim rst As Recordset

Then:
Set rst = db.OpenTable("table of names", dbOpenDynaset)

Now you can loop:
rst.MoveFirst
Do Until .EOF
MsgBox rst![field name of list of names]
rst.MoveNext
Loop

This just displays each name in turn but you get the idea.

I tried another forum, perhaps you guys could help. I would think this would
be simple to enhance...

I have some code. You fill in a table name on a form and press a button. It
goes through all the queries in a db and reports back those queries that
contain the table name. I want to alter the code to look for more than one
table at a time. In effect I have a table that contains the table names I
want it to look for. When I hit the button it should open this table and
start searching queries to see if the tables therein are referenced in the
queries. Here is the current code that looks for one table at a time. How
would I modify it?

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

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

'Start looking thru each Query's Sql to see if it contains the table name
desired,
'if so add it to the table: tbl_TempList
For Each QueryNm In .QueryDefs
If InStr(QueryNm.SQL, [Forms]![frmSearchQueriesForTableName]![TableName])
<> 0 Then
Indicator = "Found"
sqlstr = "INSERT INTO tbl_TempList(Name1) VALUES ('"
sqlstr = sqlstr & QueryNm.Name & "');"
DoCmd.RunSQL sqlstr
End If
Next QueryNm
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

 

[robertfuschetto via AccessMonster.com]

thanks...will try!

Sorry, missed out an rst (you could also use a With/End With):
rst.MoveFirst
Do Until rst.EOF
MsgBox rst![field name of list of names]
rst.MoveNext
Loop

You could use a recordset variable, pointed at your table of table names, and
then you can loop through each record. Add the following to your Dim lines:

[quoted text clipped - 75 lines]

 

[robertfuschetto via AccessMonster.com]

Am I on the right track....how do I alter the part that looks through the
tables.....THANKS!


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()
Set rst1 = db.OpenRecordset("tblIMPORTUserFieldsTable", dbOpenTable)
With db
With rst1
.MoveFirst
Do Until .EOF
'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


'Start looking thru each Query's Sql to see if it contains the table name
desired,
'if so add it to the table: tbl_TempList
For Each QueryNm In .QueryDefs
If InStr(QueryNm.SQL, [Forms]![frmSearchQueriesForTableName]![TableName])
<> 0 Then
Indicator = "Found"
sqlstr = "INSERT INTO tbl_TempList(Name1) VALUES ('"
sqlstr = sqlstr & QueryNm.Name & "');"
DoCmd.RunSQL sqlstr
End If
Next QueryNm
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


rst.MoveNext
Loop




DoCmd.SetWarnings True

End Sub

Sorry, missed out an rst (you could also use a With/End With):
rst.MoveFirst
Do Until rst.EOF
MsgBox rst![field name of list of names]
rst.MoveNext
Loop

You could use a recordset variable, pointed at your table of table names, and
then you can loop through each record. Add the following to your Dim lines:

[quoted text clipped - 75 lines]

 

[Martin]

Careful, you seem to have too many Withs and VBA won't know which is which!
What I meant was to loop through the recordset (i.e. look through your table
of table names one row at a time) and substitute rst![field of table names]
(which is the name held within the curren row) into your code when it
searches through the query definitions.

Am I on the right track....how do I alter the part that looks through the
tables.....THANKS!


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()
Set rst1 = db.OpenRecordset("tblIMPORTUserFieldsTable", dbOpenTable)
With db
With rst1
.MoveFirst
Do Until .EOF
'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


'Start looking thru each Query's Sql to see if it contains the table name
desired,
'if so add it to the table: tbl_TempList
For Each QueryNm In .QueryDefs
If InStr(QueryNm.SQL, [Forms]![frmSearchQueriesForTableName]![TableName])
<> 0 Then
Indicator = "Found"
sqlstr = "INSERT INTO tbl_TempList(Name1) VALUES ('"
sqlstr = sqlstr & QueryNm.Name & "');"
DoCmd.RunSQL sqlstr
End If
Next QueryNm
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


rst.MoveNext
Loop




DoCmd.SetWarnings True

End Sub

Sorry, missed out an rst (you could also use a With/End With):
rst.MoveFirst
Do Until rst.EOF
MsgBox rst![field name of list of names]
rst.MoveNext
Loop

You could use a recordset variable, pointed at your table of table names, and
then you can loop through each record. Add the following to your Dim lines:

[quoted text clipped - 75 lines]

End Sub