Counting was easy in 3rd grade, but not access---please help

[ToddT]

Hi.

I'm trying to do something that I thought would be simple:

For a given record in a form (Job Entry Form), I would like to count the total number of stores assigned to that job. The stores are assigned through the use of a subform (Store Assignment Subform) on the Job entry form. One the count is done, I would like to place that number into a field on the main form.

Any help would be great.

Todd
todd@traveldirectors.com

 

[JoeMiller]

Are you placing it to store it in a table?? If so this is a bad idea! Why is it a bad idea? I'm glad you asked!! If a value can be calculated from other data in a database (IE: count the number of related records, price times quantity equals extended price) the results of the calculation should not be stored because they can be calculated at any time in queries. If you record a calculated value, you have to remember to code so that the calculated value is updated if you change one of the values the calculated value depends on. Instead always calculating it when it's need provides better database stability.

If you want to SHOW the count of stores related to a job you can use the DCount() statement to do it. Set the control source of a textbox to this:

=DCount("*","MyTableThatStoresTheSubFormData","[JobUniqueIDFieldName]=[MyTextboxOnTheMainFormThatStoresTheIDForTheJobI'mInterestedInCountingOn]&quot

obviously you'll need to change the table name, field name and textbox name to match your db.

HTH Joe Miller
joe.miller@flotech.net