Lookup Wizard changing Data Type

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to make Lookups from Table Field to Table Field and generally it
seems OK.

However the LookupWizard is changing the Data Type from Text to Number (I
guess it's looking at the ID?)

Data picked from Combo Box 'looks' OK in Table view

A Query view is asking for a number but displays the Text field value when a
valid ID number is added.

Viewing Results in FrontPage Database Wizard, and using hand coded .asp
querie, it's showing the ID field value.

I can see why...

Any ideas?

Merci.
 
Yes, the wizard isn't the problem it's the use of Lookup Fields in the
first place. Don't! The best solution to your current problem is to
go back to the tables and take them out. Re-work anything that made
use of their cute little comboboxes. It's OK to curse MS under your
breath.

See www.mvps.org/access for some relevant discussion.

HTH
 
Las

So what your saying, in you mildly sarcastic way, is that this doesn't work.

Is there an alternative?

Thanks
 
Appalachia said:
Las

So what your saying, in you mildly sarcastic way, is that this doesn't
work.

Is there an alternative?
Click to expand...

Yes. You can do lookups, but you should not do it with "lookup fields"
because they obscure what is actually stored in the table. They are useful
only for end-users who just use datasheet view, and are a mild-to-traumatic
pain to others who later try to use the field as though it were "real,
normal" data.

For example, if you have a table of Custom Colors, you could refer to it in
a lookup field to see the Color Name in datasheet view of the Product Table,
but what's actually stored is the id or key field of the Custom Colors
Table, not the name you see. So, when you query that table, you expect to
see the name, but instead see the number that's actually stored.

Instead, if you store the number, you can always use a Combo Box to select
it by looking at the name; you can link the Product Table in a Query to the
Custom Colors Table to retrieve the color name along with product
information, or again use a two-column Combo Box. And, when you look in the
Product Table, it's _obvious_ what is stored, a foreign key to the other
table, so there are no surprises.

Larry Linson
Microsoft Access MVP
 
Appalachia said:
Lol,

Any examples anywhere ?
Click to expand...

Yes, there are examples throughout the Northwind Traders example database
that comes with, and unless you take specific action to prevent it, is
installed with every copy of every version of Access.

An outstanding "guide" to the examples in the Northwind Traders database
(and others) is the Developer Solutions sample database, a download link to
which is available in the Knowledge Base article at
http://support.microsoft.com/kb/248674/en-us.

Larry Linson
Microsoft Access MVP
 
I am trying to make Lookups from Table Field to Table Field and generally it
seems OK.

However the LookupWizard is changing the Data Type from Text to Number (I
guess it's looking at the ID?)

Data picked from Combo Box 'looks' OK in Table view

A Query view is asking for a number but displays the Text field value when a
valid ID number is added.

Viewing Results in FrontPage Database Wizard, and using hand coded .asp
querie, it's showing the ID field value.
Click to expand...

It's showing the ID field value because that is what is in the table. The
Lookup Wizard is pretty much universally considered to be a misfeature and
more of a problem than a benefit. See

http://www.mvps.org/access/lookupfields.htm

for a critique.

You can base your FrontPage display on a stored Query joining to the lookup
field. The lookup wizard just isn't smart enough to help here - the *only*
things it can do are make it easier to use Table Datasheet view in the Access
user interface (which is a Bad Thing because table datasheets should not
generally be used for data interaction anyway), and make it a bit easier to
add combo boxes to Forms (again in the Access user interface).

In short - the lookup wizard will make it harder, not easier, to work with
your Frontpage interface. Use the lookup tables, by all means; but you'll need
to provide your own Queries in order to use ASP.

John W. Vinson [MVP]
 
Back
Top