Dsum Help! Simple I Hope...

[vidus]

Hey guys, I'm just writing a simple dsum, but I'm not sure how to continue.

Im trying to have a text box display a sum of the field [JobValue]. But, there is a field beside that called [Invoiced] which is a "Yes/No" field. I want the new text box to display the sum of [JobValue] only for jobs that have the [Invoiced] setting to "No".

I have so far just the sum:

=DSum("[JobValue]","[Job Board]")

I have played around a bit but have gotten nowhere with excluding jobs that are already invoiced... not sure where to go from here.. any advice?

 

[boblarson]

How about this:

=DSum("[JobValue]","Job Board", "[Invoiced]=False")

 

[vidus]

How about this:

=DSum("[JobValue]","Job Board", "[Invoiced]=False")

See that's what I had but it doesnt work.. BUT I just tried replacing False with No and it works.

It doesn't seem to update when you change a Jobs Invoiced status though... is there a after update code I can add to the Invoiced selection box?

 

[boblarson]

See that's what I had but it doesnt work.. BUT I just tried replacing False with No and it works.

It doesn't seem to update when you change a Jobs Invoiced status though... is there a after update code I can add to the Invoiced selection box?

You would have to save the record first so in the Invoiced selection checkbox's after update event you can put:

If Me.Dirty Then Me.Dirty = False

to force a save.

 

[vidus]

You would have to save the record first so in the Invoiced selection checkbox's after update event you can put:

If Me.Dirty Then Me.Dirty = False

to force a save.

We have other dirty code that runs already upon any change to the form (changes background color etc.. we have probs with people not understanding when there editing ) So this does not work in any case, it will only update the text box if you close the table and re open it.

 

[boblarson]

If you want the calculated control to change, you need to force a save after the checkbox, or move to another record (which will force the save as well). If you do not, then it cannot update the control because it is a domain aggregate which is dependent upon the saved records to calculate. If you need to, you can force the save and then explicitly set a

Me.Recalc

which should recalculate any controls with calculations in them. If you cannot save the record after the selection, due to other issues, you will not have any ability to make the text box change whatsoever until the record has been saved.

 

[vidus]

or move to another record (which will force the save as well).

Yes this is how it works now. But even after moving to a new record, the text box does not update.

Where can I place this Me.Recalc to force a recalc after moving fields?

 

[boblarson]

Yes this is how it works now. But even after moving to a new record, the text box does not update.

Where can I place this Me.Recalc to force a recalc after moving fields?

You can put it in the form's After Update event. You could try the On Current event too, but that might cause a never ending loop.

 

[vidus]

You can put it in the form's After Update event. You could try the On Current event too, but that might cause a never ending loop.

Perfect. This works great!

Thanks for your help boblarson, appreciate it.

 

[boblarson]

Glad we could help out.