Count form values

[KevinAnderson]

I use access 2003 and would like to know if its possible to count records that i have displayed on a form and store the results in a textbox on the same form. More than that I have a field that can contain one of three values for this examples lets just say the values are "Red" "White" and "Blue". Is there any way to make a textbox on the form that will count entries equal to "Red" "White" and "Blue". Seems simple but so far i have had no luck. Is this even possible. Do i need to write a query and pull the results from that?

any help would be good

 

[georgedwilkinson]

For the second part, you could take a look at the DCount() function.

For the first part, when are you going to clear the field? Is it over the life of the system, this session, this computer, what? What if the user (or a different user) displays the same record twice within your defined period?

At any rate, a good place to increment your counter "might" be on the On Current event of the form. You'd have to experiment with that event to make sure it only fires once per record. You might have to keep track of the "state" of your counter with regards to an individual record, possibly requiring you to track the current records key value against a list or table, depending on your answers to the above questions.

 

[Galaxiom]

Rather than use multiple DCount functions it is better use a single query because in essence each DCount is an independent query. Use subqueries in the form's Record Source query to derive three boolean fields representing the values in the field of interest.

Then sum the boolean fields in the header or footer of the form. True equals minus one so either the negative or absolute value of that sum will indicate the count of the field.

Either way I would also consider normalizing the data so the text is represented as numbers since this will increase the speed of the query and prevent potential errors.

 

[bulrush]

If you are using a bound form, only one record can be shown on a bound form at a time, so the count is always 1. Am I understanding you correctly?

Or, are you counting the records on a subform, which represent detail records linked to the currently shown master or header record? I do that sometimes. And it's tricky because of the way subforms work. It's also tricky to keep the count (stored on the master record) updated when the user adds or deletes a subform record.

 

[Galaxiom]

If you are using a bound form, only one record can be shown on a bound form at a time, so the count is always 1.

No, that is not correct. Expressions in the header or footer of a form are applied to the Record Source fields not the controls so it makes no difference whether a form is Single, Continouous or Datasheet.

This is often misunderstood because the Form Wizard names controls to match the fields. Experienced developers often prefer to name the controls to avoid the potential problems.

Their full titles (neglecting Currentdb. etc) are:

For a control: Forms!formname.Form.Controls!controlname
For a field: Forms!formname.Form.RecordSource!fieldname

However these names are usually shortened to:
Forms!formname!objectname

This can be done because of the default properties heirarchy. Where a control and a field have the same name the default is first to the control then to the the record source field if no control by the name is found.

However the aggregate expressions refer to the field. You cannot aggreagate a control.

It's also tricky to keep the count (stored on the master record) updated when the user adds or deletes a subform record.

Just requery the control containing the expression.