Form with subforms

[newbieaccess]

Hi, I was wondering if there is a way to have a Name field (which is the relationship between the 2 other subforms) to change in all 3 locations if someone changes it in the initial form?

Thank you

 

[dkinley]

How setting the relationships using autonumbering as primary and foreign keys and then just having the name in one table so you won't have to worry about it?

If the field is called 'Name' you could run into potential problems in the future. I also suppose that all names will be unique? What happens if there are two names that are the same?

-dK

 

[newbieaccess]

Initially we added in the autonumbering but how do you pull in information from the other tables using that logic because there is a 1 to many relationship

Example Master Table only has 1 row per Customer
then there's a funding table with many events occuring per customer
and another table of contact information with many contact per customer

 

[dkinley]

So in all of those tables and records, what name are you trying to update?

-dK

 

[newbieaccess]

Sometimes a Company can be acquired and we move to the acquired name so in the Master we would change it and would like to change it in the other 2 tables as well?

 

[dkinley]

In the scenario I presented, you would only have to change it once - in the master record.

You could use a query to drive your other forms by linking the pk and fk to use the name of the master record in the other forms.

In this manner, you aren't worried about the updates having to be replicated to the other tables potentially causing orphaned records and a mess of problems.

To make sure I have it right, when you acquire a new company - are you attempting to merge that company's records (funding, contacts) with another master record?

-dK

 

[newbieaccess]

Thank you for such a quick reply. I'm still a little confused. Here's an example:
Company A is in Master, Funding & Contacts
the Funding and Contacts tables have a 1 to many relationship with the Master because they can have multiple records.

So, Company A down the road gets acquired or they just change their name to Company XYZ and the data entry person goes into the Form which has the Master table driving it with subforms (funding and contacts) below. We want to make sure that the Funding and Contacts table also receive this update without him needing to dive into the actual tables?

 

[dkinley]

Aye ... you would only have to update it in the master.

The other forms derive the name of the company due to the linking of the pk and fks.

You would only have to drive the other forms with a query if you wanted the company name to be present on them. If the other tables are subforms of your mainform and you have adjusted the parent/child link on the subform properties, then there is no need to worry about it - Access takes care of all of it for you.

For reporting purposes, drive the reports with a query. Probably like you do it now. For instance, if you do are report about funding you are putting the company name on them. If you are also putting the company information (such as address) on the same report then you have to have a query to saddle all of this data together. You would saddle the name up just the same way you do the address.

HTH,
-dK

 

[newbieaccess]

how do I know where the primary keys and foreign keys are? I didn't create the form? When we tried to test it by changing the name in the master section it didn't change it in the other two?

 

[newbieaccess]

I think he created the relationship but doesn't appear to have created a parent-child link? Can you do this after the fact?

 

[dkinley]

The primary and foreign keys are created at the tables and yes, you can create these after the fact. If you look at the relationships screen (you might have to right-click and do a 'show all'), it should reveal if there are any relationships set up. If not, you can create these yourself.

Whoever did all of this for you might be manually doing the linking so you might have to analyze all of the code in and around the forms to check it out.

You can do things like use the query wizard to look for duplicate or unmatched fields from table to table to help you rationalize what is going on. It sounds like the description wasn't use on the table to define each field for you.

Most of all though, make a copy of the database and use that to experiment on - don't wreck your production copy until you are for sure know what you are doing, what you want to do and the steps to accomplish the objectives.

-dK

 

[newbieaccess]

thank you - there are relationships built, but they are just one -to - many? Do we need any cascading or referential integrity? -- thanks again!

 

[dkinley]

I normally use referential and cascade deletes ... again, it depends on the scope and purpose of the business rules you are using to define the requirements of the database.

Here are some links that may be of some benefit ...

http://www.accessmvp.com/strive4peace/Access_Basics_Crystal_080113_Chapter_04.pdf
http://www.itu.dk/~slauesen/UID/AccessTutorial.pdf
http://www.vtc.com/products/Microsoft-Access-2000-tutorials.htm
http://www.sienaheights.edu/personal/csstrain/Access2007.htm
http://www.techonthenet.com/access/index.php

These will get you started down the right path and provide a good launching point.

HTH,
-dK

 

[newbieaccess]

Thank you - I'll go thru them now I appreciate your help

 

[dkinley]

You bet. I think most importantantly is to take your time - try to get it done right the first time around so whatever updates/repairs you have to make later don't soak up alot of clock time.

Good luck!
-dK

 

[newbieaccess]

The links you sent were really helpful - I appreciate it

 

[Chaz]

My noobish input, your tables will be saving a number anyways to represent ABC company, changing the name in the company table does nothing with the number and will therefore be replicated through the database.

t_company
PK_ID (Autonumber)
Name

t_table1
PK_ID (Autonumber)
CompanyName (Number)

t_table2
PK_ID (Autonumber)
Otherstuff etc
CompanyName (Number)

Once you change the data in the t_company table, everything else will follow through.