Usually, the main form is bound to the table on the one side of the relationship, and the subform is bound to the many side of the relationship.
Suppose instead, that you want to design a form that is bound to a table that contains information about customers. Some of this information is unique for each customer, but some of the information is not. The names and addresses of the customers are unique, but perhaps you want to store information about the shipper that is normally used for deliveries to each customer. There are only a few shippers, and it would be redundant and in violation of relational database design rules to store information such as the addresses of these shippers in the customer records.
Instead, you should create a Shipper table, and use a Shipper Code field to create a link between this table and the Customer table. You should only store the shipper code in each customer record in the Customer table and store all the other information about the shippers in the Shipper table.
This is the many side of a one-to-many relationship. Each customer can be associated with only one shipper, but a shipper can be associated with many different customers.
A main form or subform structure is not applicable in this scenario. (Although it would be applicable if you were to design a form to display information about the shippers. The subform could then display a list of the customers that use each shipper.)
Creating the Customer form
There are two things to consider when creating the Customer form (and table):
-
Do you want an easy way to enter the shipper code?
-
Do you want to validate the Shipper Code field in the Customer table against the Shipper table? That is, do you want the system to verify that the contents of the Customer table field are present in the Shipper table?
If you do not establish a relationship to the Shipper table, you will need to remember the shipper codes. This means that you can easily enter a code that does not exist in the Shipper table. If the tables are related, a lookup function is provided for the Shipper table, so that you can press F6 or click a lookup button and select the shipper code from a list that displays the codes as well as other information, such as name and address.
A control can be related to a field in another table in two different ways. The first is by defining the relationship at table level, as a property of the Shipper Code field in the Customer table. The second is by defining the relationship at form level, as a property of the text box that displays the shipper code on the Customer (main) form.
If you want to ensure that you do not enter non-existent shipper codes into the Customer table, you can validate the entries against the Shipper table. The ValidateTableRelation property, either for the field (at table level) or for the text box (at form level), determines whether the values you enter must exist in the Shipper table.
Apart from simply ensuring that the entered codes exist in the Shipper table, you can create more advanced validation rules that check the entered codes against combinations of values for fields in both tables. For example, you can have the system check whether the shipper allotted to a customer operates in the customer's country/region. To do this, you have to create the validation rule by writing C/AL code in the OnValidate trigger of the control on the main form.