#Error on Main Form

[Coldsteel]

Hello All,

I need some help with a form problem. I have a main form which reads data from a sub-form called tbl_referral1. The problem I am having is when there is no data in the sub form I get the #error on a txtbox in my Main form designed to read from the sub-form. I have tried using different iif statments but I keep getting the #error when there is no data. Any help will be greatly appreciated.

Here is my code:

=IIf(tbl_referral1.Form!MTDfundedTTL Is Null,0,tbl_referral1.Form!MTDfundedTTL)

 

[wiklendt]

1) what are you doing this for? if it's already in the subform, why display it again in the main form?

2) try the null handler/operand =Nz()

 

[Coldsteel]

Thanks for the post.
I am trying to build a custom display form

I am still getting the #error here when I placed code in the control source for the Main Form txtbox: =IIf(Nz([MTDfundedTTL]) Is Null,Nz([MTDfundedTTL]),0).

Thanks again.

 

[wiklendt]

no no, null operand works as an if in it's own right. something like this:

=Nz([MTDfundedTTL],"")

look it up for more detail/info.

> i am trying to build a custom display form.

well, obviously! but what i mean is what you are physically/technically trying to do - i.e., are you creating totals? counts? just displaying exising field? and on what kind of relationship? i.e., if it was a customer form with a subform for different contact details (e.g., business, home, etc)... what is is that you are actually displaying? and why?

- if it helps, maybe attach a screen shot? then i'll understand more... (hopefully!)

 

[HiTechCoach]

Hello All,

I need some help with a form problem. I have a main form which reads data from a sub-form called tbl_referral1. The problem I am having is when there is no data in the sub form I get the #error on a txtbox in my Main form designed to read from the sub-form. I have tried using different iif statments but I keep getting the #error when there is no data. Any help will be greatly appreciated.

Here is my code:

=IIf(tbl_referral1.Form!MTDfundedTTL Is Null,0,tbl_referral1.Form!MTDfundedTTL)

What you are missing is the use of the IsError() function.

Try:

=IIf( IsError(tbl_referral1.Form!MTDfundedTTL) = True, 0, Nz(tbl_referral1.Form!MTDfundedTTL,0) )

 

[Coldsteel]

Still no luck with the code.

Here are the two screen shots. Hopes this helps

Thanks Again

 

[wiklendt]

seems to me you don't need the approved textbox in the main form.

first of all, it's clearly seen in the subform data as "approved" as opposed to the main form which only shows "1" - and who knows what that means? (edit: not only that but what happens if there's two records in the subform, one with "approved" and another with "rejected" (or whatever the other options are) - which will the main form display??)

second, if you wanted to keep the form size the same, whether or not the subform has data, you can change the "auto size" property of your main form to "no"... that may fix it (or may not, i'm working of memory here)!

i'd just delete the main form one and save yourself a whole heaps of trouble/time/money/hair

 

[HiTechCoach]

Still no luck with the code.

Here are the two screen shots. Hopes this helps

Thanks Again

I actually copied the code form a working application and change the form reference to yours.

Does it work if there is data in the sub form?

Please copy and paste the code you are using in the control source.

 

[Coldsteel]

Thanks so much for the help. I pasted the code in the wrong area. It works great now!!
I just want to thank you both for all the help and advice you have provided.

Thanks again

 

[HiTechCoach]

You're welcome!

Glad we could assist.