Populate a combo box with most frequently used data

[Easto]

It's been about 5 years since I actually took any Access programming semi-seriously. I've forgotten more than I remember. I have a good idea how to go about this but was looking for a more "elegant" solution. Just a basic point in the right direction should get me going on this.

I want to populate a combobox on a search form with a list of the top 3,5,10 or 20 (I'll set that amount) most frequently used selections from a field in a table.

For example. Our database has approximately 5000 customers but only about 200 are actually active and doing business with us on a regular basis. I'd like to only expose those 200 customers for selection with the ability to unfilter the search if necessary.

 

[dhookom]

Set the Row Source of your combo box to something like:

SELECT TOP 200 CustomerID, Count(*) as NumOf
FROM tblSales
GROUP BY CustomerID
ORDER BY 2 DESC

Duane
Hook'D on Access
MS Access MVP

 

[Easto]

Thank you.

How would I code a button next to that combobox that would remove the "Top" functionality and show all the records?

 

[dhookom]

The code for the command button might look like:

Dim strSQL as String
strSQL = "SELECT CustomerID, Count(*) as NumOf " & _
    "FROM tblSales " & _
    "GROUP BY CustomerID " & _
    "ORDER BY 2 DESC "
Me.cboYourComboBoxNameHere.RowSource =  strSQL

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

Or...create an Active field (Y/N)in your Customer table default to Y. Set your in-active customers to N in this field. This may take a short time the first time to get all your in-active customers to N (you could use an update query)

For your combo box set its criteria for the Active field to Y. This way you would not have to deal with the top 2 -5- 10 -20. Your combo box would always show only active customers.

Now you can track the Active vs Inactive just by setting criteria in a query so you see which customers you want.

As you can see - re-activation would be easy. Just call up the customer and change the Active field to Y.

 

[Easto]

Thank you everyone.

puforee... that was an option I was working one too.

Thank you all for the help.

Easto