Search through entire database

[camaroltd82]

I have a db that keeps track of storeroom inventory. I have one table that includes all of my data, and I have searches, controlled by select queries that allow my users to search through and find parts by a certain classification. (Eg... Search by Vendor, Search by location, etc....) These searches work fine, as I use the following character string in the criteria box of the field I want to search..

Like & "*" [Enter Vendor Name to Search] & "*"

I was curious to know if there was a way for me to add anything that would allow my users to search through the entire db, with only a few key words that would search ALL fields of the table? Thanks in advance for any help

Mike Fischer
camaroltd82@msn.com

P.S. I just wanted to thank everyone that offers advice on this message board. I started this February as a beginner with access, and thanks to you, I now feel very comfortable using the program. I really do appreciate the assistance, and I hope to help some day too. THANKS!!

 

[raskew]

The attached function is intended to search all text and memo fields in a specified table. It could be easily modified to loop through all tables in an application's tableDefs. Hope this will provide a starting point:

Function ReplaceOmatic(pTable As String, pString As String, pRepwith As String)
'*******************************************
'Name: ReplaceOmatic (Function)
'Purpose: Search and replace for all text
' and memo fields in specified table
'Inputs: ptable: Table to search
' pString: String to replace
' pRepWith: Replacement string
'To call: from debug window: ? replaceomatic("Products1","o","~~")
'To recover: ? replaceomatic("Products1","~~","o")
'*******************************************

Dim db As DATABASE
Dim rs As Recordset
Dim strSQL As String
Dim td As TableDef
Dim namehold As String 'field name
Dim typehold As Integer 'field type
Dim lenhold As Integer 'field size
Dim namefix As String 'contents of namehold
Dim i As Integer, n As Integer
Dim j As Integer, k As Integer, l As Integer
Dim lefthold As String, righthold As String

Set db = CurrentDb
Set td = db.TableDefs(pTable)

'loop through the table's fields, select text or memo fields
i = td.Fields.Count

For n = 0 To i - 1
If td.Fields(n).Type = dbText Or td.Fields(n).Type = dbMemo Then
'dbText = 10; dbMemo = 12
   namehold = td.Fields(n).Name
   lenhold = td.Fields(n).Size
   typehold = td.Fields(n).Type
   'Debug.Print namehold & "; " & lenhold & ";" & typehold
   strSQL = "SELECT " & namehold & " FROM " & pTable & " WHERE " _
   & "Instr([" & namehold & "], """ & pString & """)>0;"
   Set rs = db.OpenRecordset(strSQL)
   l = 0
  'test for empty record set
   If Not rs.BOF Then
      rs.MoveLast
      rs.MoveFirst
      l = rs.RecordCount
   End If
   If l > 0 Then
      Do While Not rs.EOF
         j = Len(pString)
         namefix = rs(namehold)
         Do While InStr(namefix, pString) > 0
            k = 0
            k = InStr(namefix, pString)
            lefthold = Left(namefix, k - 1) & pRepwith
            righthold = RTrim(Mid(namefix, k + j))
            namefix = lefthold & righthold
         Loop
         'test for field length
         If typehold = 12 Or Len(namefix) <= lenhold Then
            '12 = memo field
            rs.Edit
            rs(namehold) = namefix
            rs.Update
         End If
         rs.MoveNext
      Loop
   End If
End If

Next n

rs.Close
db.Close
Set db = Nothing

End Function