Home

Showing Data on a Form From a Related Table

This is a common requirement in a database application. Here are a few random examples:

• You rent cars. You have a form based on a Hires table, which uses the vehicle’s Registration Number to identify which car is being rented. But you want the car’s description to also show on the form, and this information is in another table (Vehicles).
• You are running a sports tournament for seeded players. You are using a form to enter playing schedules. But you want the seed rankings to also show on the form, and this information is in another table (Players).
• You are a plumber. You have a form for creating a Quote for a customer, based on the CustomerID. But you want the customer’s address and phone number to also show on the form, and this information is in another table.

Here are four commonly used approaches to this…

Query

Sometimes the simplest and most efficient way to do it is base your form on a query that includes both tables. This means you can include the required field in the query, and hence directly on the form, bound to the field from the query. For example, suppose you have a form for entering Members for a club, and each member has a membership Grade, and each Grade has a different subscription Fee. Suppose, for some reason, you want to show the applicable Fee on the Members form. Make a query, something like this…

Assign this query as the Record Source of the form. As soon as you enter a Grade for a Member, the corresponding Fee will be automatically displayed.

In a case such as this, the Locked property for the Fee textbox should be set to Yes – this is not an appropriate place to allow editing of data in the Grades table!

Concatenation

It may be suitable to show the related data together with the base data. Using the same example as above, the Row Source property of the Grade combobox could be set to a query, like this…

… and the Properties of the Grade combobox set up like this…

Note in particular that the first column of the query is hidden in the combobox by setting its width to 0 in the Column Widths property, so it is the concatenated field which will be displayed. However, the value of the Grade field is still the one to be stored in the table, as per the setting of the combobox’s Bound Column property.

So, the form can be based directly on the Members table, it does not require a query. And the data on the form will be seen like this…

Column Property

Again, we can use a multi-column Row Source for the Grade combobox. But this time, simply the Grade and Fee fields from the Grades table…

And the combobox’s Properties like this…

In this case, the Grade is still the Bound Column. And it is the Fee that is hidden by its Column Width being set to 0. But it is important that the Fee field is included in the Row Source query.

Now, to see the Fee associated with the selected Grade, put an unbound textbox on the form. We will put an Expression into the Control Source property of this textbox, to reference the value of one of the columns in the combobox’s Row Source. In many cases, it is simple a matter of an expression like this:

=[NameOfCombobox].[Column](x)

… where ‘x’ is the ordinal number of the column (remembering that the numbering starts at 0). But in our example, if we want it to be displayed as currency, we have to specifically format it as such. So, the Control Source setting of the Fee textbox will be:

=Format([Grade].[Column](1),“Currency”)

Again, the form can be bound directly to the Members table, and it will appear exactly the same as the first example above. But the means of displaying the Fee information is quite different.

DLookup Function

For this final method, the Grade combobox’s Row Source can just be one column. In this case, we might set its Row Source property to a SQL statement:

SELECT Grade FROM Grades ORDER BY Sort

Again, to see the Fee associated with the selected Grade, put an unbound textbox on the form. We will put an Expression into the Control Source property of this textbox, in this case to reference the value of the Fee field directly from the Grades table. In this case, the display as currency can be controlled via the Format property of the textbox (yes, I know this is inconsistent with the above!). And the Control Source expression for our example is:

=DLookUp("[Fee]","Grades","[Grade]='" & [Grade] & "'")

Again, the form can be bound directly to the Members table, and it will appear exactly the same as the first example above. But the means of displaying the Fee information is quite different.

Conclusion

The concepts explained above, using this very simple example, can be adapted to many scenarios where there is a requirement to see data from a related table on a form.

Steve Schapel