Email Fields like Outlook's Contact Form

[Robert Neville]

My Access database is a complex contact manager, which commercial
applications do not readily emulate. In summary, its distinction
involves managing contacts and companies (like most contact managers);
plus, relating multiple projects with numerous contacts and companies
(unlike most contact managers).

My challenge involves incorporating numerous interface improvements
similar to GUIs of modern contact managers (like Outlook 2002, Act!,
and Goldmine). Currently, I would like to incorporate multiple email
addresses for contacts and companies; with an input field similar to
Outlook's Contact Form. How does one create a form with a field that
drops down and allow the user to choose from four control sources? I
rather avoid having five separate email fields that occupy screen
space on my form. Plus, this solution may help me as well with
numerous phone numbers. Let me know if this functionality is even
possible in Access XP.

Please give me advice on the best approach. A sample Access database
with all design views that incorporate the Outlook Form scheme would
help me a great deal too.

[Arvin Meyer]

Outlook's email fields are a gross violation of relational database tenents.
The proper way to do this is to have table with communication types (Phone,
Fax, Cell, Pager, and Email) and communication numbers or addresses. That
way you can have as many (or as few) as you need. The table structures would
be something like:

tblCommunicationType
CommunicationTypeID - PK
CommunicationType - (values such as Phone1, EmailAddress1, etc)

tblCommunication
CommunicationID - PK
PersonID - FK to tblPeople
CommunicationTypeID - FK to tblCommunicationType
Number
Default - Yes/No - the default for that type?

Now you can have 1 phone number, or 40, and 20 more email addresses, and not
waste any space in the database. Typically, you'd display these on a
subform, often on a tab control used on the people form.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

"Robert Neville" <robert_n...@yahoo.nospam.com> wrote in message
news:ks5mmug1qccsrdicl...@4ax.com...

[Robert Neville]

Thanks for your insight about Outlook forms. You have some interesting
points about the communication table. I understand that Outlook may
not follow solid relational data conventions. My challenge involves
saving screen real estate by having a text box that allows the user to
choose the control source from form view like Outlook.

The communication table presented me with several challenges during my
previous implementation. So I disregarded it to simplify the process.
A dilemma arose when combining addresses with phone numbers. Here's a
make-shift explanation.

A Company Form, Contact Form, and Project Form reside in my database.
The control sources are the following.

Company Form = tblComp
Contact Form = tblCont
Project Form = tblProj

Each form has a sub-form for multiple addresses since each entity may
have numerous addresses. The address sub-forms' record sources have a
SQL statement similar to the one below. Now, the obstacle to overcome
with adding a communication table is maintaining an UPDATABLE query
statement in the address sub-forms' record source. As the workaround,
I placed the phone fields in the trelCompAddr. Please let me know if
another approach exists.

SELECT trelCompAddr.CompID, trelCompAddr.AddrID, tblAddr.AddrID,
tblAddr.AddrName, tblAddr.Addr1, trelCompAddr.Addr2, tblAddr.City,
tblAddr.StateID, tblAddr.PostalCode, tblAddr.Country,
trelCompAddr.Phone, trelCompAddr.Fax, trelCompAddr.Email,
trelCompAddr.Website, trelCompAddr.AddrTypeID
FROM tblAddr INNER JOIN trelCompAddr ON tblAddr.AddrID =
trelCompAddr.AddrID;

Database Schema
-----------------------
trelCompAdd
-----------------------
CompID
AddrID
Addr2
Phone
Fax
Email
Website
AddrTypeID

-----------------------
tblAddr
-----------------------
AddrID
AddrName
Addr1
City
State
PostalCode
Country