12.3. Customer Information

The customer information form is probably most easily created by first creating the table to hold the necessary information and then using the Form Wizard from Access. Without complicating matters too much, your first decision is whether to hold all of the customer information in one table or having it in multiple tables. For example, you can have a table that holds only customer names, along with a unique identifier for the customer. You would also have address information, phone information, and company contacts in separate tables.

That approach is preferable for a number of reasons. First, many companies (and even people) have a number of phone numbers. If you designed a database in the mid-1980s, you would probably have only one phone number. When fax machines became popular, if you did not have a separate table, you would have needed to add a field to your original table to hold a fax number. Now, think about pagers, cell phones, conference call numbers, etc., and you can see how this would cause a lot of design work. If you have a separate table to hold phone information, you can add new phone numbers and new types of phone numbers with no programming. Some programmers disagree with this approach, saying that it makes it difficult to do a mail merge, print information on reports, etc. However, I solve this problem by simply having a checkbox on each phone record to signify whether it is the default phone number. You can do the same thing for addresses and company contacts.

It is rather easy to implement a form that links the separate tables of customer information. First, create simple forms that hold phone numbers, addresses, etc., making sure that you have a field with the ID number from the main customer table in each of the other tables. Next, create your form for the customer information and drag one of the other forms onto the customer information form. Go to the properties of this subform and set the child and master fields to be the ID field from the customer table. This tells Access to show only records related to the current customer.

Here is a tip: if you don't want to show all of the information forms at the same time, have the default show one of them, and have buttons for each subform. For the OnClick event for each button, change the SourceObject property of the subform to be the name of the form that relates to the button. For example, if you have a button to show addresses, and the address form is called frm_Address, your OnClick event would be the following code. The subform on this form is called multiSubForm so that you realize it is designed to show multiple forms. Also, it is best if you keep the size of these forms the same so that it looks clean.

 Private Sub Command5_Click(  ) Me.multiSubForm.SourceObject = "frm_Address" Me.multiSubForm.LinkChildFields = "CustomerID" Me.multiSubForm.LinkMasterFields = "CustomerID" End Sub 

This method lets you use one form to put in all the information. You could also use a tab control and have a subform on each tab, but I like this method better. In the end, you need to decide what works best for you and the end user.