Download Project Files and Database

More often than not, the primary key field of a database table is composed of a single field which is autoincremented whenever a new record has to be inserted. However, it is sometimes necessary to have a primary key field composed of multiple fields. Take for instance the example of a table containing a list of Companies, whereby each company has multiple Account records in a related table. Whereas the primary key field of the Companies table is a single autoincremented field, the Accounts table has a compound primary key field. The compound primary key field is composed of an AccountID as well as the CompanyID of the parent Company record:

The process of inserting records into the Companies table can be accomplished with an ordinary record form since the primary key is always generated by the database. However, when inserting new records into the Accounts table, a Custom Insert has to be performed. Before a record is inserted in the Accounts table, the form has to receive an input parameter specifying the CompanyID of the parent company which will then be combined with a user specified AccountID to make up the compound primary key.The implication here is that the Accounts record form cannot be accessed directly but rather has to be accessed via a link which contains the CompanyID of the parent company. One way to do this is to have a page with the record form for the Company records and also a grid form for the Accounts belonging to the company as shown below:

In this case, the grid form with the Account records has a Add new Account link which leads to the record form used to insert new Account records. The link defines an output parameter containing the CompanyID of a company so that the CompanyID will be accessible when adding a new Account record.

The link with the CompanyID URL parameter leads to the AccountEdit page which has a record form used to add new Account records. In this record form, the Custom Insert Type property is set to Table whereas the Custom Insert property specifies the fields to be used in the insert operation as shown below. Notice that whereas the AccountName and AccountID values are received from a form, the CompanyID value is received from the URL parameter specified above. As such, the user specifies the AccountID and Account Name in the record form then when the form is submitted, the CompanyID is retrieved from the URL and used to create the compound primary key.

We do not need to specify Custom Update or Custom Delete properties because once the AccountID and CompanyID values have been inserted into the database table, both values can simply be retrieved from the database when editing a record. The insert operation is unique because the CompanyID comes from a different table whereas the AccountID is specified by the user.

PS: When you use any of the builders to create record forms, you are allowed to specify only one primary key field per table. However, after the form has been generated, you can edit the Data Source property of the record form to add input parameters for other fields that make up a compound primary key.