I originally wrote a very long version of this question...let's see if I can be more effecient and still get help.
I need to toggle the visibility of a subform OR a label control on a subform based on the value to a calculated text box on the subform. The difficulty is that the text box often has a null value and seems to not respond to any IF/ELSE statements where the the condition IsNull is true (it responds fine when IsNull is false).
I thought I could try forcing a value with the NZ function, but I can't find anything that is consistent and clear as to the syntax to even try.
I did some searching... I have had calculated text-boxes in the past that I wanted to trigger certain items... So, I went to the OnChange event of a text-box on a Form. Here is a note that directly speaks to your problem...
Setting the value of a control by using a macro or Visual Basic doesn't trigger this event (the onChange event) for the control. You must type the data directly into the control, or set the control's Text property.
In other words, what I am seeing is...
If you have code in a calculated-text-box, you can't get that code to happen without ACTUALLY TYPING IN THAT BOX...
Everything sorta depends on how you set up your calculated text-box. There are three basic ways of setting up a calculated text-box.
One) You set the box to calculate every time the record changes. For instance... As the user hits next-record on the form, the calculated text-box adds up other fields on the form and calculates a total...
If this is the case with your calculated text-box, then try putting some code in your form under the AfterUpdate event. Yes, you will have to RE-calculate the data for the text-box, in fact you might want to change the text-box to a NON-Calculated Text-Box and Fill it in through the AfterUpdate on the form... This way you can make your code happen AS the text-box updates!
Two) THe user fills in other fields on the form. Then the user presses a button to calculate the calculated text-box.
If THIS is how you are generating your calculated text-box, just put the code in the Button's OnClick event and have it trigger then.
Three) The form has several fields in it. Some text-boxes, some checkboxes, some drop-downs... etc... As the user enters data into these fields, the calculated text-box slowly calculates based on the entries / updates to the other fields on the form. So, to sum it up, the calculated text-box updates itself BASED ON OTHER FIELDS.
If THIS is how your calculated text-box is done then there is an alternative workaround. Create a function. List out all of the other fields that affect the calculated total in the calculated text-box. In your function, go through that list and get your calculation... drop the calculated total into the calculated text-box... ALSO check... if your total is a total you want to react to, you can react to it now.
In other words...
Code:
Private Function CalculateYourTotal()
Dim RunningTotal as Integer
// You can DIM this as a Long or whatever else you need...
// It does NOT need to be an Integer
if IsDirty(me.field_to_check_number_one) Then
// Do this if Field Number One is Dirty...
RunningTotal = <insert your calculation here>
// For your information,
//IsDirty checks to see if the field has been changed...
End If
if IsDirty(me.field_to_check_number_two) Then
// Do this if Field Number Two is Dirty...
RunningTotal = <insert your calculation here>
End If
etc, etc...
End Function
THEN, once you have the function written, you call the CalculateYourTotal() function in the onChange event for each field that you use to calculate your total.
I hope that helps....
Feel free to tell me if it doesn't help...
Good Luck to ya...
