ACC: Lookup Wizard Fails with Certain Data Types in Tables (164098)

   Microsoft Access couldn't launch the LookUp Wizard.
				

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 Currency.

Method 1: Include a Text, Number, or Yes/No Field as the Bound Column

The following example shows how you can 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:

1. Open the sample database Northwind.mdb.
2. 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
   						

3. 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.
4. In the "Which table or query should provide the values for your lookup column?" dialog box, click Order Details, and then click Next.
5. 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.
6. 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.
7. In the "Choose a field that uniquely identifies the row" dialog box, click OrderID, and then click Finish.
8. Click Yes when you see the message that the table must be saved before relationships can be created. Save the table as LookupCurrency.
9. 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.

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

1. Open the sample database Northwind.mdb.
2. Create the following new table named Table1:
   

         Table: Table1
         -------------------------------
         Field Name: Fields ID
            Data Type: AutoNumber
            Indexed: Yes (No Duplicates)
         Field Name: Field1
            Data Type: Number
            Field Size: Double
            Format: $#,##0.00
   						

3. Click the Lookup Tab for Field1 and change the Display Control property to Combo Box.
4. Click in the RowSource property builder.
5. Select the Order Details Table to Lookup data from.
6. Drag the Unit Price field to the query grid.
7. Close the query, and then close and save the table. Click No to the prompt "Do you want to create a primary key?"
8. Open the Table1 table in Datasheet view. Note that you are able to look up Currency data in the Field1 field.