ComboBox Multiple Entries

[Chad Cameron]

Hi All,

I am starting a new database. I have a combobox that points to a
companyname field. When I select a company name I want a listbox to
populate with the available contractors names.

I have multiple contractors for a single company, so my first problem is:
When I click on the combobox, it displays the same company name multiple
times (1 for each contractor). How can I setup the combobox to show only1
company name for all the companys we deal with?

Chad

 

[kate]

Hi All,

I am starting a new database. I have a combobox that points to a
companyname field. When I select a company name I want a listbox to
populate with the available contractors names.

I have multiple contractors for a single company, so my first problem is:
When I click on the combobox, it displays the same company name multiple
times (1 for each contractor). How can I setup the combobox to show only1
company name for all the companys we deal with?

Chad

 

[kate]

You ought to have the following tables:
TblCompany
CompanyID
CompanyName
etc


TblCompanyContractor
CompanyContractorID
CompanyID
CompanyContractor

Set the RowSource property to TblCompany and you will only see each
company name once.

Steve
(e-mail address removed)

 

[BruceM]

As has been pointed out, basing the combo box row source on a company table
will eliminate duplicates. Another option, depending on the details of what
you are trying to do, is to add DISTINCT after SELECT in the Row Source SQL.
You can do this in query design view by right clicking anywhere in the
design grid (where the tables are shown) and selecting Properties. Set
Unique Values to Yes. In a purchase order database I may want to search for
companies to whom purchase orders have been written within a specified time
frame. The row source of a search combo box uses SELECT in the SQL, as I
only want to see each company name once no matter how many purchase orders
there are.

The SELECT DISTINCT option is useful in some circumstances, but again you
really need a company table if you do not already have one. It is the only
real way to keep typos, abbreviations, and other inconsistencies from
showing two or more companies where only one is intended. Also, if a
company name changes you will have continuity between old records and new
ones.

 

[kate]

You ought to have the following tables:
TblCompany
CompanyID
CompanyName
etc


TblCompanyContractor
CompanyContractorID
CompanyID
CompanyContractor

Set the RowSource property to TblCompany and you will only see each
company name once.

Steve
(e-mail address removed)