Lookups displaying AutoNumber instead of Field Name

[milkman2500]

Hi,

I just joined the forums and am hoping to get some advice on random issues I'm running into with my new database.

I have 3 tables: [SurveyVendor], [Surveys] and [SurveyResults]

SurveyVendor has 2 fields:
[VendorID] - PK
[VendorName]

Surveys has 3 fields:
[SurveyID] - PK
[VendorName] - FK
[SurveyName]

SurveyResults has 3 fields:
[SurveyResultID] - PK
[SurveyName] - FK
[SurveyQuantity]

I used the LookUp wizard to establish the relationships, and I chose to include 3 fields when linking [SurveyResults].[SurveyName] to [Surveys].[SurveyID]. I'd like the ID, Survey Name and Vendor Name to be displayed when making a choice in the combo box. And I would like the column to only display the SurveyName.

However, what's happening is the VendorID shows up instead of the VendorName when I click the combo box for [SurveyName]. And the SurveyID shows up in the column results, rather than the Survey Name.

Any ideas?

 

[Alansidman]

Read here on why that happens.

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx


As an aside you should not be doing lookups in a table. These should be done in your forms.

Read this

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

if you are doing this in your forms or need to learn how to do it in a form, then look at this video on setting up the bound field and the display fields. there are three tutorials listed if you scroll down to combo boxes.

http://www.datapigtechnologies.com/ExcelMain.htm

 

[gemma-the-husky]

the "key" though is to actually not show the idfield - as it should not matter - it's just something the system uses to relate records.

in a lookup, ACCESS can only show one value anyway, which is normally the first non-zero length column. Make the ID column zero length, and you will then see the description instead.

 

[milkman2500]

Thanks for the help! Problem solved.

The article you referenced was quite helpful as well.