I am reading Beginning Access 2000 VBA by Robert Smith and David Sussman.

 

I see with the product and supplier tables (as with a number of others) 

the foreign key for the supplier is in the products table. When you run a 

query with the two tables, and include all the fields from the product 

table including the foreign key from the supplier the query result 

supplies the Supplier Name in the foreign key field, not the foreign key 

number.

 

How does it do this, all my attempts always result in the foreign key's 

number showing not the name, which is what I want, that is the name.

 

Can you please help, as I want to us autolookup using the Name of the 

supplier not the supplierID number.

 

Stephen Ward

Lennard Promotions

You set this on the Lookup tab of the field in the table's design view.

Choose ComboBox as the Display control.  Choose Table/Query as the row

source type.  In the Row Source prop, write SQL that selects first the

numeric code you don't want to show, and then the text values you do want to

show.  Leave bound column set to 1.  Change column count to 2.  For column

widths, set the first column width to zero (e.g., '0,1').  That should do

it.



Cheers,



-Roy



-----Original Message-----

From: lennard@n... [mailto:lennard@n...]

Sent: Saturday, May 05, 2001 8:02 PM

To: Access

Subject: [access] autolookup query access 2000





I am reading Beginning Access 2000 VBA by Robert Smith and David Sussman.

 

I see with the product and supplier tables (as with a number of others) 

the foreign key for the supplier is in the products table. When you run a 

query with the two tables, and include all the fields from the product 

table including the foreign key from the supplier the query result 

supplies the Supplier Name in the foreign key field, not the foreign key 

number.

 

How does it do this, all my attempts always result in the foreign key's 

number showing not the name, which is what I want, that is the name.

 

Can you please help, as I want to us autolookup using the Name of the 

supplier not the supplierID number.

 

Stephen Ward

Lennard Promotions

Roy,



Thank you for your response. I tried what you said and it has worked 

beautifully.



I appreciate your kind assistance.



regards

Stephen Ward

Lennard Promotions (Australia)