Automatic combo box options...

[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

 

[Dirk Goldgar]

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.

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.

 

[Steve Schapel]

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

 

[Ben Moore]

You guys understood me perfectly, and I appreciate your help. I will try
those options out.

Thanks again!

cheers,
Ben Moore