Hi All, I found this code on a website that uses a form to search all tables in my database. Problem is that when I click "search" I get an invalid argument error. I am guessing that there is a problem with my SQL string. Missing space? Missing quotation marks? etc etc.... Anyway, here is the code:
Code:
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim rs As DAO.Recordset
Dim strSearch As String
Dim strTableName As String
Dim strIdentifier As String
Dim strFieldName As String
Dim strFieldValue As String
Dim strListRowSource As String
Dim i As Long
Dim lngCount As Long
If Me.txtSearch <> "" Then
strSearch = Me.txtSearch
strListRowSource = """TableName"";""Identifier"";""FieldName"";""FieldValue"""
Me.lstList.RowSource = strListRowSource
Me.lblNumRecords.Caption = ""
lngCount = 0
' iterate through each user table in the db
For Each tdf In CurrentDb.TableDefs
strTableName = tdf.Name
' exclude system tables
If Not tdf.Name Like "MSys*" Then
Set rs = CurrentDb.OpenRecordset(strTableName)
If Not rs.BOF Then
' iterate through each record in the table
Do Until rs.EOF
' iterate through each field in the record
For i = 0 To rs.Fields.Count - 1
' test for a match
strFieldName = rs.Fields(i).Name
strFieldValue = Nz(rs(strFieldName).Value, "")
If strFieldValue Like "*" & strSearch & "*" Then
' assuming pk is not compound and is first field in table
strIdentifier = rs(0).Value
strListRowSource = strListRowSource & ";" & strTableName & ";" & strIdentifier & ";" & strFieldName & ";" & strFieldValue
lngCount = lngCount + 1
End If
Next i
rs.MoveNext
Loop
End If
End If
Next tdf
Me.lstList.RowSource = strListRowSource
Beep
Me.lblNumRecords.Caption = lngCount & " matching value(s) founds."
Else
MsgBox "Please enter text to search.", vbCritical
End If
