Display count beside combo box 2

[jimoblak]

Please excuse my stupidity. I come from a background of raw SQL and find the user-friendly GUI of Access not so friendly to me.

I am creating a form with a combo box that displays the distinct types of orders (online, store, phoned, faxed, etc). Once selected in the form, what is an ideal way to display the number of instances for the selected type of order?

The row source for the combo box is:
SELECT DISTINCT [Sales].[ordertypes] FROM [Sales];

The combobox runs a 'requery' event after update.

I would like the form to place a number next to this combo box that automatically updates to the count of whatever is selected in the combo box.

The count zone in the form should use the following SQL...

Select COUNT([Sales].[ordertypes]) From [Sales] WHERE [Sales].[ordertypes] = Forms![MyFormName]![ordertypes].Value;

What kind of object should I place next to this combo list to show the count SQL?

 

[PHV]

Why not simply have a 2 columns combo:
SELECT ordertypes, COUNT(*) FROM Sales GROUP BY ordertypes

And then, in the AfterUpdate event procedure of the combo you may update a text box like this:
Me![name of TextBox] = Me![name of ComboBox].Column(1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[snyperx3]

You can use a label or a textbox. I guess a label would look better.

May I suggest using DCount?

syntax: DCount ( expression, domain, [criteria] )

In your vba for your form you could use the after update event of the combo box and place the code

lblCount.value = DCount(uniquesalesid,sales,"OrderTypes = '" & ordertypes.value & "'")

May I also suggest using the standard naming convention. This will help you and all future people working on this database. For example, the combo box should be named cboOrderTypes instead of just ordertypes.

-Pete