Use a combo to filter records on a form

[CJ]

Hi groupies:

My form has a combo box (cmbCompany) that stores an ID and lists the name of
the Company. The ID column is hidden so that only the name of the company
shows up.

The SQL is:

SELECT tblCompany.lngCompanyID, tblCompany.strCompany
FROM tblCompany
ORDER BY tblCompany.strCompany;

The record source for my form is tblStimulations

I would like to be able to use a second combo box (cmbSearch) to filter my
form based on the name of the company and show all of the records for one
company.

If I just use the combo tool and use the wizard to find records based on the
selection, then I can get it to list the companies based on the forms record
source and tblCompany, but then it shows each company many times, which I do
not want.

So, I would like it to show each company once in the combo and then return
all of the records for that company.

Thanks for your thoughts
CJ

 

[Joan Wild]

Change the control source of the combo from
SELECT .....
to
SELECT DISTINCT.....

 

[Marshall Barton]

My form has a combo box (cmbCompany) that stores an ID and lists the name of
the Company. The ID column is hidden so that only the name of the company
shows up.

The SQL is:

SELECT tblCompany.lngCompanyID, tblCompany.strCompany
FROM tblCompany
ORDER BY tblCompany.strCompany;

The record source for my form is tblStimulations

I would like to be able to use a second combo box (cmbSearch) to filter my
form based on the name of the company and show all of the records for one
company.

If I just use the combo tool and use the wizard to find records based on the
selection, then I can get it to list the companies based on the forms record
source and tblCompany, but then it shows each company many times, which I do
not want.

So, I would like it to show each company once in the combo and then return
all of the records for that company.

Change (both?) the combo box's RowSource query to:

SELECT DISTINCT tblCompany.lngCompanyID,
tblCompany.strCompany
FROM tblCompany
ORDER BY tblCompany.strCompany

 

[CJ]

OK, obviously we have a little bit of a misunderstanding and it's my fault
;-)

The first combo (cmbCompany) is used on the form as a lookup for ease of
data entry. It only shows each company once.

The second combo (cmbSearch) is the one that I am having trouble with. If I
use the combo tool, the wizard returns this SQL:

SELECT tblStimulations.lngStimID, tblStimulations.lngCompanyID
FROM tblStimulations;

I do not have access to the Company name because I have a lookup in my
tblStimulations table that only shows the Company Name, it doesn't store it.

I have to play with the SQL to make the Company Name show up (numerous
times) in the list.

SELECT DISTINCT will not work because of all of the Stim ID's.

Any other thoughts?
Thanks.
CJ

 

[Marshall Barton]

Sounds like a conflict in your goal. If the StimID is
different in every record, what would want to do with it
when the CompanyID only shows once? Wgy do you want the
StimId value in the query anyway?

BTW, you can get the company name by joining the companies
table in the query, which you are probably doing in the data
entry combo box. I guess I am wondering why the two combo
boxes are not identical except the search combo is unbound??

 

[CJ]

Well, you know.......I had the form based just on tblStimulations, not a
query with tblCompany $#$**#!!@???
One of those, can't see the forest for the trees situations.....sigh.

Thanks for clearing out the deadwood, Marshall.

CJ

Sounds like a conflict in your goal. If the StimID is
different in every record, what would want to do with it
when the CompanyID only shows once? Wgy do you want the
StimId value in the query anyway?

BTW, you can get the company name by joining the companies
table in the query, which you are probably doing in the data
entry combo box. I guess I am wondering why the two combo
boxes are not identical except the search combo is unbound??
--
Marsh
MVP [MS Access]

OK, obviously we have a little bit of a misunderstanding and it's my fault
;-)

The first combo (cmbCompany) is used on the form as a lookup for ease of
data entry. It only shows each company once.

The second combo (cmbSearch) is the one that I am having trouble with. If
I
use the combo tool, the wizard returns this SQL:

SELECT tblStimulations.lngStimID, tblStimulations.lngCompanyID
FROM tblStimulations;

I do not have access to the Company name because I have a lookup in my
tblStimulations table that only shows the Company Name, it doesn't store
it.

I have to play with the SQL to make the Company Name show up (numerous
times) in the list.

SELECT DISTINCT will not work because of all of the Stim ID's.

"Marshall Barton" wrote