Displaying Data From Related Tables on a Form
Today's guest blogger is Access MVP Scott Diamond. Scott is the principal of Diamond Computing Associates and the author of Microsoft Office Access 2007 VBA.
This is a very frequently discussed issue. But before I get into the methods, you need to understand one of the principles of relational databases. That principle is that data should exist in one place only. Having the same data in multiple tables is a violation of normalization. Related records are indicated by a Foreign Key within the record that holds the Primary Key value of the parent record. So when you want to have data from multiple tables on a data entry form, you set it up to display the data not store it in the form’s RecordSource.
There are basically four ways to display related data on a form; Subforms, the Column property, DLookups and Listboxes. Ill discuss each in turn and suggest where to use each.
Subforms
You can use a subform to display several fields from the related table. Create the form using the Subform wizard or create a separate form and place it on the main form as a subform (I generally create a separate form). Using the wizard, you go through the following steps.
- Select whether to use an existing form or create a new one
- If you are creating select the table and fields to use
- Select the linking fields, usually accept the defaults Access proposes
You can customize the subform, so it looks like part of the main form, by removing record selectors, navigation buttons, borders etc. I use subforms when I want to display 4 or more fields from the related record. Another advantage of using subforms is where you have a One to Many relation. Using a Continuous Form or Datasheet View, you can display multiple related records at once.
Column Property
Generally Foreign Keys are entered by selecting the related value from a combobox. The combobox uses a query as it Rowsource. This query displays the records from that parent table. At the least, the query includes the primary key field as its bound column and a description field. However, you can add as many other fields from the table as you want. These fields can then be reference using the Column property. Click the Build button […] next to the Rowsource property to enter Query Design Mode. In Query Design Mode you can add tables and fields to the query. You can control what fields actually display in the pull down list by setting their Column Width. Setting the width of an individual column to 0" will hide that column (Note: Column widths are entered separated by a ; for each column listed in the column Count). The combobox will only display the first non zero length column after selection. The following properties of a combo are key to using combos in this way: RowSource (the list), Bound column (what's actual stored), Column Count (how many columns in the list, Column Widths (the size of each column in the list).
You can then set the ControlSource for an unbound control to:
[comboxname].Column(x)
Where comboxname is the name of the control and x is the number of the column in the query for that field. Note: the column count starts with 0 so the 3rd column is 2.
Since the combobox selects a single record, the Column property will also reflect a single record. I use this method if I need to display 3 or less values from the related record.
DLookups
DLookups allow you pull a value from a field in a specific record. It uses the syntax:
DLookup([fieldname],table/queryname,Criteria).
The Criteria is used to specify the record you want to return. Since the Comboxname will store the Foreign Key value you would use a criteria like:
[keyfield] = & [Comboboxname].
This would also be used as the controlsource of an unbound control. Each DLookup should only be returning data from a single record. If its possible that the DLookup might not find a matching record you should use it within a NZ (NullZero) function to prevent errors. I use DLookups when I need to pull data from different tables based on a key value.
Listboxes
A Listbox can have multiple columns with column headers. It also can be set to display multiple matching records. I will, sometimes, use a Listbox in place of a continuous form or datasheet subform. Listboxes will also display multiple matching records.
There are two exceptions to the rule of not repeating data in multiple tables. The first is the PK value. Obviously, that value has to be repeated as the FK to relate the records to each other. The other exception is time sensitive data. Sometimes you need to freeze data that will change over time. The best example of this is price data. For example: In an order entry application, you want to freeze the price at the time of the order. In such a case, you would have the Price field repeated in the OrderDetails table. Generally you would use the Column property for this and populate the control in the After Update event of the Products combo use code like:
Me.txtPrice = Me.cboProduct.Column(2)
These guidelines should help you build forms that preserve normalization and are well organized and easy for the user to use.
