Search query for multiple tables

[JDJD]

How do you make a search query or form or anything of that sort that will enable me to do a search through multiple tables? I have about 25 tables in one database and I need to find one name out of those tables. I do not want to go through the tables one by one and would like to just enter the name I want and it will go through the tables and display the location of the name. Thanks in advance.

 

[alaskaguru]

Public Function Search_All(srchSTR as String, Yourcolumn as String)<br>
Dim qdf as Querydef<br>
Dim tdf As TableDef<br>
Dim wsp As Workspace<br>
Dim dbData As Database<br>
Dim rst As Recordset<br>
Dim i As Long<br>
Set dbData = CurrentDb<br>
On Error Resume Next<br>
With dbData<br>
For Each tdf In .TableDefs<br>
strSQL = &quot;Select * from &quot; & tdf.Name & &quot; where &quot; & Yourcolumn & &quot; = &quot; & srchSTR & &quot;;&quot;<br>
Set rst = dbData.OpenRecordset(strSQL, dbOpenSnapShot)<br>
With rst<br>
If Not (.BOF And .EOF) Then<br>
'there are records in the recordset<br>
intCount = .RecordCount<br>
.MoveFirst<br>
' rest of code to manipulate the record goes here<br>
' use dynaset for updateable recordset<br>
' Or you can build a querydef here <br>
'<br>
Set qdf = dbs.CreateQueryDef(&quot;qry_OnFly&quot;, strSQL) <br>
exit ' quit while you're ahead<br>
end if<br>
end with<br>
<br>
Next tdf<br>
End With<br>
End Function<br>
<br>
Hope this helps a little--I didn't debug the code but you should get the general idea.<br>
<br>