Search Tables Collection for Text or Whole Field Number "Ronster" <rconners[ at ]keenan.com> 27.09.2006 17:40:35 I'm using MS Access 2003 and I am looking for a function that will search the entire Tables Collection for a specific string or whole field number (not part of a field).For example if I enter "widget" or "123456" it interrogates the entire tables collection and returns the table name and column name where the data is found. I have found some excellent code in this group for seaching for text but noting if I looking for a whole field number like 123456. (not 345). Appreciate any help.

RE: Search Tables Collection for Text or Whole Field Number Klatuu 27.09.2006 19:51:02 This is what I think you want; however, note that it will only find the first occurance. Function FindFieldValue(varFind As Variant) As String Dim tdfs As TableDefs Dim rst As Recordset Dim lngTblCount As Long Dim lngFldCount As Long Dim lngTblTot As Long Dim lngFldTot As Long Dim blnFoundIt As Boolean FindFieldValue = "Not Found" Set tdfs = CurrentDb.TableDefs lngTblTot = tdfs.Count - 1 For lngTblCount = 0 To lngTblTot Set rst = tdfs(lngTblCount).OpenRecordset If rst.RecordCount <> 0 Then rst.MoveLast rst.MoveFirst Do While Not rst.EOF lngFldTot = rst.Fields.Count - 1 For lngFldCount = 0 To lngFldTot If rst.Fields(lngFldCount).Value = varFind Then FindFieldValue = tdfs(lngTblCount).Name & "|" _ & rst.Fields(lngFldCount).Name blnFoundIt = True Exit For End If Next lngFldCount If blnFoundIt Then rst.Close Exit For Else rst.MoveNext End If Loop 'While Not rst.EOF rst.Close End If Next lngTblCount Set rst = Nothing Set tdfs = Nothing End Function "Ronster" wrote: [Quoted Text] > I'm using MS Access 2003 and I am looking for a function that will > search the > entire Tables Collection for a specific string or whole field number > (not part of a field).For example if I enter "widget" or "123456" it > interrogates the entire tables collection and returns the table name > and column name where the data is found. > > I have found some excellent code in this group for seaching for text > but noting if I looking for a whole field number like 123456. (not > 345). > > Appreciate any help. > >

Re: Search Tables Collection for Text or Whole Field Number "Ronster" <rconners[ at ]keenan.com> 27.09.2006 23:13:28 Excellent program Klatuu! And it's quick! Nice work. Since I need to be able to search all tables I have made some modifications to your code. This will search all tables for one find only then move to the next table. Function SearchAllTablesForValue(varFind As Variant) As String Dim tdfs As TableDefs Dim rst As Recordset Dim lngTblCount As Long Dim lngFldCount As Long Dim lngTblTot As Long Dim lngFldTot As Long Dim blnFoundIt As Boolean SearchAllTablesForValue = "Not Found" Set tdfs = CurrentDb.TableDefs lngTblTot = tdfs.Count - 1 For lngTblCount = 0 To lngTblTot Set rst = tdfs(lngTblCount).OpenRecordset If rst.RecordCount <> 0 And Left(UCase(tdfs(lngTblCount).Name), 4) <> "MSYS" Then rst.MoveLast rst.MoveFirst Do While Not rst.EOF lngFldTot = rst.Fields.Count - 1 For lngFldCount = 0 To lngFldTot If rst.Fields(lngFldCount).Type = dbLongBinary Then lngFldCount = lngFldCount + 1 End If If rst.Fields(lngFldCount).Value = varFind Then SearchAllTablesForValue = "Table - " & tdfs(lngTblCount).Name & _ Chr(13) & Chr(13) & "Field - " & rst.Fields(lngFldCount).Name MsgBox SearchAllTablesForValue, , "Searching For: " & varFind Exit Do End If Next lngFldCount If lngTblCount >= lngTblTot Then rst.Close Exit For Else rst.MoveNext End If Loop 'While Not rst.EOF rst.Close End If Next lngTblCount MsgBox "Search Complete" Set rst = Nothing Set tdfs = Nothing End Function ' ***************************************** Sub MyTestSub() ' Examples: ' DateValue("11/30/2005") (for 11/30/2005) ' "moser" ' 34704 ' 13000 (for $13,000.00) SearchAllTablesForValue DateValue("11/30/2005") End Sub

Re: Search Tables Collection for Text or Whole Field Number Klatuu 28.09.2006 13:39:04 Glad it worked for you. I can see where you may want to find in multiple tables, I considered that, but haven't had time to get back to it. I think I'll steal your code :) "Ronster" wrote: [Quoted Text] > Excellent program Klatuu! And it's quick! Nice work. > > Since I need to be able to search all tables I have made some > modifications to your code. > > This will search all tables for one find only then move to the next > table. > > Function SearchAllTablesForValue(varFind As Variant) As String > > Dim tdfs As TableDefs > Dim rst As Recordset > Dim lngTblCount As Long > Dim lngFldCount As Long > Dim lngTblTot As Long > Dim lngFldTot As Long > Dim blnFoundIt As Boolean > > SearchAllTablesForValue = "Not Found" > Set tdfs = CurrentDb.TableDefs > lngTblTot = tdfs.Count - 1 > For lngTblCount = 0 To lngTblTot > Set rst = tdfs(lngTblCount).OpenRecordset > If rst.RecordCount <> 0 And Left(UCase(tdfs(lngTblCount).Name), 4) > <> "MSYS" Then > rst.MoveLast > rst.MoveFirst > Do While Not rst.EOF > lngFldTot = rst.Fields.Count - 1 > For lngFldCount = 0 To lngFldTot > If rst.Fields(lngFldCount).Type = dbLongBinary Then > lngFldCount = lngFldCount + 1 > End If > If rst.Fields(lngFldCount).Value = varFind Then > SearchAllTablesForValue = "Table - " & > tdfs(lngTblCount).Name & _ > Chr(13) & Chr(13) & "Field - " & > rst.Fields(lngFldCount).Name > MsgBox SearchAllTablesForValue, , "Searching For: > " & varFind > Exit Do > End If > Next lngFldCount > If lngTblCount >= lngTblTot Then > rst.Close > Exit For > Else > rst.MoveNext > End If > Loop 'While Not rst.EOF > rst.Close > End If > Next lngTblCount > > MsgBox "Search Complete" > > Set rst = Nothing > Set tdfs = Nothing > > End Function > > ' ***************************************** > > Sub MyTestSub() > > ' Examples: > ' DateValue("11/30/2005") (for 11/30/2005) > ' "moser" > ' 34704 > ' 13000 (for $13,000.00) > > SearchAllTablesForValue DateValue("11/30/2005") > > End Sub > >