#Error in Calculated Control on Continuous Form New Record

[dftjsn]

I have a continuous form that contains an unbound calculated control on each record. For the form, AllowAdditions=True so I get the * on the new empty record at the bottom of the form (which is what I want).

The calculated control works properly for all the records in the continuous form except for the new empty record with the * which displays "#Error". Since it is an empty record and the calculated control relies on another entry in that record, I want the calculated control to be blank if the field it draws upon hasn't been entered.

Any ideas?

Thanks!

 

[PHV]

You may play with the IIf function and the NewRecord property, or simply the Nz function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[randy700]

How about something like this in the forms OnCurrent event...

If IsNull(me.TextboxName) or me.TextboxName = "" Then
me.CalculatedControl = ""
End If


Randy

 

[TheAceMan1]

How are ya dftjsn . . . . .

= Nz((YourCalculation), Null)

Or

= Nz((YourCalculation), "")

See Ya! . . . . . .

 

[dftjsn]

The solution was to set the control source equal to a function that passes as an argument the nz([FieldValue],""). In the function, you do the calculation. The return value of the function is the calculation or "" if the argument coming in was "". Now no more #Error on the new record.

You can't do it in the On Current event because it is a continuous form (same with a datasheet).

I tried doing it all with an iif and nz statements in the control source setting to getting around having to use a function, but couldn't get that to work.

Thanks for all your ideas!

dftjsn