Cell Validation Dependent on another cell value

[Lois]

Hi
I am creating a table in access 07 & i was just wondering if i could set up the following:
i have a 'Preferred Method Of Contact' column containg lookup fields & i would like to set it so that 'E-Mail' could only be selected if there was an e-mail address in the 'E-mail address' colum & Home Phone could only be selected if there was a number in the Home Phone column. Is this possible?

 

[jzwp22]

Welcome to AWF!

First off, although Access has the capability of having lookup fields at the table level, it is generally not recommended. This link has more details as to why.

Now as to your question, it would be better to do this with code in a form rather than in the table. Your users should never see your tables, they should only interact with the database through your forms & reports.

Can you provide your table structure to see what you have? If you can't post your actual database, you can represent your table and fields as follows:

tblContacts (table)
-pkContactID primary key, autonumber field
-txtContactFName (text field)
-txtContactLName (text field)
etc.

 

[Lois]

Hi i have uploaded the file.. maybe you could show me!

 

[jzwp22]

Taking a look at your database, each customer contact has many contact methods (home phone, e-mail, mobile phone etc.). This describes a one (customer)-to-many (contact methods) relationship. This is best handled with a related table. Furthermore, when you have a relationship like this, you would normally use a form/subform setup. I have attached your database with an example form with two subforms. Both subforms are based on queries. One query just selects the preferred contact info while the other shows all contact info for the customer contact.

 

[Lois]

Thanks. i'm sorted now!!!

 

[jzwp22]

You're welcome; glad we could help.