ACC2: Adding Records to AutoLookup Form Generates Errors

• Microsoft Access versions 2.0

SYMPTOMS

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

When you are adding a record to a form based on an AutoLookup query, one of the following messages is generated 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 outer join

   -or-

   Field can't be edited

CAUSE

You have one or more default values set on form control(s) bound to the one-side table in your AutoLookup query.

RESOLUTION

Make sure that the form control(s) on the "one" side have no default values set.

MORE INFORMATION

When you enter a value in the foreign key field in 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 has a default value, Microsoft Access assumes 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:

   Current field must match join key "?" on 'one' side of outer join

   To make changes to this field, first save the record

Steps to Reproduce Behavior

1. Open the sample database NWIND.MDB.

2. Use a Form Wizard to create a new form based on the AutoLookup query. Include all the fields in the form.

3. View the form in Design view. Set the Country control's DefaultValue property to "USA."

4. View the form in Form view. From the Records menu, choose Data Entry. Try to type any value in the Customer ID field. Note the message in the status bar.

5. Press the ESC key.

6. In the Order ID field, enter "1111" (without the quotation marks). In the Customer ID field, enter any value. Note that you receive an error message when you try to exit the field.

REFERENCES

For more information about the AutoLookup feature, search for "AutoLookup," and then "Looking Up Information Automatically (AutoLookup)" using the Microsoft Access Help menu.