Results 1 to 12 of 12
  1. #1
    zrawe16 is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2011
    Posts
    20

    New primary key entered creates a new blank row in related table

    Is there a way to create a relationship so when a new record is entered into the main table it automatically creates a new row/record in the related table with just the foreign key entered? Thanks...

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    yep. you'll probably have to use code. child recs are not created by default because they don't need to be, even if RI is present. It's not a violation of RI to have a parent with no children.

    Ironically too, it's not a violation of law for parents to have no children. (yeah, that's really relevant!)

  3. #3
    zrawe16 is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2011
    Posts
    20
    lol. I was afraid of that - mostly because I'm not a programmer.

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by zrawe16 View Post
    lol. I was afraid of that - mostly because I'm not a programmer.
    do you have anything else to say other than "well, I can't do it then"??? would you like some help??

  5. #5
    zrawe16 is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2011
    Posts
    20
    Well, its not a show stopper, but it would create a more ideal situation if I could build this into our database. I'll take any advice that you'd like to give. Where do I put the code? Tie it to the Unique ID field? The master table is now kept in our GIS, but I think eventually I'm going to try and link this access database to the table in GIS. Thanks for any advice.

  6. #6
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    I don't know what a 'GIS' is. So...you can put the code anywhere. Ideally, what you'd have would be this:

    Code:
    currentdb.execute "insert into ChildTable ( FKfield ) 
    
    VALUES (" & me.AutoNumField & ")"
    that's VBA code. But you might want to think about it. why have child records in a child table that are blank?? I don't know any other developer that does that. To me, that makes more clutter than necessary. It doesn't even comply with normalization rules.

    But it's up to you.

  7. #7
    zrawe16 is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2011
    Posts
    20
    Geographic Information System. Basically I'm tracking stormwater Ponds by mapping them in GIS which is tied to a table. Every three years we have to inspect these ponds. I've built our inspectioins sheets into tables in Microsoft Access that create forms. I think (being a novice and all) I could simply the process if the inspection tables are updated as I map the stormwater ponds. Since we only inspect every three years I'm trying to make this process as simple as possible. I'm learning more about normalization rules and standards as I go. There maybe an easier way, but as I said I'm a novice at best.

  8. #8
    zrawe16 is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2011
    Posts
    20
    Where would I place the code in access? I'm new to speaking in code.

  9. #9
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by zrawe16 View Post
    Where would I place the code in access? I'm new to speaking in code.
    you'd place it behind an event in the form when you add a new rec. you want a blank child rec added when with every new parent rec, right? so any event will work fine. as long as you can make it fire on a new rec entry.

  10. #10
    zrawe16 is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2011
    Posts
    20
    thanks again for this. Just w. my knowlege it seems like this would be something that would routinely be needed in database design. Otherwise you have to enter the unique ID in one table then open the other table and re enter the unique ID again. If the row is automatically entered when the record is entered in the main table it would prevent there from being a record in one table and not the other especially in a one to one relationship. Just seems like it should be an option when creating the relationships between tables in a database. I'm learning as I go.....

  11. #11
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    one to one's aren't used much, at least to my knowledge. I've never used one in the 12 years I've been doing this.

    But there are needs for it. I just have never worked with them. Oh hey, I'm going to mark this solved.

  12. #12
    zaphod0414 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    1

    One-to-One should mean One-to-One

    How is it difficult to understand why someone would want to create a relationship between tables such that for each record in one table access automatically ensures that a matching record is created in the other table(s) in this relationship. What else is a One-to-One relationship supposed to be? For each record in table A there is one matching record in table B (and one in table C, table D, etc). I am trying to do just that as well, and am having more trouble than I think I should making that happen.

    Consider an HR database that tracks employees. Rather that cram everything into one huge table, it's often easier to separate the data into multiple tables - one that contains the employees contact info, one that contains their work history, one that contains their scheduling info, etc. It would make sense to use the employee's unique ID as the PK in all of the tables, and that their should be one record in each table for each employee - even if that table starts out empty. When an employee is first hired and their contact info is entered, the other tables need to have an entry for that employee ID even though for now they may be completely empty otherwise, and will be filled in later. It makes no sense to have to type in the employee's ID number by hand into each additional table, especially considering that there is a One-to-One relationship already set up between these tables.

    When table A gets a new record, the related fields in the other table(s) should be populated with the related value - perhaps not as the default behaviour, but we should not have to resort to code to get this to happen. Does access not provide this as some deeply buried option somewhere perhaps? Or, is there some reason that this approach is not valid? I see no reason why you'd have to cram all that related info into a single table, but is there a better way to tell access to essentially keep record creation and numbering consistent across multiple tables? This has got to be simpler than we are making it.

    Z

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Foce new record in related table?
    By thekruser in forum Access
    Replies: 1
    Last Post: 11-08-2010, 02:00 PM
  2. Replies: 5
    Last Post: 10-28-2010, 09:48 PM
  3. Primary key multiplynig in blank columns
    By desiinusa123 in forum Queries
    Replies: 0
    Last Post: 03-12-2009, 04:29 PM
  4. Split a table into related tables
    By triley01 in forum Database Design
    Replies: 1
    Last Post: 03-12-2009, 02:38 PM
  5. one date entered to hit 3 fields in one table how?
    By techexpressinc in forum Queries
    Replies: 0
    Last Post: 12-10-2008, 10:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums