Label Visible based on sum

[jkfeagle]

OK Access wizards, I need your help. I have a form that sums the fields in the form (in the footer obviously). Also in the footer I have a label control that I want visible or invisible based on whether the sum is positive or negative. I'm having trouble with where to put the 'trigger' to get it to update based on when a new record is entered in the form and when the form opens. I've tried putting the condition of if sum > 0 then label is visible, etc. in the load, open, dirty, after update and nothing seems to work. Any guidance?

 

[DCrake]

How are you performing the sum?

If you are using DSum() then at what point are you doing this or is it in the control source of a textbox?

If you use the OnCurrent event and code

If Nz(DSum(),0) < 0 Then
Me.Label.Visible = False
Else
Me.Label.Visible = True
End If

David

 

[jkfeagle]

I was actually doing it in the control source of a textbox so that it automatically updates whenever a new record is entered. I hadn't been using DSum but now that I think about it I probably should (instead of the standard Sum). Would OnCurrent still work or would the control have to be selected to activate it?

 

[jkfeagle]

I tried the following code and am geting this error when opening the form:

"You cancelled the previous operation.


Private Sub Form_Current()
If Nz((DSum("[Credit]", "Q_Account_History")) - (DSum("[Debit]", "Q_Account_History"))) < 0 Then
Me.lblBalanceDue.Visible = True
Else
Me.lblBalanceDue.Visible = False
End If
End Sub

 

[jkfeagle]

Alright. I have this working .....sort of.....by using the after update trigger. The only problem is that it seems to lag by one update. In other words, on the first update it works fine. But any subsequent updates it seems to be one behind in its conditional fomatting. To put it more simply, if I enter a record that makes the value go negative, it won't show lblBalanceDue until the next record is entered, etc. The text box with the sum updates accurately every time so I don't think the problem is there. Any ideas?

Private Sub Form_AfterUpdate()
If Me.txtBalance < 0 Then
Me.lblBalanceDue.Visible = True
Else
Me.lblBalanceDue.Visible = False
End If
End Sub

 

[gemma-the-husky]

is there a syntax error in this - you dont seem to have a default value for the nz function

If Nz((DSum("[Credit]", "Q_Account_History")) - (DSum("[Debit]", "Q_Account_History"))) < 0 Then

if you have on error code (which it sounds like, from the error message you quote) , it is probably intercepting this error.

 

[jkfeagle]

Dave,

I did have an error but abandoned that method. The last posting shows my latest approach. This is what is in the text box.

=Sum([Credit])-Sum([Debit])

This is what is in the afterupdate code

Private Sub Form_AfterUpdate()
If Me.txtBalance < 0 Then
Me.lblBalanceDue.Visible = True
Else
Me.lblBalanceDue.Visible = False
End If
End Sub

I've tried invoking a requery before the conditional statement but that doesn't work. I think I need to better understand the sequence of events when a new record is entered. I assume it automatically requeries and then the summation occurs and that is why it didn't work. Help!!!