single appearance of name in combo box in query-by-form

[Kyle]

Help! Help! Underdog - help!

I have searched through the newsgroup postings on query-by-form and
combo boxes but have not found the answer to my peculiar, er,
particular problem.

I have a report that lists all units tracked in the database by Primary
Investigator. I would like occasionally to be able to generate that
report for just one P.I. I determined that I needed to do that by
constructing a query-by-form and used the Combo Box Wizard to set it
up, and everything on the form looks OK, except...

The combo box lists every single appearance of a P.I. in the query from
which I pull the data for the report - so if the database shows that
Dr. Joe Schmoe has 12 freezers, then his name appears in the combo box
12 times.

If I link to the table tblEmployees from which the P.I. names are
pulled, it lists every single employee, regardless of whether they're a
Primary Investigator, lab tech, manager, etc., which numbers in the
hundreds more than the P.I.s we have.

How do I limit the combo box to display the Primary Investigator names
(the ones in the query) only once even though each name appears
multiple times (sometimes as much as 70 times) in the query?

Structure:
The query qryFreezersbyPISelection pulls data from tables tblFreezers,
tblDepartments and tblEmployees.
I've created a form SelectPrimaryInvestigator to manage the
query-by-form.
The report rptFreezersbyPI pulls the data all from the query
FreezersbyPISelection.

Thanks!

 

[Sprinks]

Kyle,

Change the SELECT statement of the combo box' RowSource to SELECT DISTINCT.
If your RowSource is a named query, either edit the query in SQL View (View,
SQL) and do the same, or, in QBE view, change the query's UniqueValues
property to Yes.

Sprinks

 

[Kyle]

I love you! Marry me! ;-D

(I think my wife wouldn't like that very much...)

All kidding aside, you just made my life simpler by an order of
magnitude. I am grateful for your putting me onto SELECT DISTINCT. It
worked like a charm!