ACC95: Yes/No Field Causes Error in AutoLookup Query

Last reviewed: May 22, 1997
Article ID: Q142540
The information in this article applies to:
  • Microsoft Access version 7.0

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you add a record in a form (or query datasheet) bound to an AutoLookup query, you may receive one of the following error messages:

   Current field must match join key '?' on 'one' side of one-to-many
   relationship because it has been updated.

   -or

   To make changes to this field, first save the record.

CAUSE

The AutoLookup query contains a Yes/No data type field from the "one" side table. For Yes/No fields, Microsoft Access automatically enters a 0 (zero) as the default value.

Because the Yes/No field is from the "one" side table and has a hidden default value, Microsoft Access assumes that you are trying to add a record to the "one" side as well as to the "many" side, which generates the error messages.

RESOLUTION

There are two possible workarounds for using a Yes/No field in an AutoLookup query. Your choice depends on whether you need to modify the Yes/No field in the "one" side table.

Method 1

If you want to add records to the "many" side table, but only display (not modify) the Yes/No field from the "one" side table, you can work around the hidden default value for Yes/No fields by using an expression in the query grid. For example, you can replace a Yes/No field reference such as

   FieldName: MyYes/No

with the expression:

   FieldName: Expr1: [MyYes/No]+0

Because an expression is used to output the field instead of a direct reference, Microsoft Access does not try to set the value of the Yes/No field in the "one" side table when you add records to the AutoLookup query. A side-effect of this workaround is that you cannot modify the Yes/No field in the "one" side table.

Method 2

CAUTION: This method involves modifying the design of the "one" side table. If your application has multiple queries, forms, reports, and modules based on the table, this change may affect their behavior. To avoid potential side-effects caused by the design change, you should use Method 1 as the resolution for this issue.

If you need to modify the Yes/No field in the "one" side table and add records to the "many" side table, you can change the field's data type from Yes/No to Number. Then, you can set the field's FieldSize and Validation Rule properties to simulate the behavior of a Yes/No data type field. To do so, follow these steps:

  1. Open your "one" side table in Design view.

  2. Select the Yes/No field that you included in your AutoLookup query.

  3. Change the field's properties to the following settings:

          Table: Sample
          -----------------------
          FieldName: SampleField
          DataType : Number
          FieldSize: Integer
          ValidationRule: 0 or -1
    

  4. Close the table and save the design changes. If you receive a "Data integrity rules have changed" message, click Yes to test your existing data against the new validation rule.

NOTE: If you have an existing form that has a check box, option button, or toggle button for entering data into the Yes/No field, the form control should continue to work properly after you change the field's DataType, FieldSize, and ValidationRule properties as described in step 3.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access 7.0. This problem no longer occurs in Microsoft Access 97.

MORE INFORMATION

Steps to Reproduce Problem

  1. Open the sample database Northwind.mdb.

  2. Open the Order Details Extended query in Design view.

  3. Add the Discontinued field from the Products table to the query grid.

  4. Run the query.

  5. Enter the following values in a new record:

          OrderID: 11077
          ProductName: Chai
          UnitPrice: $18.00
    

    Note that you receive the error message in the "Symptoms" section.

  6. Press the ESC key twice to undo the new record.

REFERENCES

For more information about AutoLookup queries, search for "AutoLookup queries," and then "About AutoLookup queries that enter data automatically" using the Microsoft Access 97 Help Index.

For information about the "Current field must match join key '?' on 'one' side of one-to-many relationship because it has been updated" error message in Microsoft Access 2.0), please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q116062
   TITLE     : ACC2: Adding Records to AutoLookup Form Generates Error

   ARTICLE-ID: Q148410
   TITLE     : ACC95: Microsoft Jet Database Engine 3.0 Reserved Errors
               List


Additional query words: #3341
Keywords : kberrmsg kbusage QryFixup
Version : 7.0
Platform : WINDOWS
Hardware : X86
Issue type : kbbug
Resolution Type : kbfix


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 22, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.