ACC1x: Parameters Expected When Navigating Form with Sub-subform

ID: Q113545

The information in this article applies to:

SYMPTOMS

While navigating a main form that contains a subform that contains another subform, you receive the following error message:

3 Parameters were expected but only 1 were supplied


CAUSE

A common field is being used in the LinkChild and LinkMaster properties to link the main form to the subform and the subform to its subform (the subsubform). When Microsoft Access tries to evaluate the common link field to filter the records in the subforms, it may try to evaluate the links in an incorrect order, which leads to the error message described above.


RESOLUTION

This problem does not occur in Microsoft Access version 2.0.

There are two ways to solve this problem in versions 1.0 and 1.1:

  1. Create a single, unique, key field (perhaps a counter field) in the subform table that can be used to link to the subsubform table, rather than linking on multiple fields.


  2. Base the subform and the subsubform on a query that has a calculated field that combines the linking fields into a single field. This single calculated field can be used in the LinkChild and LinkMaster properties of the subsubform control to link to the subform. This eliminates the common field from being defined in both links.


See the example below, which demonstrates how to use both techniques to solve this problem.


MORE INFORMATION

The following example demonstrates the problem described above, and demonstrates the techniques to solve the problem.

Note that this problem is very difficult to reproduce. Following the steps below may not be sufficient to cause the problem to manifest in the sample database NWIND.MDB.

Suppose that you have common Orders and Order Details tables to take orders for your business, similar to the design in the sample database NWIND.MDB. The Orders table stores information about an order and the Order Details table stores information about the "many" products ordered on "one" order. The tables might have the following important fields (among others): 


   Orders
   ----------
   *Order ID
   Order Date


   Order Details
   ----------------
   *Order ID
   *Product ID
   Unit Price
   Quantity Ordered

NOTE: An asterisk (*) denotes a primary key field.

Your business is run such that you can fulfill an order by making multiple shipments of products to the customer for a given order. (You may be out of stock of some products and will ship the remainder of the order separately.) You need to track how many of each product was shipped on a given date for a given order. To do this, you create an additional "many" table to store the dates and the quantities shipped per item on a given order. You create the following table: 

   Order Details Shipped
   ---------------------
   *Order ID
   *Product ID
   *Date
   Quantity Shipped

To view this information, you create a main form based on the Orders table. You create a subform to view information about the "many" products ordered from the Order Details table linked on the Order ID field. You create a subsubform to view information on the "many" shipments made for each product on the order from the Order Details Shipped table linked on the Order ID and Product ID fields. Your form links look like the following: 

   Main form     Subform              SubSubform
   Orders        Order Details        Order Details Shipped
   ---------     -------------        ---------------------
   Order ID  ->  Order ID         ->  Order ID

                 Product ID       ->  Product ID

In this design, the Order ID field is being used to link both the main form to the subform, and the subform to the subsubform. This can cause the error described above when you navigate the records on the main form.

Solution 1

The best way to solve this problem is to create a single, unique key field (perhaps a counter field), in the subform table that can be used to link the subsubform table, rather than linking with multiple fields.

Using the example above, add a field called Order Detail ID with a Counter data type to the Order Details table. The table will look like: 

   Order Details
   ----------------
   Order Detail ID
   *Order ID
   *Product ID
   Unit Price
   Quantity Ordered

You can then link a product to the many shipment records in the Order Details Shipped table using this single field: 

   Order Details Shipped
   ---------------------
   *Order Detail ID
   *Date
   Quantity Shipped

The links between the main form and the subforms would look like the following: 

   Main form     Subform              SubSubform
   Orders        Order Details        Order Details Shipped
   ---------     -------------        ---------------------
   Order ID  ->  Order ID

                 Order Detail ID  ->  Order Detail ID

The difficulty with this solution is that implementing it on an existing database with a lot of data may require some effort. The second solution, although less preferred, solves the problem at the form level rather than the table level.

Solution 2

This solution involves another way of eliminating the use of a common field in the links between the main form and the subforms by using a query. To use this solution, create two queries. One of the queries will be based on the subform table, and the other will be based on the subsubform table. Include all the fields from the underlying table in each query. In an empty column on the query grid, create a calculated field that combines the values of the linking fields.

Using the example above, place the following in an empty Field row on the query grid: 

   OrdProdLink: [Order ID] & [Product ID]

You can now alter the LinkChild and LinkMaster fields between the subform and the subsubform to use this single calculated field. The links between the main form and the subforms would look like the following: 

   Main form      Subform              SubSubform
   Orders         Order Details        Order Details Shipped
   ---------      -------------        ---------------------
   Order ID  ->   Order ID

                  OrdProdLink     ->   OrdProdLink


REFERENCES

Microsoft Access "User's Guide," version 1.0, Chapter 6, "Designing Select Queries," pages 126-127

Microsoft Access "User's Guide," version 1.1, Chapter 6, "Designing Select Queries," pages 130-131

Microsoft Access "User's Guide," version 1.0, Chapter 10, "Creating Forms Based On More than One Table," pages 275-276

Microsoft Access "User's Guide," version 1.1, Chapter 6, "Designing Select Queries," pages 275-276 


Keywords          : kbusage FmsSubf 
Version           : 1.0 1.1
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: April 3, 1999