Microsoft Access subforms are a proven useful way of showing multiple records held in your database system. However, you could have a customer record with no orders at all. You would not want to have orders which have no related customer record. When you think about this in the real world it holds true. However, the reverse is not true in that deleting a record in the main form will remove any related records in the subform. It is worth noting that if referential integrity and cascading delete has been enforced between the linking tables then deleting a record in the subform will not affect the record in the main form. It is also possible to create multiple subforms in MS Access linked together by a common key, although this can get quite tricky. Without this the main and subform will not function properly. It is vital that the relationship between the tables is created correctly. Link master will contain the primary key of the table behind the main form and link child will contain the foreign key in the table behind the subform. We are interested in the link child and link master properties. By right clicking on the subform we can view its properties. The main and the subform have to be linked somehow and this is normally done via the primary key of the table behind the main form and the foreign key in the table behind the subform. In this case we could show order1, order2, order3 and so on. When creating subforms in MS Access, it is common to display them in a grid type format to show multiple records.
The main form would show one customer record while the subform would show many orders relating to that customer.
As an example we could say a customer would have many orders. The main form would model the one part of the relationship and the subform would take on the many side of the relationship. It is common for a main and a subform to model what is known as a one to many relationship. Although many types of forms are now available, the two commonly used are the main form and also subforms in MS Access.īefore creating the forms you should first design your database and create relationships between the tables. They can be made read only, but generally are used for entering information. Forms are another way of describing screens.