Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2006
    Posts
    11

    Unanswered: Want adding new client in parent table to be added to child table

    I am using Access 2003 and want to know how can I set up the dbs so that when I add a new record (client ID) to my parent table, the new record will also be added to a child table?

    I asked this on Thursday and someone mentioned subforms. I do not know code, so if this is what I should do, can I do this with wizards?

    Thanks,
    Nolla

  2. #2
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    What purpose would you need to duplicate data in this way?
    Based on DAO 3.6 and Access 2000 + 2003
    VB.Net 2005 + ADO.Net

  3. #3
    Join Date
    Jun 2006
    Posts
    11
    I have the two tables linked by client ID with other different fields that are not related in each table. If i enter a new client in the parent table, i want that new ID to show up in the other table, so that someone else knows there is a new client and she will go enter their contact information in the child table via another form. The parent table has client ID and interview information, the child table has client ID and contact information and each table is managed by two different people.

    thanks.

  4. #4
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    In this case have a Header form with client info, name, surname, address etc and create another table with the information that the two people store.

    It is not good practice to duplicate information in any way and should be avoided.

    I would definately consider re structuring your tables to accomodate only using one header table.
    Based on DAO 3.6 and Access 2000 + 2003
    VB.Net 2005 + ADO.Net

  5. #5
    Join Date
    Jun 2006
    Posts
    11
    I don't understand, I should have a third table that links the other two tables called a "header table"? I am not duplicating information except to have the common field of client id. The client ID is the primary key and the only "duplicated" field. I thought this is what you do to relate tables?

  6. #6
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    You do but it would be a foreign key in the other tables.

    This all depends how you are going to show your data.

    If you have one main holding form and you have child forms coming from this form when you open the child forms in and are adding a new record you feed a foreign key to the child form.

    This is very complicated because i really dont know how you are displaying your data, i have never had to append foreign keys to any tables when i create something buts that just how i create my forms, it may be very different to you.

    I completely mis understood BTW, i was under the impression you where duplicating the data between tables.

    If you could give a better understanding of how your forms work maybe this would be a bit easier, maybe up your database?
    Based on DAO 3.6 and Access 2000 + 2003
    VB.Net 2005 + ADO.Net

  7. #7
    Join Date
    Jun 2006
    Posts
    11
    I might be able to but how could I save it without all of the data and disable the multi-user password protection?

  8. #8
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    if it is sensitive information then just provide a blank database
    Based on DAO 3.6 and Access 2000 + 2003
    VB.Net 2005 + ADO.Net

  9. #9
    Join Date
    Jun 2006
    Posts
    11
    I am leaving work for the day- can we pick this up tomorrow?

    Thanks so much for all of your help already!

  10. #10
    Join Date
    Jun 2006
    Posts
    11
    Actually I just happened to find a way to query if the child table does not have the new client ID when the parent table gets a new one. THis is pretty close to what I wanted for an outcome. I would prefer that the child table generates the new ID when the parent table does, but this is pretty good for now.

    thanks again for your help!

Posting Permissions

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