Data Entry automate procedure

Hi Joanna

It sounds like you need to normalise your database. What this means is this : if you have multiple contacts for each company, rather than entering the company details all over again for each contact, you split the data into 2 tables - 1 for the companies and 1 for the contacts and link them using a 'foreign key' in the contacts table, like this :

tblCompanies
Company_id {unique key}
Company_name
Comapny_address1
..
Company_address4
..etc..

tblContacts
Contact_id {unique key}
Contact_Company_Link {foreign key, linked on a one-to-many relationship with tblCompanies.Company_id - see Tools -> Relationships}
Contact_Name
Contact_Phone_DDI
..etc..

When you set-up the data entry screens, you could either have separate forms for the companies and contacts or you can combine both into one form with a subform (this would entail having the company details in the main form and the contact details in the subform, and would provide what you asked for).

Here is a thread on database normalisation that might give you some guidance.

HTH, Andrew

ouch! This sounds that I'll have to do the work from the beginning. I new that I was missing something! Feel like an idiot. I've almost finished my project. If I split now the tables I'll have to rebuild all the queries etc... hmmm. Ok. anyway. Thanks for pointing this out to me.

Joanna

I think that if you follow andrew's suggestion in the long run you will have a far more efficient and manageable database.

well i'm stuck! ok! I have two tables one with company's details and one with the contacts. I have a form with the companies and a subfom with contacts. relation is the ID field. what is next. How do I join them so to add a new contact without having add the company again??

In your main form design, reduce the screen size so you can see the database window behind, click and drag the subform onto the detail section of the main form - this should invoke a subform wizard.
HTH, Andrew.

I'm not disagreeing with Andrew, but there is also a nifty approach that requires no new forms, but a touch of VBA code. You can add a button that says "clone record": Give regard to whether null fields are allowed, and if any key fields are being cloned, whether they are required to be unique.

I'm surprised so few seem to use this approach, because of the immense data entry savings.

Thanks Andrew! I've already found how to do it and finished with it. Gates thanks for the code but too late! I followed Andrew's advice and I will not change my project again. I got so dizzy trying to put everything in order again that I don't dare to change anything... Thanks anyway. I'll have the code in mind for a future need.

Joanna_gr said:

I got so dizzy trying to put everything in order again that I don't dare to change anything

Click to expand...

Do yourself (or the poor fellow who follows your footsteps) by making a flowchart while it's fresh in your mind. A flowchart doesn't have to be the cryptic boxes and circles that some I.T. people go wild with; it can be just a functional description in your own language that describes what's connected to what, and why. It may not be worth 2 hours effort (although it might be) but I'll bet that in just 7 minutes you can explain some crucial details. You might even include http://www.mrexcel.com/board2/viewtopic.php?t=142338 in your documentation :wink:

Gates > that sounds a good idea! I already wonder If It was me who built this database. sometimes even I forget what is connected to where... lol It's the first time that I created something that needs manual to explain to myself... how it works! And it's just a simple contacts base...