Struggling with the basics!! Please help!

[leahb747]

I'm a beginner at Access and I can't seem to find the correct answer on any other forum online.

I have a number of tables, forms and queries set up, it's just the very basic that is stuffing me up.

To break it down, I have two tables
1. CompanyRegister - contains 2 fields CompanyID (Auto No and Primary Key) and Company Name
2. Contact Register - contains ContactID (Auto No and Primary key) and CompanyID as well as a number of contact details etc.

They are related through the CompanyID, referential integrity enforced. And I have run a query and used this to put together a form for data entry. What happens though, is that whenever two contacts from the same company are entered in (which will quite often need to happen), the company name is listed twice, with two different CompanyIDs in the CompanyRegister table. How can I get Access to recognise that it already exists and just enter it in once?

Thanks in advance
Leah

 

[Beetle]

This should be handled through a main form / sub form setup. The main form would be based on the Company table an the sub form would be based on the Contact table. The Master/Child link of the Subform Control would handle the insertion of the appropriate CompanyID in the Contacts table.

 

[leahb747]

I was hoping to avoid a subform and use the query to make the form. It's just the double up I'm getting that's driving me crazy.

 

[leahb747]

The problem I find with the subform is that it's great as long as you can quickly find the Company you're looking for. I've gotten rid of the duplicates, but if you're say at company 800 and it tells you that it already exists, how do you go back through all the other records quickly to find what you're looking for? Query your form?

 

[Beetle]

You place an unbound combo box in the header of the main form that returns CompanyID and CompanyName from the Company table, and you set it up so that the main form moves to the correct record when you make a selection in the combo box. This can be done with the wizard if you're not familiar with how to set it up.

 

[leahb747]

That makes sense. Not sure the Wizard in 2007 has that option. I'll keep trying

 

[Beetle]

Not sure the Wizard in 2007 has that option

If you drop a new unbound combo box on your form in design view you should get the combo box wizard.