Adding Count(*) to form

[colt64]

I'm feeling really silly for not being able to do this, but I've searched everywhere and I can't find a solution. I'm also very new to Access, so maybe I just don't know what to look for. I have a very simple DB to keep track of whether users are "Active" or not in our LMS. I have a very simple form for our employees to add/remove users and mark them as active or inactive.

I would like to have two text fields in the footer of the form, one that shows the COUNT(*) of all record, and one that shows the total number of active users.



I have a report where I use =Count(*) as the data Control Source for a textbox for the total and =Abs(Sum([Active])) for the total active users. This works beautifully. But I'd rather have it all in one place. So I'd like to put these in the footer of the form.

However, when I do this, both values show up as 0, or sometimes "False". I've tried running a requery on these fields from the code builder on my Add/remove buttons to no avail. Is there any way to get a calculated number in there?

[aytee111]

This will only work like you have it if it is a continuous form. Is it?

[June7]

I just tested this on a Single View form and aggregate calculations in form header/footer worked.

I cannot duplicate your issue. If you want to provide db for analysis, follow instructions at bottom of my post.

[colt64]

It appears to be an issue with the form being set as Data Entry. I tried setting it to "No" and boom, working fine. Unfortunately, for the form to work the way I wanted, it has to be Data Entry. Fortunately, the requirements have changed and this is all a moot point now. Still, I wonder if this is possible with a data entry form.

[June7]

Form set to DataEntry Yes opens with zero records so the Count(*) calculation will be zero (or False - odd). You could try a domain aggregate function (DCount, DSum, DAvg, DMax, etc). It performs calculations on specified domain (a table or query), not the dataset of form or report it sits in. So the function needs criteria provided in the WHERE CONDITION argument if you want to restrict to a sub dataset.

[colt64]

Thank you! Great advice. Ended up using 2 forms, one data entry and one not, since that works better for what I need anyway. DSum seems to come up with #Error when Data Entry is set to Yes still though.

The bigger issue though, is that I need to take the DSum of a boolean field. If I have 50 users, and 25 are marked True for the field "Active" then DSum([Active], "RegisteredUsers") should return -25, but instead it returns -50. This is very important to another calculation I have. Any ideas??

I have tried DSum([Active], "RegisteredUsers", [Active]=-1) as well, with the same result.

[June7]

Domain aggregate functions refer to fields of domain (the table or query) not the form or report.

=DSum("[Active]", "RegisteredUsers", "[Active]=-1")

or

=DCount("[Active]", "RegisteredUsers", "[Active]=-1")

I don't like maintaining multiple identical forms. By use of code I can make one form serve multiple purposes.

[colt64]

You rock. That fixed it. Thank you so much!!