Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access - Creating a new record for multiple table (Access 2000)

    I need to create a routine that we allow for the creation of a new record in each of multiple tables. I want this to be done in a public sub or function so that I can call on it many times from many different locations. Any help will be greatly appreciated.

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

    Re: Access - Creating a new record for multiple table (Access 2000)

    Please provide more information.
    - Should those records be independent of each other, or linked in some way?
    - How should the records be populated?

  3. #3
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access - Creating a new record for multiple table (Access 2000)

    These records are linked. I am creating a database to keep track of aircraft data. I have multiple tables splitting that data into sections. For example I have tables called wing data, and tail data. When a new aircraft is to be entered I need each table to simultaneously have a new record generated having the aircraft name saved in each table.

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

    Re: Access - Creating a new record for multiple table (Access 2000)

    Does the main table have a primary key?
    Is it an AutoNumber field? If not, how does the primary key get populated?

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access - Creating a new record for multiple table (Access 2000)

    If I understand your situation correctly, it sounds like you want to set up and Aircraft table including a primary key (e.g., an autonumber field call AircraftID) and other fields containing the other information about a single aircraft. Your WingData table should have it's own primary key (e.g., another autonumber field called WingDataID) plus a long integer field (e.g., AircraftIDLink) plus the wing data fields. The AircraftIDLink field is then populated with the AircraftID value from the Aircraft table to provide the necessary relationship between the record in the Aircraft table and the corresponding wing data in the WingData table. You'll need to set up a "one-to-one" relationship between the two tables -- linking AircraftID in the Aircraft table to AircraftIDLink in the WingData table -- using Access's Relationships window. Now, if you set up an Aircraft form with a master/child related subform (linked through these two key fields), Access will take care of populating the Wingdata table's AircraftIDLink field when you create a new Aircraft table record on the master form. You can add additional subforms (e.g., on tabs) for your other data. It's a little tricky the first time you set something up like this, but it works quite nicely (Access creates the links for you when you add new aircraft records and "pulls" the right subform data as you step through the master form records).

    If one set of wing data can apply to many different aircraft, things need to be done a little differently (setting up a one-to-many relationship instead), but I don't know if this is applicable to your situation.

    Give it a try. If you get stumped, post back and I'm sure someone here will be able to help you out.

Posting Permissions

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