Access: Cross-Reference Contacts and Lists

TABLE RELATIONSHIPS

Figure 1 shows part of the relationships diagram for tables in the contact database. The table names all begin with “c_” since they are just a part of the bigger contact management system. Using a naming pattern like this helps to differentiate (and group together) tables and other objects within a large database.

The c_Contact table contains records for people you know and work with. The primary key is CID, which is an autonumber. The table also contains a CID_ field, which is used, when applicable, to indicate a company, organization, or head of household to which the current contact is connected. (See last month’s column, “Managing Contacts,” at http://bit.ly/2OPWXaX.)

The c_List table contains the categories for grouping and organizing the contacts. Lists can be whatever you want—people you want to send cards or gifts to; members of a club you belong to; business associates; those you share common interests with, like music, art, golf, and Access; and so forth. The primary key of the c_List table is ListID.

Similar to CID_ in the contacts table, ListID_ is used to establish a hierarchical relationship with other lists. For instance, say you like to send out letters and greeting cards for various occasions. You create a new record with a ListName of “ToSend” that is assigned a ListID of 18. Then you make two more lists, “Holiday Cards” and “Birthday Cards,” to create lists specifically for sending holiday and birthday cards, respectively. Both of those lists would have a ListID_ of 18 so they’ll be associated with the “ToSend” list.

Now that we have a way to keep track of contacts and lists, we need a way to cross-reference them so that (1) a contact can be on many lists and (2) a list can have many contacts. A common way to name a cross-reference table, which provides a many-to-many relationship, would be to call the new table a mix of the source tables like ListContact or ContactList. In this case, however, the cross-reference table is called c_Member because there might be related tables for members in the larger database. There’s also a unique index on the combination of the ListID and CID fields so that a contact won’t be duplicated on the same list.

MAIN FORM AND SUBFORM

A main form and subform are used to enter data into tables, as shown in Figure 2. The record source of the main form is the c_List table. To show multiple contacts in a list, a continuous subform is used that is based on the c_Member table. There are command buttons in the form labeled 1 and 2 that let you swap between two versions of the subform. The numbers are underlined on the form because the hot keys for them are Alt+1 and Alt+2. To add a hot key, open the Property Sheet for the command button. In the Caption property, add an ampersand in front of the underlined character, for example, &1 for the first button.

The first subform (displayed in Figure 2) has places to specify contact, active status, and notes. You can also see the number of contacts in the list displayed in the navigation area of the subform at the bottom-left. For example, note that the current list shown in Figure 2 contains 94 contacts.

For a greeting card list, not much else needs to be tracked. For a more complex list, such as members of a forum or people who have blogs on a topic you follow, you can use the second subform. Press Alt+2 (or click the other command button) to display it. This subform includes more detail, such as username, website or profile link, and member type.

SWAP SUBFORMS WITH VBA

The two subforms that we’re toggling between are the “f_List_Members_1_sub” and “f_List_Members_2_sub” forms. VBA code is used to let users swap between the two subforms. Both are based on the c_Member table and linked to the main form using ListID. Here’s the code for button 1:

Private Sub cmd_ListMember1_Click()

With Me.f_List_Members_sub

If .SourceObject <> "f_List_Members_1_sub" Then

.SourceObject = "f_List_Members_1_sub"

.LinkChildFields = "ListID"

.LinkMasterFields = "ListID"

End If

End With

End Sub

When you click the command button or press the hot key, the code checks to see whether the desired form is displayed. If it isn’t, the form name is specified in the Source Object property of the subform control, and LinkChildFields and LinkMasterFields are set.

Download this month’s database: SF1218_CrossRefTable.

SF SAYS

Use a cross-reference table to create a many-to-many relationship for your tables.