how to autofill data fields when ticking a checkbox

[emichelFACCI]

I am new on Access and currently use access 2003. I have read a few other posts already for other problems I have had and they have been very helpful, so I thought I'd post my own question as I couldn't find it in the posts..


I have two separate related tables, Contact and Company, and a form "add new contact". Contact has fields such as: ContactID, First Name, Surname, Address, City, Company ID, phone, etc. and a Main Contact field(Yes/No field).
Company has fields like:CompanyID, CompanyName, Address, City, Phone, etc, and a Main Contact field (text field).

On my "add new contact" form and "update contact" form, I have a checkbox (check if contact is main company contact).
What I would like to do is, when the checkbox is ticked, that the first name and surname of the contact on that form is automatically set into the company table under the Main Contact field.

I have tried creating a separate table called "Main Contact" with MainContactID for example, and etc. but the table will not update. I'm not sure if I am doing this the right way...

It's important for my database as each company has many contacts, and we need to identify on the company form which is the main contact.

Many thanks for your help in advance!!
Elodie

[NeoPa]

Maintaining your main contact information in two separate places (Contact and Customer tables) is not Normalised (See Normalisation and Table structures). This is not a good thing and I'd advise you to restructure to avoid this.

That said, looking specifically at the Contact table, you would need code to handle the situation whenever a contact is changed from or to being the Main Contact. When one is changed to Main Contact you would need code to remove this setting from whichever one previously had it set. Resetting this value should only be allowed if you have some valid logic that can determine which should be promoted in its stead. This logic would also need to be implemented in code in that case.

Importantly, in all scenarios, the first contact should always be forced to be the Main one.

[emichelFACCI]

Hi and thanks for your reply.

however I am not sure you have understood my question well. I do not have a Customer table but COMPANY table, and the details of the contacts are NOT stored in the company table but contact table. these two tables are linked by companyID (primary key of Company table).

In any case, I have reseolved my problem in another way

cheers

[NeoPa]

I'm pleased you've found a way to resolve your problem, nevertheless, my earlier advice, with the proviso that I used the wrong name for your table - a pretty minor inconsistency in the circumstances - for which I apologise, still holds.

Quote=emichelFACCI
What I would like to do is, when the checkbox is ticked, that the first name and surname of the contact on that form is automatically set into the company table under the Main Contact field.
End Quote

Assuming the quoted text is still true, then your more recent statement that :

Quote=emichelFACCI
the details of the contacts are NOT stored in the company table
End Quote

seems to be contradictory. I'm not sure where that leaves us. I would just say if the former is true then this is non-normalised data. If the latter is true, then you clearly don't have that problem.

Lastly, it would be interesting to see what your solution is, bearing in mind the question is posted in a public forum. If you have the time to post it that would be appreciated.