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...
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...
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!)
lol. I was afraid of that - mostly because I'm not a programmer.
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.
I don't know what a 'GIS' is. So...you can put the code anywhere. Ideally, what you'd have would be this:
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.Code:currentdb.execute "insert into ChildTable ( FKfield ) VALUES (" & me.AutoNumField & ")"
But it's up to you.
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.
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.
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.....
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.
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