"#Error" shows up on txtbox instead of "nothing"

[MsLady]

Hello friends

Can anyone direct me no how to get rid of the text "#Error" showing up on my form field when there's no value. This is the txtbox on my main form and it's looking up its value from another txtbox on my subform.

The main form textbox produces #error when there is no record on the txtbox on the subform. It's controlsource is:

=subfrmWorkDone.Form!txtGrandTotalTime

The controlsource on the subform txtbox (where the value is wants to be gotten from is):

=(Format(Sum([Timestop]-[timestart]),"h") & " Hours" & " " & Format(Sum([Timestop]-[timestart]),"n") & " Mins")

and it works perfectly and simply doesn't show anything when there is no value.

How can i get rid of this #Error. It's not really an error cos it produces the value from the subfrm correctly when there's a value on the sufrm txt, when there's no value, i get the #error on the mainform (but a blank shows on the subfrm, which is what i want).

The #error will confuse any user to think my form has a problem when it really does not.

I have tried wrapping it in a ISNULL IIf and this

=IIf(subfrmWorkDone.Form!txtGrandTotalTime=Null,"  ",subfrmWorkDone.Form!txtGrandTotalTime)

but no luck. Any more ideas?

 

[Banana]

Perhaps instead of using expressions for that textbox, use OnCurrent Event to check for value in the subform's textbox and place it in the textbox or leave it empty if there's nothing there.

I don't know why one expression would come up with empty while another returns an error, though.

 

[RuralGuy]

MsLady,
*Nothing* is ever = to Null, including Null. If Null = Null returns false. Null is the absence of "anything" and not defined.

=IIf(subfrmWorkDone.Form!txtGrandTotalTime=Null," ",subfrmWorkDone.Form!txtGrandTotalTime)

Try:
=IIf(IsNull(subfrmWorkDone.Form!txtGrandTotalTime)," ",subfrmWorkDone.Form!txtGrandTotalTime)

 

[MsLady]

Thanks Banana
I tried this onCurrent, but no luck, still same thing

Private Sub Form_Current()
On Error Resume Next
'Dim myGT As String
'myGT = Form_subfrmWorkDone.txtGrandTotalTime.Value
If IsNull(subfrmWorkDone.Form!txtGrandTotalTime) = False Then
Me.GrandTotalTime.ControlSource = "=subfrmWorkDone.Form!txtGrandTotalTime"
Else
Me.GrandTotalTime.Value = ""
End If

Hi Rural dude .
Thanks for that short lesson. I have tried that, that's what i tried initally, no luck either.

Thanks buddies! Much appreciation.
Any more ideas?

The error is really mysterious.

 

[pbaldy]

http://www.mvps.org/access/forms/frm0022.htm

 

[RuralGuy]

Hmmm...
How about:
=IIf(IsError(subfrmWorkDone.Form!txtGrandTotalTime) ," ",subfrmWorkDone.Form!txtGrandTotalTime)

Oops...Paul beat me to it.

 

[MsLady]

Thank you thank you!!!

http://www.mvps.org/access/forms/frm0022.htm

Thanks for the link pbaldy, that was quite informative.

Hmmm...
How about:
=IIf(IsError(subfrmWorkDone.Form!txtGrandTotalTime) ," ",subfrmWorkDone.Form!txtGrandTotalTime)

Oops...Paul beat me to it.

You are too much. I simply plugged this in. works like magic!

Thanks again friends: you guys made my day! hehe