I am working on a routine where if you put in any value, it looks
through all the tables of any database for that value.  Currently, I am
at the point where a table is created with three columns.

Here is an example of the table that is generated:

Table Name: SearchData

TableName FieldName SearchValue
Table1      Field1   alpha
Table1      Field1   alpha
Table1      Field9   alpha
Table5      Field3   alpha
Table5      Field8   alpha

In this case the word "alpha" is the search value and it generates a
table with all the tables and fields where "alpha" is found.

The next step is to create a routine that uses the values of TableName,
FieldName, and SearchValue to return a wildcard search of each table in
the recordset.

I am sure there is a better approach to this, but the code that has
evolved so far skips through the entire datasource and generates only
the first query from the first record.

What I'd like it to do is after it creates TEMPxyz that it allows the
user to review the data, and after reviewing it, that it either
automatically creates a new record and displays it, or allows the user
to click a next command button to do the same.

This brings up a related question:  Is it necessary to generate a new
table with each row of the source record set -- or can this done using
a different method being used.  Seems that constant
creating/overwriting of TEMPxyz would cause a lot of fragmentation of
the database.

Thanks for any help with this,

RBollinger

Function queryResults()
On Error GoTo Err_Line

    Dim db As DAO.Database
    Dim rsXYZResults As DAO.Recordset
    Dim mTable As String
    Dim mField As String
    Dim strSQL As String

    Set db = CurrentDb
   'Open the Table/Fields table
   Set rsXYZResults = db.OpenRecordset("SearchData", dbOpenSnapshot)
             With rsXYZResults
             .MoveFirst
             Do Until .EOF
                   mTable = Trim(.Fields("TableName"))
                   mField = Trim(.Fields("FieldName"))
                   mvalue = Trim(.Fields("SearchValue"))

                   strSQL = "SELECT " & mTable & ".*" & _
                   " INTO TEMPxyz" & _
                   " From " & mTable & _
                   " WHERE ((( " & mTable & "." & mField & ")" & _
                   " Like " & "'" & "*" & mvalue & "*" & "'" & "))"

                 db.Execute strSQL, dbFailOnError

                 DoCmd.SetWarnings False
                 DoCmd.OpenQuery "qryTEMPxyz", acViewNormal, acEdit
                 DoCmd.SetWarnings True
             .MoveNext
             Loop
              End With
           rsXYZFields.Close
           Set rsXYZFields = Nothing
           db.Close
           Set db = Nothing
Exit Function

Err_Line:
'MsgBox "Error occurred"
Resume Next
End Function