Entering Data to update all tables.

[mike02]

Hey,



I have a main table which CompanyID is a FK in 7 other tables. How can I set it up to update all seven tables when i add a NEW company?

All relationships are 1 to many, refrential integrity, and Cascade Update.

I have tried having a tabbed Add form, Which i still have to input data in all tabs(subforms) to update the Company ID.
I have also tried using a form for just my Main table, but it doesnt automatically update the NEW entries.

Any ideas would be great.

Thanks,

[Bob Fitz]

I have a main table which CompanyID is a FK in 7 other tables. How can I set it up to update all seven tables when i add a NEW company?

What do you mean by "update all seven tables when i add a NEW company". What data, exactly do you expect/want them to be updated with.

[mike02]

Like i mentioned above, The companyID is a foreign key in the 7 tables. When i add a new company in the main company form, I want it to add the companyID in all the other tables, I only need that to be updated.

For example.

Main form.
CompanyID, Company, etc....

OrderForm
OrderID, Company ID. etc.

I need it to add a new entry in the other tables, and just input the companyID (ForeignKey of these tables)

The order ID is an AutoNumber, And so is CompanyID.

All other tables (7 of them) are set up the same way as the one above.

[Bob Fitz]

OrderForm
OrderID, Company ID. etc.

The Company ID is not needed until you supply the etc. If you do that with an Main form and Sub form, the Company ID value will be added to sub form by Access.

[mike02]

If it is not needed then how can i search on these records later? the issue is i need it to update all at once, sometimes I dont manually update them all with the addForm (tabbed with all subforms) but still need them all to be edited, or searched on later. This allows other users to udate information without having to add it, does that make sense?

Thanks,

[Bob Fitz]

If it is not needed then how can i search on these records later? the issue is i need it to update all at once, sometimes I dont manually update them all with the addForm (tabbed with all subforms) but still need them all to be edited, or searched on later. This allows othe rusers to udate information without having to add it, does that make sense?

No. I'm sorry, but to me it makes no sense at all.

If it is not needed then how can i search on these records later?

I did not say that it was not needed. I said that it is not needed until you enter some actual data. You have nothing to search for until then.

the issue is i need it to update all at once, sometimes I dont manually update them all with the addForm (tabbed with all subforms)

So how do you "Update" them?

This allows other users to update information without having to add i

What is it that allows other users and what is there to update if all you have in the record is the FK.

[mike02]

I have an Edit form set up, so all the users can do is edit it, I also have a navigation form set up, all the users can do Is read the data. The idea is that then they wont be destroying the data. This puts restrictions on it, My end users Have no experiance with access, so I am trying to make it User friendly all through forms. If im approaching this wrong in your opinion let me know so i can see it from another view.

Thanks,

[Bob Fitz]

I have an Edit form set up, so all the users can do is edit it, I also have a navigation form set up, all the users can do Is read the data.

So how does the data get into the table in the first place.

[mike02]

Through my add form.

I want to have it set up to update the foreign key immediately. then i can go in to the edit form later and go through the tabs and update the information on that specific table. The whole reason I have it set up like this is to make it more user friendly.

Now, I have it set up to search the company through a combo box the edit form. This then filters the subforms by the Company ID. That is why I need it to update, that why I just brings up theblank form and I can edit it.

Thanks

[Bob Fitz]

So, you have an "add" form in which the initial data is added. If this is a sub form on a main form then the FK is populated by Access and saved to the table when the current sub form record losses the focus.

[mike02]

so I have my Add form, (Main Table IS the control source,) then Once I input a company Name, it updates a new entry. The cOmpanyID that is generated will automatically update in the tab control(7 subforms) ONLY If i update the data in each individual form. It will not update if i dont enter data in each subform.

Note: I only need to enter data in at least one field on subforms for it to automatically update the companyID.

Now what im wanting to do is have it update regardless if i update a field in the subforms.

[Bob Fitz]

Can you post a copy of the db with a few dummy records. Needs to be in A2003 mdb format.

[orange]

Just to help clarify and put what Bob is saying into perspective:

Your Tables identify things (people, concepts...) and your FK identify the relationship(s) between tables.
You indicated that the CompanyID is the PK of 1 table and it is FK in 7 other tables.
Let's just suppose 1 of those 7 tables is Order.
You really can not relate an Order to a Company until you have an Order (and assign it some identifier).

So until you add an Order (with its Id, OrderDate,OrderQuantity, OrderAmt....) you really don't have an Order, and so it will not have a FK to identify (relate back to) the Company.

Hope this helps.

It might be better if you showed us a jpg of your data model(tables and relationships).

Since you mentioned Company and Order you may be interested in these links.
Build a data model; make sure all the info you want to retrieve is allowed for; normalize your data and set up relationships to provide referential integrity.

Here are some videos and links to assist in the process that should help put things into context.
Principles of relational Design http://forums.aspfree.com/attachment...2&d=1201055452
Entity Relationship Diagramming http://www.rogersaccesslibrary.com/T...lationship.zip

Video tutorials:
If you google for videos "normalization langer" you will find a series of free videos by Dr.Art Langer. These are quite good for learning by Watching/Listening rather than reading.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

Complete set of tutorials on Acc2010.
https://www.youtube.com/playlist?lis...FoilxbUY0yUqZP

[mike02]

I use this database for research, and learning about the Management companies and there companies information. That is why I need the search, Here, is an attachment.

thanks for the input, I will check out those Links! I apoloogize for using order as an example, I meant to use, ManagerID, ServiceProviderID, Etc.

[mike02]

I tried approaching this problem a new way. It worked. I have a form, like the top of the example, and then have a button to click save, then run append queries to update the information i need. appreciate the help on this.

Thanks!!