ACC: Errors Adding Record to AutoLookup Form (95/97)
ID: Q142842
|
The information in this article applies to:
-
Microsoft Access versions 7.0, 97
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-
Current field must match join key '?' on 'one' side of
one-to-many relationship because it has been updated.
-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.
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 receive the error message:
<fieldname> 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
- Open the sample database Northwind.mdb.
- Select the Orders table, and on the Edit menu click Copy.
- On the Edit menu, click Paste.
- In the Paste Table As dialog box, type Orders2 in the Table Name box,
and then click OK.
- Open the Orders2 table in Design view.
- Change the OrderID Data Type from AutoNumber to Number.
- Save and close the Orders2 table.
- Create the following new query based on the Customers and the Orders2
tables:
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
- Save the query as AutoLookup and close it.
- Select the AutoLookup query, and on the Insert menu, click AutoForm.
- When the new form opens in Form view, on the View menu click Design
View (or Form Design in Microsoft Access 7.0).
- Select the CustomerID control, and then on the Format menu, point to
Change To, and then click Text Box.
- Select the Region control, and set its DefaultValue property to 0
(zero).
- Open the form to Form view, and on the Records menu, click Data
Entry.
- Try to type any value into the Customer text box. Note that the
following message appears in the status bar:
To make changes to this field, first save the record.
- Press the ESC key twice to undo the new record.
- Type 1234 in the OrderID text box. Press TAB to move to the Customer
text box, enter a value, and then press ENTER. Note that you receive
the following error message:
Current field must match join key '?' on 'one'
side of one-to-many relationship because it has
been updated.
- Press the ESC key twice to undo the new record.
REFERENCES
For more information about the AutoLookup feature, search on the phrase
"AutoLookup queries," and then view the "Create an AutoLookup query that
automatically fills in data" or the "About AutoLookup queries that enter
data automatically" topic, using the Microsoft Access 97 Help Index.
Keywords : kberrmsg QryHowto
Version : 7.0 97
Platform : WINDOWS
Issue type : kbprb