ACC2000: Errors Adding Record to AutoLookup Form

ID: Q208564

The information in this article applies to:

SYMPTOMS

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

When you are adding a record through a form that is based on an AutoLookup query, you may receive one of the following error messages when you try to enter anything in the foreign key field:

To make changes to this field, first save the record.
-or-
The current field must match join key '?' in the table that servers as the 'one' side of one-to-many relationship. Enter a record in the 'one' side table with the desired key value, and then make the entry with the desired join key in the 'many-only' table.
-or-
Field can't be edited.


CAUSE

Default values are set on a control or controls bound to the "one" side table of an AutoLookup query, which acts as the record source for the form.


RESOLUTION

Make sure that the control or controls that are bound to the "one" side of the query do not have any default values set on the form.


MORE INFORMATION

When you enter a value in the foreign key field of an AutoLookup query, fields from the "one" side table should update automatically with related information. However, if you are adding a new record and one or more fields from the "one" side have a default value, Microsoft Access assumes that you are trying to add a record to the "one" side as well as to the "many" side.

If there are no default values for any of the "many" side fields, when you enter a value in the foreign key field after making an entry in another field from the "many" side table, you may receive the following error message:

<field name> field must match join key '?' on 'one' side of one-to-many relationship because it has been updated.
If there are default values for any of the "many" side fields, or if you have not entered any value in any field from the "many" side table, you will receive the following warning message in the status bar after you enter a value in the foreign key field:
To make changes to this field, first save the record.

Steps to Reproduce Behavior

  1. Open the sample database Northwind.mdb.


  2. Select the Orders table, and on the Edit menu click Copy.


  3. On the Edit menu, click Paste.


  4. In the Paste Table As dialog box, type Orders2 in the Table Name box, and then click OK.


  5. Open the Orders2 table in Design view.


  6. Change the OrderID data type from AutoNumber to Number.


  7. Save and close the Orders2 table.


  8. Create the following new query based on the Customers and the Orders2 tables:


    9. 
   Query: AutoLookup
   ------------------
   Type: Select Query

   Field: OrderID
      Table: Orders2
      Sort: Ascending
   Field: OrderDate
      Table: Orders2
   Field: CustomerID
      Table: Orders2
   Field: CompanyName
      Table: Customers
   Field: Address
      Table: Customers
   Field: City
      Table: Customers
   Field: Region
      Table: Customers
   Field: PostalCode
      Table: Customers
   Field: Country
      Table: Customers
   Field: Phone
      Table: Customers
  9. Save the query as AutoLookup and close it.


  10. Select the AutoLookup query, and on the Insert menu, click AutoForm.


  11. When the new form opens in Form view, on the View menu, click Design View.


  12. Select the CustomerID control, and then on the Format menu, point to Change To, and then click Text Box.


  13. Select the Region control, and set its DefaultValue property to 0 (zero).


  14. Open the form in Form view, and on the Records menu, click Data Entry.


  15. Try to type any value into the Customer text box. Note that the following message appears in the status bar:


    16. To make changes to this field, first save the record.
  16. Press the ESC key twice to undo the new record.


  17. Type 1234 in the OrderID text box. Press the TAB key twice to move to the Customer text box, enter a value, and then press ENTER. Note that you receive the following error message:


    18. The current field must match join key '?' in the table that servers as the 'one' side of one-to-many relationship. Enter a record in the 'one' side table with the desired key value, and then make the entry with the desired join key in the 'many-only' table.
  18. Press the ESC key twice to undo the new record.



REFERENCES

For more information about AutoLookup queries, click Microsoft Access Help on the Help menu, type "AutoLookup query" in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Additional query words: prb 


Keywords          : kberrmsg kbdta QryHowto 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: May 13, 1999