How to correctly link main table (form) to other tables (forms)

[Heejoe]

Dear members,

Sorry, I know this is a long story but this should make things clear, I hope.

Following my previous question "How to combine 1) Company with 2) Yearly financial data" another question pops up and I have lost sight on normalizing/linked fields protection etc. Can you help me?!

What I have is:
TblCompany details
TblCompany financials
Tbl Production Facilities
Tbl Brands
Tbl Extra (needs to be locked by password)
FrmCompany details with two subforms linking to all other tables except "Tbl Extra". "TblExtra" can be opened through a button with password. I have used about 10 controls to query and update tables with my needs.

Tables hold different yet similar pieces of information for a unique key (name of company). The tables have either the same primary key or are linked through foreign key. Referential integrity is applied.

What I need is:
The PROBLEM with the existing "new record" button is that only in the main table a record is created (say Example.Inc). When I put in data the underlying fields get filled in nicely. But: when I close the form and re-open the query doesn't show the added company. Adding the new company, example.inc, MANUALLY into the underlying and linked forms (tblExtra)makes it visible in the main form again.

My question:
How do I set-up an append query or structure or VB (sorry to be vague, searching the net has offered my so many "solutions" I don't know which is best) that adds a new record to ALL tables so my database doesn't get messy and my superiors can enter (and delete!) records after I leave???

Hope you can offer me some advice! Thanks so much for your efforts before hand.

Haio

 

[dhookom]

IMO, you shouldn't enter values into a record until you need to enter values. I'm not sure why your record wouldn't show the added company. I expect you have an INNER JOIN between a two or more tables in the Record Source of your main form. I would probably attempt to base the main form on a single table and add subforms for related tables.

Duane
Hook'D on Access
MS Access MVP

 

[Heejoe]

Thanks dhookom,

Including the subforms helped. Probably because the underlying query in the main form changed.

I had it working but if fails on me again. I have figured out that I have a ref.integrity+cascade for one table, linking to the main table.

I will try and experiment with the "joins". If I cannot work it out I will reply to this thread again.

Thanks again (sometimes one line of text can make a hell of a difference)

Haio