ACC2000: AutoLookup Field Returns Unexpected Value in Word Mail Merge (207597)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q207597
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

When a Microsoft Word Mail Merge document displays AutoLookup fields from a table, the fields do not return the AutoLookup values as expected.

CAUSE

Microsoft Word Mail Merge does not use the RowSource property of the table fields to retrieve the lookup values, and retrieves the data values of the fields instead.

RESOLUTION

You must create a new query that includes the fields referred to in the RowSource property of the AutoLookup fields. Use this query as the data source for your Word Mail Merge document.

To display the AutoLookup values in a Word Mail Merge document, follow these steps.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

Prepare for the Mail Merge

  1. Start Microsoft Access and open the sample database Northwind.mdb.
  2. In the Database window, click Tables under Objects, click the Orders table, and then click Design.
  3. Select the CustomerID field and, on the Lookup tab under Field Properties, examine the RowSource value. Note that AutoLookup uses the CustomerName field from the Customer table for its lookup value. This reference is used in step 10.
  4. Select the EmployeeID field and, on the Lookup tab under Field Properties, examine the RowSource value. Note that AutoLookup concatenates the LastName and FirstName field values from the Employees table for its lookup value.

    These references are used in step 11.
  5. In the Database window, click the Queries tab, and then click New.
  6. In the New Query dialog box, select Design View, and then click OK.
  7. In the Show Table dialog box, select Employees, and then click Add.
  8. Repeat step 7 for the Orders table and the Customers table, and then click Close.
  9. From the Orders table, add all the fields except the CustomerID and EmployeeID fields to the query design grid.
  10. Add the CompanyName field from the Customers table to the query design grid.
  11. Add the LastName and FirstName fields from the Employees table to the query design grid.
  12. Save the query as MergeOrders, and then close it.

Running the Mail Merge

  1. In the Database window, click Queries under Objects, and then select the MergeOrders query that you created in the "Prepare for the Mail Merge" section.
  2. On the Tools menu, point to OfficeLinks, and then click Merge It with MS Word.
  3. In the Microsoft Word Mail Merge Wizard dialog box, click to select the Create a new document and then link the data to it check box, and then click OK.

    Note that Microsoft Word starts and that a new, blank document is created.
  4. On the Mail Merge toolbar in Word, click the Insert Merge Field button. Note the list featuring all the fields in the Orders table, the CompanyName field from the Company table, and the LastName and FirstName fields from the Employees table. Select CompanyName from the list, and then press ENTER.
  5. Click the Insert Merge Field button, select LastName from the list to add it to the document, and then press COMMA and then SPACEBAR to separate the LastName field from the FirstName field.
  6. Click the Insert Merge Field button, select FirstName from the list, and then press ENTER.
  7. On the Mail Merge toolbar, click the View Merged Data button.

    Note that the data presented in the Mail Merge document is composed of the same AutoLookup field values that are displayed in the Orders table.

MORE INFORMATION

Steps to Reproduce the Behavior

The following example demonstrates what occurs when you include an AutoLookup field from a table in a Mail Merge document.
  1. Start Microsoft Access and open the sample database Northwind.mdb.
  2. In the Database window, click Tables under Objects, and then click Orders.
  3. On the Tools menu, point to OfficeLinks, and then click Merge It with MS Word.
  4. In the Microsoft Word Mail Merge Wizard dialog box, click to select the Create a new document and then link the data to it check box, and then click OK.

    Note that Microsoft Word starts and that a new, blank document is created.
  5. On the Mail Merge toolbar in Word, click the Insert Merge Field button. Note that a list of all the fields in the Orders table is displayed. Select CustomerID from the list, and then press ENTER.
  6. Click the Insert Merge Field button, and then select EmployeeID from the list.
  7. On the Mail Merge toolbar, click the View Merged Data button.

    Note that the data presented in the mail merge does not contain the AutoLookup field values, but, rather, the actual EmployeeID and CustomerID values.

REFERENCES

For more information about AutoLookup fields, click Microsoft Access Help on the Help menu, type create a field that looks up or lists values in tables in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MajorLast Reviewed:6/28/2004
Keywords:kbprb KB207597
©2004 Microsoft Corporation. All rights reserved.