Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multi Table Search (Access 97/SR 1)

    I am in the process of creating a table that contains chemical information. I need to allow the user to input a chemical number and search the 10 tables that contain additional information then display which tables the information is on.
    I have looked at the Seek option, but that appears to be related to using external tables and the 10 tables I need to look at are in the same database as the main file.
    I am starting to work with a multi table query but am not certain I'm on the right track.
    Any help is greatly appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multi Table Search (Access 97/SR 1)

    The Seek method works on tables in the current database too, as do the FindFirst and FindNext methods. Just use CurrentDb as the database:

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblElements", dbOpenTable)
    rst.Index = "PrimaryKey"
    rst.Seek "=", Me!txtSearch

    or

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblElements", dbOpenDynaset)
    rst.FindFirst "ElementID = " & Chr(34) & Me!txtSearch & Chr(34)

  3. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Posts
    160
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi Table Search (Access 97/SR 1)

    Dear Hans , can explan more where i put the code . if u attached an example it is better .
    ashraf

  4. #4
    New Lounger
    Join Date
    Apr 2002
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi Table Search (Access 97/SR 1)

    Thanks for the response - but I'm still a little new to the VB side. So I am trying to understand how this all works. Here are my questions;

    Do I need to link all 10 tables?
    What is the tblElements?
    I am guessing that I need to have a form with an Unbound field labeled txtsearch to display the results, correct?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multi Table Search (Access 97/SR 1)

    The name tblElements was just an example, as were the other names I used. You didn't provide specific information, so I made something up myself.

    From your original question, I guess that you want to display the names of those tables (among the 10) that contain the "Chemical number". Is this correct? If so, what is the name of the field that contains the "Chemical number" in the 10 tables?

  6. #6
    New Lounger
    Join Date
    Apr 2002
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi Table Search (Access 97/SR 1)

    The field is the same for all 10 tables - it is called CAS. Thanks Hans

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multi Table Search (Access 97/SR 1)

    I can think of two approaches:

    1) Create a query for each table that contains the info from the table plus the table name, and selects those records that match the chemical number entered in the search text box. Next, create a union query that combines the results of the separate queries. Use this to fill a list box or combo box or subform.
    It takes a bit of time to set up the queries, but you need only minimal code (requery the list box or whatever in the AfterUpdate event of the text box).
    2) Use DAO code to find the chemical number in each of the tables. The code is easiest if the tables have a consistent numbering scheme, like tblData01, tblData02 etc., or tblDataA, tblDataB, etc. You need to know a bit more about VBA and DAO, but this method is less work than the first one.

    I have attached a zipped database that demonstrates both approaches. Of course, if you want to copy bits of it, you will have to modify them to fit your situation.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •