stp error message on mainform when subform has no data

[fibayne]

Hi
On the mainform I have a textbox with the following control source =[sfAgedDebt].Form![TotalDue] which is working fine however if TotalDue on the subform is blank the text box on the mainform displays #Error would anyone be able to help me stop the #Error message showing and the Textbox to be blank if the subform is blank ?? as always thanks for your help cheers Fi

 

[MStef]

Try this:
=Nz([sfAgedDebt].Form![TotalDue],0)

 

[fibayne]

Hi MStef thanks for replying, I have tried your suggestion but it is still giving me the #Error message, would you have any other suggestions ?? thanks again Fi

 

[Rabbie]

Try

iif([sfAgedDebt].Form![TotalDue] = "",0,Nz([sfAgedDebt].Form![TotalDue],0))

This should catch the case where the field has a zero length string in it.

 

[fibayne]

Hi Rabbie...thanks for your reply ..tried your suggestion by putting it as the control source of the text box but still getting #Error when there is a blank record in the subform ...the subform is populated from a linked excel spreadsheet with [TotalDue] summing the records in the subform footer I dont think this should cause the #Error but then again !? would you have any further suggestions....thanks again Fi

 

[gemma-the-husky]

because there is no data, the value cannot be looked up (does not exist!) hence the error

perhaps you could call a function, instead of setting the lookup

then in the function you could trap the error as follows - however this wont self refresh, so you would have to find a way to call it again if the subform changes, i think

try this sort of thing

function getsubvalue as double
dim temp as double
onerror goto novalue
temp= subform!requiredvalue

exithere:
getsubvalue=temp

novalue:
temp=0
resume exithere

end function

 

[fibayne]

Hi Gemma..thanks for reply, I copied and pasted your code but the line 'onerror goto novalue' turns red, bit of a novice here is the code I pasted, not sure where to put it and how to relate it to the text box i would likt the value to show !!

Function getsubvalue() As Double
Dim temp As Double
onerror goto novalue ----turns red
temp = SubForm!TotalDue

exithere:
getsubvalue = temp

novalue:
temp = 0
Resume exithere

End Function

if you can help further I'd be really grateful...cheers

Fi

 

[gemma-the-husky]

Function getsubvalue() As Double

Dim temp As Double

[COLOR="red"]on error[/COLOR] goto novalue '----turns red
temp = SubForm!TotalDue

exithere:
getsubvalue = temp

[COLOR="Red"]exit function[/COLOR]

novalue:
temp = 0
Resume exithere

End Function

i hadnt checked the code in action
1 - on error should be two words - you are getting a compile error
2 - you need an exit function, to avoid a permanent loop!

hope this version works

 

[fibayne]

Hi Gemma..thanks for your reply & apologies for not getting back sooner, unfortunately I'm not sure where to put this code ! and how it to make it relate to / show the value in my text box, would you be able to help further...thanks again Fi