Combo Box does not display the value I expected! 2

[FuzzyBear9]

Hi,

I am newbie to Access and would appreciate any guidance to resolving the problem below.

I am currently trying to implement a contact management database. I have two tables.

Table 1: tblCompany
Table 2: tblContact

Table 1 contains two fields:

CompanyID (AutoNumber - Primary Key)
Company (Text)

Table 2 contains numerous fields, eg:

ContactID (AutoNumber - Primary Key)
CompanyID (JOIN: The 'Company' field from Table 1)
Position (Text)
Address (Text)
etc etc

Depending on the type of business, different contacts and their details will need to be accessed for the same company.

Eg TEST COMPANY PLC may have a Finance Director, an HR Director, an IT Director etc, with different addresses and contact numbers.

PROBLEM:
I have been able to set up the related tables and a form (frmClient) for users to enter their data. However I to wish to implement a search function. I have successfully used a combo box in the past to search records by a particular field. However when I attempt to create a combo box for frmClient, the combo box displays the primary key from tblCompany. I want it to display the actual company name. Is there a simple way to achieve this?

Thanking you in advance

 

[smoker1]

In the control source of the combo box, reference the tblCompanyName (if it is available in the related query). If it is not available from the form's control source, use a syntax similar to this:

SELECT DISTINCTROW [Company] FROM [tblCompany] ORDER BY [Company]

This will pull the company name field and sort them alphabetically.

Hope that helps.

--Angela

 

[RickSpr]

You also have the option of including both the CompanyID and the company name fields in the combo box, even if you only want the company name to be visible. Simply include both fields in your Row Source SELECT statement, set the Column Count to 2, and set the Column Width property to give the CompanyID field a width of 0. (You don't have to specify a width for the company name; it will default to taking up the whole width of the combo box.) Rick Sprague

 

[FuzzyBear9]

Hey! You guys are great!!!!

When I used the Toolbox/Control Wizard to create the combo box to search records on the form, Access produces this SQL statement:

SELECT DISTINCTROW tblContact.ContactID, tblContact.CompanyID
FROM tblClient;

Thanks to you guys, this is what I used to resolve the problem:

SELECT DISTINCTROW tblCompany.CompanyID, tblCompany.Company
FROM tblCompany
ORDER BY tblCompany.Company;

I set the column to 2 and the column width of the first column to 0cm, as you suggested.

Thank you Angela & Rick!!