Drop down lookup problem

[John]

Hi

I have a client table with clients and their ids. Some client names are
duplicate as they have several locations but ids are unique, so for example;

ID Company
23 MyCompany Inc
27 MyCompany Inc
56 MyCompany Inc

On the orders form I have the clients drop which displays all the clients
for user to select. The problem is; as company names are duplicate selecting
by company name is not enough. How can I select a company but also get the
client id behind the scene? Ideally I do not want to store the id in the
client field bound to the drop down as the rest of the app expects client
name in that field and not client id.

Thanks

Regards

 

[Ken Snell \(MVP\)]

Explain more about "also get the client id behind the scene". Do you want to
display the client id value on the form? do you want the form to be able to
read that value and do something with it?

Either way, perhaps this article will help:
http://www.mvps.org/access/forms/frm0058.htm

 

[John]

Form needs to know the value of client id does not have to be displayed for
user though.

Thanks

Regards

 

[John Vinson]

Form needs to know the value of client id does not have to be displayed for
user though.

Then have the ClientID as the Bound Column of the combo box, but set
the width of that column to 0 in the ColumnWidths property.

John W. Vinson[MVP]

 

[Dirk Goldgar]

Hi

I have a client table with clients and their ids. Some client names
are duplicate as they have several locations but ids are unique, so
for example;

ID Company
23 MyCompany Inc
27 MyCompany Inc
56 MyCompany Inc

On the orders form I have the clients drop which displays all the
clients for user to select. The problem is; as company names are
duplicate selecting by company name is not enough. How can I select a
company but also get the client id behind the scene? Ideally I do not
want to store the id in the client field bound to the drop down as
the rest of the app expects client name in that field and not client
id.

In a situation like this, whatever you show the user in the combo box
needs to contain enough information to enable them to distinguish one
identically named company from another. I suggest that you set the
combo box's rowsource query to return, in addition to the ID field, a
calculated field that is composed of the company name concatenated to
some additional information. Since I don't know what additional
information is available, I can only suggest sticking the company ID on
the end, giving a rowsource query with SQL like this:

SELECT
ID,
Company & " (ID " & ID & ")" As CompanyDesc
FROM Companies
ORDER BY Company, ID;

That way you can have the combo box bound to the ID field, while still
showing unique descriptive info for each company. That will allow both
the user and Access to pick the correct company ID.