Automatic combo box options...

  • Thread starter Thread starter Ben Moore
  • Start date Start date
B

Ben Moore

Is there any way to have a drop-down list field automatically create its
choices based on repeat entries in the field? Is there a way to set the
frequency before something shows up? If anyone has any insight on how to
do this, please let me know.

Any and all help is appreciated greatly. Thank you in advance.

cheers,
Ben Moore
 
Ben Moore said:
Is there any way to have a drop-down list field automatically create
its choices based on repeat entries in the field? Is there a way to
set the frequency before something shows up? If anyone has any
insight on how to do this, please let me know.

Any and all help is appreciated greatly. Thank you in advance.
Click to expand...

If I understand correctly what you're after, you can set the combo's
RowSource to a query that extracts only values that occur more than some
fixed number of times. You can even order them so that the most
frequent ones come at the top. For example, the following SQL will
bring up only those values of [MyField] that occur more than twice:

SELECT My Field FROM My Table
GROUP BY My Field
HAVING (Count(*)>2)
ORDER BY Count(*) DESC;

To update the list as new values are entered and old values are modified
or deleted, you would need to requery the combo box in the form's
AfterUpdate and AfterDelConfirm events.
 
Ben,

A very general question, but if I correctly understand the concept,
try something like this as the combobox's Rowsource...

SELECT YourField FORM YourTable
WHERE YourField IN(SELECT YourField
FROM YourTable
GROUP BY YourField
HAVING Count(YourField)>2)

If I've missed the boat, please post back with some more details,
perhaps with examples.

- Steve Schapel, Microsoft Access MVP
 
You guys understood me perfectly, and I appreciate your help. I will try
those options out.

Thanks again!

cheers,
Ben Moore
 
Back
Top