The process begins with defining the main form. Rather than using one big table with all the information in one place, trying to guess at all the fields that might be needed or not, begin with a main table that defines the most basic information. Returning to the contact management database example we began in November 2018 (http://bit.ly/2OPWXaX), the main table can include various name fields as well as other identifying information. Then use related tables for associated information, such as addresses or phone numbers.

Next, create a form to enter data for each table, such as the one discussed in the November column. That form contains several links for phone, email, URL, and list information. Those are actually command buttons that open up pop-up forms for entering data. As a general rule, each data entry form should be based on just one table. Therefore, the main form is used to enter contact information. For each related table, a pop-up form is created that shows records for the selected contact and allows new data to be entered.

Figure 1 shows part of the main form for contacts. When one of the underlined commands on the left is clicked, a pop-up form opens to show data related to the selected contact. Figure 2 shows the form that appears when you click Addresses.

Figure 1
Figure 2

This contact, Sophie Acorn, has a primary address that’s completely known and a vacation address that’s somewhere in Colorado. A contact that is a company may have several addresses, such as a primary address for the physical location and different addresses for billing, mailing, warehouses, and so forth. By collecting each address in its own record, whatever is needed can be entered. The user can indicate a type for each address so that it can be used when appropriate.

When new records are created in a related pop-up form, they’re associated with the contact displayed on the main form by setting the Default Value property of the CID control (the foreign key field relating to CID in the contacts table) to =[Forms]![f_CONTACTS]![CID], which is a reference to the CID control on the main form.

On the main form, the numbers to the left of the command buttons indicate how many items of each the database has for that contact. Sophie has two addresses, two phone numbers, one email address, and one URL. More of each can be added whenever you learn the appropriate information.


VBA CODE

Each command link uses the same logic to open a related pop-up form. Here’s the VBA code that runs when the Addresses button is clicked 

Private Sub cmd_Addresses_Click()

‘edit Address(es) for contact

Call OpenRelatedForm

(“f_Contact_ADDRESSes_pop”)

End Sub

The form name to open, f_Contact_ADDRESSes_pop, is passed to the OpenRelatedForm procedure, which determines the current contact and opens the form name passed to it with a filter for the current contact. This is the code that executes:

Private Sub OpenRelatedForm

(psFormname As String)

Dim sWhere As String

‘Me is the form we are behind

With Me

‘if record has changes, save them

If .Dirty = True Then .Dirty = False

If .NewRecord = True Then Exit Sub

‘nothing to edit

‘construct criteria

sWhere = “CID=” & .CID

End With

‘open specified form and set its filter

DoCmd.OpenForm psFormname, , , sWhere

End Sub

 

First, a variable named sWhere is dimensioned (i.e., defined). This variable will hold the string for the criteria. The next section of code—between With Me and End With—looks at specific properties of the form this code sits behind and then sets the criteria string.

The form properties are the words that begin with a period. If the record has unsaved changes, the Dirty property of the form will be true, so the record is saved by setting Dirty to false. And if the NewRecord property is true, this means the user is on a new record and there’s no contact to assign any data to, so the code will exit.

Next, the value of sWhere is defined. In the case of Sophie Acorn’s record, the contact ID is 31, so the criteria string will be CID=31, where CID is the field name of the primary key field for the Contact table as well as the foreign key field name in each of the related tables.

Finally, the OpenForm action opens the form specified by the psFormname parameter that is passed, and the specified criteria is sent for the WhereClause argument. The extra commas are for parameters that get skipped since they don’t need to be specified. When the related pop-up form opens, it will display records for the specified contact if they already exist and allow new records to be created.

Download this month’s database: sf1901_PopUpForms.

 

SF Says: Use pop-up forms to collect and display related data.

About the Authors