Lookup Field Problem

[rjschutt]

I am creating an inventory database and when I create a lookup field
using a wizard I run into a problem.

For example, I have a table labeled "Inventory Transactions" and the
field "Storage Location" lookups the table data in the table "Storage
Location."

Now, I go into the table and I select "Storage Location" field and it
displays:

Storage ID StorageShortName StorageName
1 SILPROCEN Processing Centre
2 LONGCON1 LB Container 1
3 FLSTORCEN Storage Centre 1

When I set up the lookup field in the wizard, I selected
StorageShortName to be the column that would display.

However, when I select the data from the lookup field in the Inventory
Transactions table, instead of displaying: "SILPROCEN" it displays
"1".

It does this with several other fields that I have set up with the
lookup wizard. I've tinkered with the bound property, but nothing
seems to work.

Any solutions?

Thank you.

 

[Wayne-I-M]

Hi

1st backup your DB and work on the copy. (just in case)

Create a new combo box on a form

Row ource Type = Table/Query
Row Source = SELECT [StorageLocation].[StorageID],
[StorageLocation].[StorageShortName], [StorageLocation].[StorageName] FROM
[StorageLocation] ORDER BY [StorageShortName];
Column count = 3
Column widths = 0cm;2.542cm;2.542cm
Note - I have shown widths of 2.54 as this is access default.
Bound Column = 1

Next go back to Inventory Transactions and delet the lookup by formating the
field as a number.

Then if you want to run a report from the table do it from a query based on
the table with the column set as the row sourse above. If you want to view
the data then do this from a form using the same type of source as above.

Basicaly the table is only there to store data and for the programmer to
amend/build - never for users to use as a glorified excel spreadsheet :-(
Always use forms or querys to work on/with the data.

Good luck

 

[John W. Vinson]

I am creating an inventory database and when I create a lookup field
using a wizard I run into a problem.

That WAS your problem.

For example, I have a table labeled "Inventory Transactions" and the
field "Storage Location" lookups the table data in the table "Storage
Location."

Now, I go into the table and I select "Storage Location" field and it
displays:

Storage ID StorageShortName StorageName
1 SILPROCEN Processing Centre
2 LONGCON1 LB Container 1
3 FLSTORCEN Storage Centre 1

When I set up the lookup field in the wizard, I selected
StorageShortName to be the column that would display.

However, when I select the data from the lookup field in the Inventory
Transactions table, instead of displaying: "SILPROCEN" it displays
"1".

That's because the table *actually contains the number 1*. That's what's
THERE. The lookup wizard conceals that basic fact from your view!

See http://www.mvps.org/access/lookupfields.htm for a critique of what many of
us considered the worst misfeature ever incorporated into Access (at least up
until they put Multivalued fields into A2007).

Table datasheets - and query datasheets - should be used ONLY for debugging
and design, not for interaction with the data. You can store a 1 in the table,
and use a Form with a combo box displaying SILPROCEN (or Processing Center if
you wish). It's not necessary to use a Lookup Field to do so either!

John W. Vinson [MVP]