Unique values in a combo box

  • Thread starter Thread starter dannie
  • Start date Start date
D

dannie

I have a form that displays my records stored in a table "inspectionsDI" I
have a listbox in the form that displays all the records for easy to find
access. I will have over 400 records so I want to make a combo box that will
filter my listbox according to a value stored in the table. I can make it so
that the combo box displays all the values but there are many duplicates, how
do I make the combo box show only each unique value once? Or is there are
better way to approach this problem?
 
Having a listbox with "over 400" records is more than most users will put up
with. <g>

Adding a combobox to narrow the list down is a way to reduce the number of
rows the users have to work through ... BUT!

If you have a combobox to help select the right record, why bother with the
listbox at all? Your combobox would take up less room on the form and would
still give your users a way to select the right record.

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
I have a form that displays my records stored in a table "inspectionsDI" I
have a listbox in the form that displays all the records for easy to find
access. I will have over 400 records so I want to make a combo box that will
filter my listbox according to a value stored in the table. I can make it so
that the combo box displays all the values but there are many duplicates, how
do I make the combo box show only each unique value once? Or is there are
better way to approach this problem?
Click to expand...

I'm not sure I follow what you want, but I think .....

Create a query to display just the distinct records.

Select Distinct MyTable.[FieldName] from MyTable;

use that as the Combo Rowsource.
(Look up the Distinct keyword in SQL help.)

Then, to filter the List Box, code the Combo Box AfterUpdate event
(all on one line):

Me.ListBoxName.Rowsource = "Select inpsectionDI.FieldName1,
inspectionDI.FieldName2 from inspectionDI Where inspectionDI.FieldName
= """ & Me.ComboName & """;"

The coding above assumes the value in MyTable.FieldName is Text
datatype.
If in fact it is Number datatype then use:

Me.ListBoxName.Rowsource = "Select .... etc ..... Where
inspectionDI.FieldName = "& Me.ComboName


Change all of the control, field, and table names to whatever your
actual names are.
 
Back
Top