Multi fields connected to a seperate record.

[Valheru]

Hello all,
I have 9 tables. tblCompany is the "one" and all others are the "many". There can be instances that as you update the customer information they could have 2 or more contacts, protocols, datasets, etc. What is the best way to design a form to make it easy to input data, without confusing the data entry person and me? The PK in tblCompany is CompanyID (auto-numbered), and all other tables have the same PK with the one-to-many relationship pointing to tblCompany. I have it set up where we would enter all company data first, e.g. Name, address, etc. Then all other tables have forms that have a pull down menu that accesses the Company Name of tblCompany. This (hopefully) guarantees the information will belong to the company in the pull-down menu. My problem is what do I do when there are several contacts, etc for the same company? I hope this makes sense. I've re-written it several times. Let me know if I need to be more specific.
Thank you.

 

[SBendBuckeye]

It sounds like you have done a nice job of normalizing your design. Maybe you could do something like this:

A. Use Access' autoform creation capabilities to create a
small form for each of the many sides (eg frmContact,
frmProtocols, etc)

B. Then create a multiple tab control on your main form

C. Put Main company information - Name, address, etc. on the
first tab

D. Then drag all of the little forms Access created for you
on to individual pages of your tab control as subforms

The benefit of doing something like the above is that on all of the tabs you can ensure the proper CompanyID, etc. is entered into the many side records. By creating them as subforms on the tab pages, you can add, edit, delete, etc. or anything else you want to do independently of the others. Of course you will want to restrict them only to the company from tab1.

Good Luck! I would be interested in knowing how this turns out for you.

 

[Valheru]

SBendBuckeye,
Thanks for the compliment and GREAT advice! I didn't know about the auto-form function. In your last paragraph you said:
"Of course you will want to restrict them only to the company from tab1."
How do I restrict them to only the one company? Any F.A.Q's, or links to explain this to me would be great!
Thanks again for everything.