The simplest form (other than a menu-only form) can show data from a single table. But often you want to show information from two related tables. For instance, you might have two tables, one containing details of suppliers, and the second details of products that each supplier provides. These would be related on a one-to-many basis - each supplier can have multiple products.

Suppose that the tables are called Supplier and Product respectively, that the primary key column in the Supplier is called SupplierID, and that this links to a column in the Product table also called SupplierID. Just to be clear, if we were writing an SQL query to retrieve supplied and product details, the query would look something like:

select ........
    from Supplier, Product
    where Supplier.SupplierID = Product.SupplierID

OK, first step is to create a new form. The top-level block type is set to Table block, and is set to retrieve data from the the Supplier table. Assuming we want to see one supplier and each of their products at a time, the block should be set to show just one row, by setting the Row Count property to one. Then add whatever data from the Supplier table we want the user to see - supplier name, address, whatever. That covers the supplier.

Now add a sub-form. Sweep out a suitable area on the form, and select New Block/Table Block from the popup menu; this creates a nested table block. The first properties dialog will specify where this block should get its data from, in this case the Product table. OK'ing this dialog will lead to the properties dialog for the (nested) table block itself; this is the same as the properties dialog for the main block, but with the addition of two properties (well, more than two, but these are the important ones), namely Parent Column and Child Column. These two properties specify the way the two tables as linked; the first identifies a column in the table associated with the outer block (in this case, the Supplier table), while the second identifies the column in the table associated with the nested block (in this case, the Product table). In this example, both are set to SupplierID, but this really means Supplier.SupplierID and Product.SupplierID respectively. Lastly, you might (but don't necessarily have to) want the nested block to display more than one row of data - this is again the Row Count block property.

That is pretty well it. You can add data from the Product table to the nested block. When you save the form and execute it, the nexted block (ie., the subform) will show only products associated with the currently displayed supplier.

This scheme also works if you have a one-to-one relationship between the two tables. The difference in this case is that you don't want the user to be able to enter more than one record into the nested block. To do this, we can use the OnAction event of the nested block. This is called immediately before an action like Previous Record or Next Record is called; if it returns zero then the action does not take place. Here is the code:

def eventFunc (block, action) :
    if action == block.actFirst    : return 0
    if action == block.actPrevious : return 0
    if action == block.actNext     : return 0
    if action == block.actLast     : return 0
    if action == block.actInsert   : return 0
    if action == block.actAdd      : return 0
    return 1

The code traps all actions that might lead to a new record, and stops them.

Copyright © by Total Rekall- An alternative to Microsoft Access All Right Reserved.

Published on: 2003-09-06 (895 reads)