Lookup Field Problem

  • Thread starter Thread starter rjschutt
  • Start date Start date
R

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.
 
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
 
I am creating an inventory database and when I create a lookup field
using a wizard I run into a problem.
Click to expand...

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".
Click to expand...

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]
 
Back
Top