How to Update Sum() on a Continuous Form?

[melissa_may1]

Hi All!

I'm working with Access 2007 on a form that is used to select the accounts to be printed.

I've set up the form as "Continuous Forms" with a checkbox next to the account name. In the footer I have a "Total To Be Printed" text box, with the data source set to
=Abs(Sum([PRINTNOW]))

I'm using the Abs function because Sum is totaling the value of all of the checkboxes, and when the checkbox is ticked, it has a value of -1.

The form displays all of the accounts, and the total is printing in the footer.

Everything's great, right?

Well, almost!

The total in the footer is not calculated until the user clicks on a different row after checking or unchecking the box.

How can I make the total update after a checkbox is changed?

I've tried a number of ways, by putting code in the OnClick or AfterUpdate, but I can't seem to get the method of updating correct.

I was using txtCount = Abs(Sum([IPRINTNOW])) but Access doesn't like this, and I get a "Sub or Function Undefined" on the Sum() function.

I also tried making a query to return the Abs(Sum()), but I can't figure out how to get the query to update the textbox. This doesn't work either:

txtCount = DoCmd.OpenQuery("GetSum")

Any ideas?

Thanks!

 

[vbaInet]

If Me.Dirty Then Me.Dirty = False

Put the above code in the After Update event of the checkbox.

 

[Privateer]

Melissa,
Access is very much like Excel here. When you type in a number, it is not really in the cell untill you hit enter or move to another cell. In the checkbox afterupdate, try me.refresh, it sends any values from the form to the table, like an enter.

Also, the negative one in Access is Microsoft speak for "True" and zero is "False", so you can also run IF statements checking for T/F if you want to.

Privateer

 

[melissa_may1]

Hi vbaInet and Privateer,

Great!!

Both of your suggestions work!

Privateer's actually updates the Sum() but the display flickers a slight bit, while vbaInet's does not.

However, Privateer's updates immediately, while vbaInet's delays about a second.

I'd rather avoid the flicker, so I'm going to use vbaInet's.

But, I'd love to know why and how it works!

If Me.Dirty Then Me.Dirty = False is a bit counterintuitive for me!

Also, Privateer, thanks for the extra info on the True/False thing. I find it hard to keep track of what the yes//no value is in different languages (I work in PHP and others pretty regularly) so I usually say =0 or <>0 and that covers it!


Thanks to both of you!

 

[vbaInet]

If you're not in a multi-user environment then you will be fine using Me.Refresh. If this is not the case then you should be using the Dirty code I gave or the RunCommand constant, acCmdSaveRecord.

Refresh doesn't take into account new records and there are a few other cases where a Refresh will not produce the desired results.

As per the Yes/No field, I just wanted to clarify that it's not "True" and "False" strings Privateer is referring to. It's True/False boolean, Yes/No or -1/0 for querying a Yes/No data type. I prefer -1/0 because that's the underlying value of that data type.

 

[melissa_may1]

Hi vbaInet,

Thanks for the follow-up!

This application is only single-user at the moment, but we were discussing today an upgrade to multi-user. So, good that I chose the me.dirty way. (That sounds so weird.. "Me Dirty!"

Can you explain how and why it works?

I don't understand the idea that if the form's dirty (meaning one or more fields changed) then making it "un-dirty" does anything useful. I'd think that this would actually mess things up...

Then again, I suppose that the Access-to-English translation is going to be "make it un-dirty by writing the record out"

Thanks, too, for the comments on the Yes/No datatype. I do get that the underlying value is -1 or 0. But I know at least one database where the boolean value is actually zero or non-zero.

While my experience is that "non-zero" is usually -1, that isn't always the case, and in fact can also be a value such as 1, as it is in PHP. That's why, whenever I have to test the numeric value of a Yes/No or boolean type, I just look for zero and non-zero.

In PHP, for example, the equivalent "Yes/No" type is the Boolean or Bool Type, for which False can be any one of: False, 0, "0" (the string character 0), and empty string, or a null.

In MySQL, however, there's no actual Boolean. The Boolean data type is in fact a synonym for a one-place tiny integer, which evaluates to 0 for False, and "non-zero" for True.

Of course, the best idea is to test for True and False, and then whatever language you're in will do the correct evaluation!

I just wish they had a data type for "confused/not-confused", since I'd need it every day...

 

[vbaInet]

(That sounds so weird.. "Me Dirty!"

I know it does

Then again, I suppose that the Access-to-English translation is going to be "make it un-dirty by writing the record out"

Almost! It's more like, "commit changes to the db, then un-dirty me".

Thanks, too, for the comments on the Yes/No datatype. I do get that the underlying value is -1 or 0. But I know at least one database where the boolean value is actually zero or non-zero.

Just note that it's not a Boolean data type. It's just an Integer type masked as Yes/No and made to interpret the boolean True/False as Yes/No.

That's why, whenever I have to test the numeric value of a Yes/No or boolean type, I just look for zero and non-zero.

Yes, that's a safe interpretation in many environments, but not in VBA.

I just wish they had a data type for "confused/not-confused", since I'd need it every day...

Oh yes it does, just Format -1 as Confused and 0 to Not Confused.