Microsoft KB Archive/207816

= ACC2000: Lookup Wizard Fails with Certain Data Types in Tables =

Article ID: 207816

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q207816



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

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



SYMPTOMS
When you use the Lookup Wizard in Design view of a given table to look up a Currency, Date/Time, or Hyperlink field in another table, you may receive the following error messages

You entered an expression that has an invalid reference to the property |.

and then

Microsoft Access couldn't launch the LookUp Wizard.



CAUSE
The Currency, Date/Time, and Hyperlink data types do not contain a DisplayControl property. When you use one of those data types as the bound column in your lookup field, the Lookup Wizard sets your lookup field to the same data type and tries to set the DisplayControl property for that field.

The error message occurs with the Lookup Wizard under the following conditions:


 * If the table that you are looking up does not have a primary key field, and the bound field that you select in the Lookup Wizard is of data type Currency, Date/Time, or Hyperlink.
 * If the table that you are looking up has a multifield primary key, and the bound field that you select in the Lookup Wizard is of data type Currency, Date/Time, or Hyperlink.
 * If the primary key in the table that you are looking up is of data type Currency or Date/Time; Microsoft Access does not allow you to create a primary key with a Hyperlink data type.



RESOLUTION
You can work around this behavior in two ways:
 * You must include a field of data type Text, Number, or Yes/No as the bound column for the lookup field in your table because those data types have a Display Control property.
 * If you are trying to look up a Currency field, you can manually create the lookup field using a Double Number field instead of a Currency field.

Method 1: Include a Text, Number, or Yes/No Field as the Bound Column
To use the Lookup Wizard to display a Currency field by adding a second field of data type Text, Number, or Yes/No as the bound column, follow these steps:  Start Microsoft Access and open the sample database Northwind.mdb. Create the following new table in Design view:

Table: LookupCurrency

---

Field Name: ID

Data Type: AutoNumber

Field Name: UnitPrice

Data Type: Lookup Wizard

Table Properties: LookupCurrency

PrimaryKey: ID

 In the Lookup Wizard dialog box, click I want the lookup column to look up the values in a table or query, and then click Next. In the Which table or query should provide the values for your lookup column? dialog box, click Order Details, and then click Next. In the Which fields contain the values you want included in your lookup column? dialog box, add OrderID and UnitPrice to the Selected Fields box, and then click Next.

OrderID is a Number field, and UnitPrice is a Currency field. In the How wide would you like the columns in your lookup column? dialog box, point to the right border of the OrderID column heading until your pointer changes to a cross with arrows pointing left and right. Then drag the column border to the left until the column disappears. Click Next.</li> In the Choose a field that uniquely identifies the row dialog box, click OrderID, and then click Finish.</li> Click Yes when you see the message that the table must be saved before relationships can be created. Save the table as LookupCurrency.</li> Switch the table to Datasheet view.

Note that the combo box in the UnitPrice field displays Currency values from the Order Details table. However, the data that is actually stored in that field is the OrderID because OrderID is the bound column.</li></ol>

NOTE: If you repeat steps 1 through 9, but omit the OrderID field (a Number field) in step 5, you receive the error message mentioned in the "Symptoms" section.

Method 2: Use a Double Number Field to Look Up Currency
<ol> Start Microsoft Access and open the sample database Northwind.mdb.</li> Create the following new table and name it Table1:

Table: Table1

---

Field Name: Field ID

Data Type: AutoNumber

Indexed: Yes (No Duplicates)

Field Name: Field1

Data Type: Number

Field Size: Double

Format: $#,##0.00

</li> Click the Lookup tab for Field1, and then change the Display Control property to Combo Box.</li> Click in the RowSource property builder.</li> Select Order Details as the table from which to look up data, and then close the Show Table dialog box.</li> Drag the Unit Price field to the design grid.</li> Close and save the query, and then close and save the table. Click No to the Do you want to create a primary key? prompt.</li> Open the Table1 table in Datasheet view.

Note that you are able to look up Currency data in the drop-down combo box in Field1.</li></ol>

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in Access 2000.

<div class="moreinformation_section">

Steps to Reproduce the Problem
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.

<ol> Start Microsoft Access and open the sample database Northwind.mdb.</li> Create the following new table and name it Table1:

Table: Table1

---

Field Name: FieldID

Data Type: AutoNumber

Indexed: Yes (No Duplicates)

Field Name: Field1

Data Type: Lookup Wizard

</li> Select the I want the lookup column to look up the values in a table or query option to look up values in a table.</li> Select the Order Details table in the Which table or query should provide the values for your lookup column? dialog box.</li> <li>Select the UnitPrice field in the Which fields contain the values you want included in your Lookup column? dialog box.</li> <li>Note that when you click Finish on the last screen of the wizard, you receive the error message described in the "Symptoms" section.</li></ol>

Additional query words: prb pra couldn t

Keywords: kberrmsg kbbug KB207816

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.