Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jul 2005
    Location
    Lancaster, Delaware, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    data entry (Access 2000/ windows xp)

    Hello all,
    I was wondering if there is anyway to have a value that is entered on a form become a record in two tables. That is, enter an item and have it "simultaneously" be recorded in two tables. As always your help is very much appreciated.

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

    Re: data entry (Access 2000/ windows xp)

    There are several possibilities, but we'd have to know about the situation.

    In many cases, it is not necessary to store the same information in two tables. If the tables are linked through a field, you can create a query that combines fields from both tables.

    If it is really necessary to store the same value in both tables, a main form - subform setup might do the job, or you could use VBA code.

    If you can provide some information about the why and what of your question, we may be able to recommend the best solution.

  3. #3
    Lounger
    Join Date
    Jul 2005
    Location
    Lancaster, Delaware, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: data entry (Access 2000/ windows xp)

    Hans,
    Thanks for the response. As far as my question goes maybe you can direct me into a better mindset. I have a database that keeps all of our inventory info. Now each component in the database has a category that corresponds to it. The problem that I have is that each component has not only a category but aso a subcatergory(if not several). The reasoning behind the subcategories is that when a user is searching for a component they can filter based on the categories and then the subcategories and then the subcategories until they reach a record that they are searching for. For example: a user is looking for a capacitor and he/she doesn't know the exact component number. So the user searches the component table and filters for capacitors. Now a query pops up the records that have a category of Capacitor. The next thing the user wants to filter by is the type of capacitor that it is ie Tantalum, Ceramic,etc. Then after that the user wants to search by voltage or capacitance or dielectric etc,etc,etc. This really gets involved when we start talking about Integrated Circuits. So how does a typical table structure for something like this. Is there a bunch of junction tables linking the component table to various category tables? I do not want duplicate info but am not sure as to how to go about giving the user want he/she wants. They pretty much want a search engine that is of the type seen on websites for component distributers. If you can point me in a direction or help me out in any way it will be very much appreciated.

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

    Re: data entry (Access 2000/ windows xp)

    You typically do this by creating a series of linked tables. For example:

    tblCategories:
    - CategoryID (AutoNumber, Primary Key)
    - Category (Text)

    tblSubCategories:
    - SubCategoryID (AutoNumber, Primary Key)
    - CategoryID (Number, Long Integer, linked to tblCategories)
    - SubCategory (Text)

    tblComponents:
    - ComponentID (AutoNumber, Primary Key)
    - SubCategoryID (Number, Long Integer, linked to tblSubCategories)
    - Component (Text)

    Each table can have additional fields, of course.

    A way to let the user search is through cascading combo boxes. See MSKB article ACC2000: How to Synchronize Two Combo Boxes on a Form. This article contains a link to download a demo database with a working example. Also see MSKB article ACC2000: How to Create Synchronized Combo Boxes.

    You can also search for cascading combo boxes in this forum. You'll find for example <post:=132,267>post 132,267</post:>.

Posting Permissions

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