How would I make a form which searches for values

问题:

I am trying to make a form which searches for the value inside all of the tables in the database (there are more than 1 table). The result will be displayed as the name of the table which this appears in. If someone can help me that will be nice.

In short, I have a form with a textbox and button. I enter the search string (for example 183939) and click on the button. It searches the value (183939) inside all the fields in the tables in the database, and if the value is found, then it displays the name of the table that it appears in. Thanks for the help.

回答1:

I think this is a bad idea because it could take a very long time, and provide confusing results due to also searching system tables... but the following function will return an array of all table names containing the search term or nothing if it wasn't found. Calling example is such: theTables = containingTable("hello") where theTables is a variant. A limitation is that this will fail for multi-valued fields.

Function containingTables(term As String)
    Dim db As Database
    Dim tds As TableDefs
    Dim td As TableDef
    Set db = CurrentDb
    Set tds = db.TableDefs
    For Each td In tds
        For Each f In td.Fields
            On Error Resume Next
            If DCount("[" & f.Name & "]", "[" & td.Name & "]", "[" & f.Name & "] LIKE '*" & term & "*'") Then
                If Err.Number <> 0 Then
                    Debug.Print Err.Number, Err.Description
                    Err.Clear
                    On Error GoTo 0
                Else
                    containingTables = containingTables & td.Name & ","
                    Exit For
                End If
            End If
        Next
    Next
    Set tds = Nothing
    Set db = Nothing
    'Alternate Version
    if Len(containgingTables) then containingTables = Left(containingTables, Len(containingTables) - 1)
    'Original Version
    'if Len(containgingTables) then containingTables = Split(Left(containingTables, Len(containingTables) - 1), ",")
End Function

To display the results with the alternate version, just use: Msgbox(containingTables(searchTerm)) where searchTerm is whatever you are searching.

回答2:

Me as well i don't know why you would want to do something like that...

I think the solution posted by Daniel Cook is correct, i just took a slightly different approach. Do you need to match the exact value like I do? Anyway, here's my code:

Function searchTables(term as String)
  Dim T As TableDef
  Dim Rs As Recordset
  Dim Result() As String
  Dim Counter

  Counter = 0
  For Each T In CurrentDb.TableDefs
    If (Left(T.Name, 4) <> "USys") And (T.Attributes = 0) Then

      Set Rs = T.OpenRecordset
      While Not Rs.EOF
        For Each Field In Rs.Fields
          If Rs(Field.Name) = term Then
            Counter = Counter + 1
            ReDim Preserve Result(Counter)
            Result(Counter) = T.Name & "," & Field.Name
          End If
        Next
        Rs.MoveNext
      Wend
      Rs.Close

    End If
  Next
  If Counter = 0 Then
    searchTables = Null
  Else
    searchTables = Result
  End If
End Function

You should filter out duplicated values, in case the function matches multiple times the same filed in the same table.