Summing up text boxes

[data808]

I have this in the Lost Focus event:



Private Sub txtSumAmtsDue1_LostFocus()

Me.txtAmtDue = [txtSumAmtsDue1] + [txtSumAmtsDue2] + [txtSumAmtsDue3] + [txtSumAmtsDue4] _
+ [txtSumAmtsDue5] + [txtSumAmtsDue6] + [txtSumAmtsDue7]

End Sub

When I test it out it, the text box txtAmtsDue just goes blank. I also tried using the code above with me.txtSumAmtsDue1 + me.txtSumAmtsDue2 + etc....

with the same results. What am I doing wrong?

[John_G]

Hi -

It's probably because when [txtSumAmtsDue1] loses the focus, the others are still Null, so the sum is Null.

Try using nz([txtSumAmtsDue1],0) + nz([txtSumAmtsDue2],0) + ... to change any Nulls to 0.

Don't forget to include that sum in the LostFocus event of all 7 textboxes.

John

[data808]

You were right! Now I got it to work however, I am now having a problem. Because these text boxes are unbound, they are not tied to any record in particular. So when go to another record, these unbound text boxes stay the same and will confuse the user because the data is static. How can I make it follow a record without having to save it to a table?

[burrina]

Do you mean retain the Old Values? If so, then;

Me.ControlName = Me.ControlName.OldValue

[data808]

Thanks. What event would that go under? Of would it just go under the current event for the form itself?

[data808]

I just tested it in the form's current event and it does not keep the old value. Example, if I enter $35 in the text box, this data will show for every single record that I navigate to. I'm thinking there is no way to assign this unbound data to the record because it is not being saved into the table. I may just have to create these fields in the table then set the source of these text boxes to that just so it can follow the specific record.

[June7]

That is true, unbound controls will show the same value for every record unless the ControlSource is an expression calculating with values of bound controls. How else could an unbound control behave?

[John_G]

Hi -

If I understand you correctly, you do not want to keep those fields' data when moving from one record to another.

All you need to do is set them to 0 in the form's On Current Event:

[txtSumAmtsDue1] = 0
[txtSumAmtsDue2] = 0
...

The On current event will fire every time you move from one record to another, including a new one.

John