C:\Data10\TOOLS\Crystal_Contacts\UPLOAD_version_ContactsDb\ReadMe_Contacts_070604

Contacts_070604_Crystal.mdb

Nearly every database that is developed in Access has need to keep track of contact information. The attached database has been designed to slide into your existing database. It includes, Tables, Forms, Reports, Queries, and, of course, Code!

The Contacts tables include:

    * People - Humans and Companies (or Organizations, etc)
    * Addresses
    * Phones
    * Email Addresses
    * Websites (includes place for Username is website is a forum like UtterAccess)
    * Relationships between people
    * Lists (the form for linking Lists to People has not yet been created -- I will leave that up to you <smile>)
    * "Anywhere" Notes -- can be attached to any table. If a table is related to People, all of its notes are also linked to People.


*** Relationship diagram ***

When you open the database and look at the Relationship diagram, it appears to be messy. This is because there is a generic Types table that is used for types in several tables. If you wish to get a clearer picture of the database, delete the Types table (both copies) from the diagram.

For People and Types, you will see 2 copies of each table on the diagram. This is because the PpLink (peopleLink) table has 2 link lines going to each of these tables -- one set for each side of the relationship. When Access sees this, it makes a second copy of the table for the second link line drawn.

Many tables include a Type -- such as People, Addresses, Phones, eMail, etc ...so a generic Types table is used. Each table in the database has a TID (Table ID) and that is how records are identified in the Types table. Type is such a common thing to collect and referential integrity can be enforced between Types and all the tables that use it.

Type is a drop-down on all forms and the NotInList event is used to allow users to add new types on the fly. Because so many forms have a TypeID field, a general function is called in the code behind the form for each NotInList event.

The Notes table links to the People table -- but not all Note records are linked to People. You can, for instance, attach Notes to List records and, in that case, PID is not filled out.

In the Notes table, TID is used to determine which table the Note links to. LinkID is the autonumber primary key value of the table the note is for.

*** Table IDs ***

usys_Tables is what is used to assign TIDs to tables in the database. It is a user system table so, to see it, you will need to:

1. choose Tools, Options from the menu
2. go to the View tab
3. check --> system Objects

any object prefaced with 'usys' is a user system object. 'MSys' means Microsoft system object.

here are records from usys_Tables:

CODE
TID  T             FriendlyT
  0  All       All
100  t_People       People
110  t_Addresses   Addresses
115  t_AddressLink People Addresses
116  t_AddrLines   Address Lines
120  t_Phones       Phones
130  t_eAddresses  Email Addresses
140  t_Websites       Websites
145  t_WebLink       People Websites
150  t_Lists       Lists
155  t_ListLink       People Lists
200  t_PpLink       Relationships
300  t_Types       Types
700  t_Notes       Notes


here are some records from the Types table:

CODE
TypeID  Typ        Ordr  TID
104     Vacation         120  
19      Alternate  30    120
46      Home       20    120
21      Work       15    120
28      Day         5    120
29      Evening    10    120
18      Primary    25    120
40      Aunt      125    200
35      Family    100    200
36      Mother    105    200
37      Father    110    200
39      Daughter  120    200
110     Consultant       200
41      Uncle     130    200
42      Company   500    200
43      Employee  505    200
44      Sister    135    200
47      Brother   140    200
38      Son       115    200


Referencing usys_Tables, you can see these are types for Phone or Relationships. The Sort Order on lists is: Ordr (nulls will be last) then Typ, so Ordr is only filled if you want to specifically change the way the choices are displayed.

*** Popup Calendar ***

There is a popup Date Picker with optional time function. The time function was written by Mark Davis. The time controls show if the Format property of the active control includes 'Time' or 'h:' or is formatted as 'General Date'.

Double-click the hours or minutes to make fast changes to the time. The +/- toggle determines wherther time is added or subtracted.

If you wish to use the popup calendar in another database, simply import the for form 'f_PickDate' and make sure you have a reference to the Microsoft ActiveX Data Objects 2.1 Library

*** Basic Functions ***

The mod_crystal_code_070602 module has several basic functions and I have tried my best to comment them so that you can learn! This would be a handy module to import into any database.

*** Library References ***

To use this database, you will need references to:
Microsoft ActiveX Data Objects 2.1 Library
Microsoft DAO Object Library (3.6 was used for development)

*** Useage ***

People can be Humans or Companies (artificial entities) -- both have need to relate to phone, email, address, etc. If the Human box is checked on the main form (default value), then additional controls such as Firstname, DOB, and Gender show.

Each form and subform has a '+' button to add records, '-' button to delete records and a '' button (it actually has a music note but it won't display here) to attach a Note.

The StatusBar text property has been filled out for all controls (think I got 'em all <smile>) and additional information about each control is listed.

Double-click any date to popup the calendar. If the control collects a time as well  such as the Action Date control on the notes form (for scheduling appointments), the popup calendar will show time controls.

Some controls, like names and addresses, will have the case converted to ProperCase if the 'Convert Case' checkbox is true. Rob Richards (R_Cubed) has written a very comprehensive program for converting case that takes into account all kinds of anamolies (Uppercase converter in the UA Archives), but the function in this database is simple. If it doesn't work right on a something you are entering, simply uncheck the box and make the change  then check the box again after you are out of the control.

The Address structure has been broken down into Number, Street, StreetType, Suite/Apt, and Direction. If you are converting data, there is no reason that you cannot put your whole address line into the Street field if desired.

For those addresses that do not fit nicely into the provided structure, an additional table, AddressLines, has been created. You can enter as many additional lines as you wish!

Because I don't like gray buttons, I have used labels and overlaid them with transparent command buttons. In my personal adaptation of this application, I also set the button style on the Tab Control to None and add a row of labels to switch between tabs. I comtemplated doing this on what you see as well but decided against it as it makes changing things a little harder when you go into design view of the form.

People and Addresses were the trickiest part of this database because it is a many-many relationship.

The form to add addresses has an extensive search feature on the top of the form so you can see if the address you are looking for is already in the database before adding it (possibly again). There are no checks against this happening, so look before you add.

Websites is also a many-many relationship with People and the table to link them also has a place to fill out username in case the website is a forum like UtterAccess. There is a HasUsers checkbox for websites if this is the case.

When you pop up the Addresses or the Websites form, you can choose to Close & Update (information will be added to the main form) or simply Close.

Ideally, the Phones table would be set up as a many-many with People also, with a link table between Phones and People but I decided to, instead, store duplicate phone numbers.

When you set up relationships between People, define them from the Company record as opposed to the Human record (if they involve a company) as this assumption was made on some of the reports.

*** Sorting ***

Any label that is in italics can be double-clicked to sort by that field in a form.

*** Reports ***

Reports have been based them on queries (even though this is not neccessarily what I normally do)  so don't delete the queries! The Reports are more for showing different types of layouts than to be all you will need so you will have to set up additional reports.

One report I want to specifically mention is the Birthday Calendar report. Instead of using Access, an HTML document is generated. Files are placed in the same directory as your database. Once the HTML calendars are generated, an index page is created with all the HTML files in the directory.

*** Sample Data ***

There is LOTS of sample data in here so you can play and learn. There are 650 People records, 1000 Address records, a couple hundred Phone and eMail records, a few websites, and scattered Notes. If you wish to quickly remove all the sample data, temporarily choose Cascade Delete on all the link lines and then delete records from the the following tables: People, Addresses, Lists, Websites, and Types (although you may want to keep some of the records in Types).

This data was created using random number generators and lists of common names and streets, so any similarity to real folks is purely coincidental!

*** Access 97 ***

Although this database is saved in Access 2000 format, it can be converted to Access 97. You will need to go through my crystal_code module. For everything that won't work in Access 97, alternate code has been written but the Exit statement is above it. So you will need to comment the lines for the later version as well as the Exit Sub/Function statement.

There may be minor things to change in other modules as well -- not sure if Access 97 had Application.Followhyperlink  if not, just comment that code out and you will need to open web pages using Windows Explorer or My computer.

You will also need to search for CurrentProject.Path and tell it to, instead use the GetCurrentPath function.

You will also need to check Library References  uncheck the MISSING reference to the 3.6 DAO Library and check the DAO Library for Access 97.

*** International Formats ***

I apologize for using an InputMask on Phone Numbers for the American style as well as collecting the Zips for American addresses. These are things you can change if necessary.

I have also filled out date formats in many places. You will need to check these as well.

*** Acknowledgements ***

Most of this code was written by me, but there is some code and features developed by Mark Davis (CyberCow) --Time picker on the popup calendar -- and Brent Spaulding (datAdrenaline) -- IsTable function and a procedure to sort an array, SortStringArray.

I wish to especially thank Shane Sullaway (StarsFan), who taught me about making forms look nice and designed the basic color scheme you see used.

Thank you also to the many people who have influenced and inspired me!

*** Enjoy ***

For those of you that know me, you know I have been working on this off and on for a couple years! I am happy to be able to share it with you and appreciate hearing your comments.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

May 2010

The attached database and description above is unchanged since June 2007.  I am always working on this type of information -- massaging the structures to be practical and quick.  Many changes since then ...

