Search All Database Tables Using FindRecord

[sallieann]

Hi there,

I have created an access database that stores details about parts - with the part code number as the primary key. There are alot of tables in the database, organised depending on the part type. I would like the user to be able to search all tables in the database from a search form which will search for anything (part name, part code, part type, etc). I currently have a txtSearch input box and a cmdSearch button & know that I need to use the FindRecord method in Visual Basic, but apart from that I am abit lost. Can anyone point me in the right direction?!

Thanks in advance
Sallie-ann

 

[DougP]

It would make more sense to have all parts in one table and add a extra column or two for the part type.

Like so
Part#, Description, Type, manufaturer, Qty on Hand, etc etc

then you are only searching through one table.

Now to add all the table togetnte into one is a append query.
Create a new blank table and add th epaets a table at a time
The last column would have a statric value for the type such as "C-clip" or whatever
then when you add the parts the already have the "type" written in when added.

DougP, MCP

 

[GKProgrammer]

Loop through the tables in your DB and look for the value in each table:

Dim dbs As Database, I As Integer

Set dbs = CurrentDb
With dbs
For I = 0 To .TableDefs.Count - 1
'...Your FindRecord code here
Next I
End With

You should also have a look at the DLookup Function, it may be a better choice for you to search your tables. Let me know how it turns out

Regards,
gkprogrammer

 

[MichaelRed]

I would follow DougP's recommendation as long as the various tables are fairly similar in structure. As long as there are FEW fields which are unique to a given set of (Table) of parts, it would seem that the 'waste' space from an unsed field or so in most of the records would be an acceptable trade off for the capability to perform the search(es) you want.

On the other hand, I would normally think that the choice of different tables was based on hte dis-similarity of the structures, so the option to conform all tables to the single recordstructure may not be an acceptable direction. But you could do a single table of common fields with joins to the tables with unique fields for the part types. This may not completly solve your problem, as you mention that you want to " ... search for anything (part name, part code, part type, etc) ...", which in the broadest sense would include even the unique fields tables but it could simplify the searches if you limit the search to the common fields.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places