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:
CAUSEA 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.
MORE INFORMATION
The following example demonstrates the problem described above, and
demonstrates the techniques to solve the problem.
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: 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: 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 1The 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: You can then link a product to the many shipment records in the Order Details Shipped table using this single field: The links between the main form and the subforms would look like the following: 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 2This 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: 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: REFERENCES
Microsoft Access "User's Guide," version 1.0, Chapter 6, "Designing Select
Queries," pages 126-127
Keywords : kbusage FmsSubf |
|
Last Reviewed: April 3, 1999 © 2000 Microsoft Corporation. All rights reserved. Terms of Use. |