tweak this for me...please

[rfuscjr via AccessMonster.com]

This code should take a table containing column names and identify those
queries containing said names into a table (templist-showing column name and
query name). It works but instead of inserting the *value* found in 'column
name' into the templist field it puts the actual column name (in this case:
Item) in. My syntax must be off here: sqlstr = sqlstr & !rst1![Item].Name
& "','"


Set TempTable = db.CreateTableDef("tbl_TempList")
With TempTable
.Fields.Append .CreateField("SearchItem", dbText)
.Fields.Append .CreateField("QueryNm", dbText)
End With
db.TableDefs.Append TempTable

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

Thanks!

 

[rfuscjr via AccessMonster.com]

This code should take a table containing column names and identify those
queries containing said names into a table (templist-showing column name and
query name). It works but instead of inserting the *value* found in 'column
name' into the templist field it puts the actual column name (in this case:
Item) in. My syntax must be off here: sqlstr = sqlstr & !rst1![Item].Name
& "','"

Set TempTable = db.CreateTableDef("tbl_TempList")
With TempTable
.Fields.Append .CreateField("SearchItem", dbText)
.Fields.Append .CreateField("QueryNm", dbText)
End With
db.TableDefs.Append TempTable

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

Thanks!

I think I figured it out: not: sqlstr = sqlstr & !rst1![Item].Name but rather:
sqlstr = sqlstr & !rst1![Item]; not sure why this part works then: sqlstr =
sqlstr & QueryNm.Name & "');" but it does return the queryname.

 

[Marshall Barton]

This code should take a table containing column names and identify those
queries containing said names into a table (templist-showing column name and
query name). It works but instead of inserting the *value* found in 'column
name' into the templist field it puts the actual column name (in this case:
Item) in. My syntax must be off here: sqlstr = sqlstr & !rst1![Item].Name
& "','"

Set TempTable = db.CreateTableDef("tbl_TempList")
With TempTable
.Fields.Append .CreateField("SearchItem", dbText)
.Fields.Append .CreateField("QueryNm", dbText)
End With
db.TableDefs.Append TempTable

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

I think I figured it out: not: sqlstr = sqlstr & !rst1![Item].Name but rather:
sqlstr = sqlstr & !rst1![Item]; not sure why this part works then: sqlstr =
sqlstr & QueryNm.Name & "');" but it does return the queryname.

QueryNm is a QueryDef and QueryNm.Name returns the name of
the query. Maybe you intended to use rst1!QueryNm??

OTOH, Item is the name of a field, so Item.Name returns the
name of the field, which. of course, is Item. I suppose
that your real code does not really have the extraneous !
before rst??