Setting up Forms....

[dan113999]

Hi, i'm new at Accesss, so i've been having problems with the following: Currently i'm setting up a database where i keep a record of Companies and contacts within those companies. I set up a many to many relationship since some companies include multiple contacts or some contacts are related to multiple companies....I set this up by creating a table for companies, another for contacts, and a table that relates the two together.

Anyway, once i have this relationship set up, i try to create a form that allows me to fill in the information of a contact and within that form, have a subform that allows me to type in the companies that user is related to.

The problem is this, when i type in a company name for one user and the same company name for another user, a new company ID is created--i dont' want that since its the same company. Does anyone know a way around this?

Much appreciated...

-Dan

 

[llkhoutx]

Select a company, then add users for that company. You're evidently doing it visa versa.

 

[dan113999]

hmmm thanks -- but say i have a field for the company name and i want only one instance of that name. If the name typed in is used previously, i want the old user ID displayed instead of having a new one created. How might i do that?

 

[llkhoutx]

Select the company via a combo box, storing the ID in the subform recordsource, not the name. User the "Not in List" event of the combo box to add new companies to the company list.

 

[dan113999]

Thanks! That's a good idea...but do i have to worry if there are thousands of companies in the list? I'm worried that the database would be set up inefficiently, but if not, that's perfect.

 

[llkhoutx]

If you have a few thousand names, no problem. Try a combo box on a test form with your list of names. If it's too slow for you, Plan B.

With many many thousands, say 50,000+, you should consider a SQL Server back end.

 

[dan113999]

Thanks a lot for the advice -- i'll give the combo box a shot. The code's not too bad is it? (i haven't really coded anything for Access yet...) I'm just checking previous posts to see what to do