#Error from no data in a subFORM in Access 2003

[apartain]

I searched the Help files and found that you can get around the #Error result when trying to calculate data from a subREPORT which has no data with the HasData property, but can not figure out how to do it with a subform.
Microsoft Help

I even tried to set the visible property to no if it didn't hold a value, but apparantly Access considers "#Error" as a value.

I do not know VBA, but am trying to learn. If the solution requires a great deal of VBA, please explain the details.

Thank you.

[NeoPa]

What is the Control Source of your control set to?
I don't get that ever with my simple bound controls.

[apartain]

I have six subforms. The control is adding the sum from each of them. Sometimes one or more of the subforms has no data, hence #Error.

I've tried to make the control (which is located on the main form) invisible and set the visible property to yes if it's value is greater than zero, but get a type mismatch error.

I've tried conditional formatting, making the text the same color as my form's background, then change it to red if the value is greater than zero, but it doesn't work at all.

I've tried dealing with nz in all of my subforms.

I see there is a HasData property for subreports, but don't know if that applies to subforms, as well. No matter, I can't get it to work for the subreport, either.

[apartain]

What is the Contro Source of your control set to?
I don't get that ever with my simple bound controls.

By the way, this is an unbound control.

[PEB]

U don't have any formula in the respective control /as control source/ and it is unbound control and u get Error???

Very strange!!!

Have u indicated a special formatting for a number and having a default value text or something simillar????

[NeoPa]

I think there is a formula PEB.
It's unbound but has a formula which refers to controls in six subforms.
It would be helpful to see the formula - I expect it's complicated.
Please use the CODE tags.

[apartain]

You are correct. It does have a formula but is not bound to any table or query. It's not even a complicated formula, just a sum of 3 fields from subforms in the same main form.

=nz(Forms![Work Order Summary Form]![Time Transaction Sum for WO Summary]!Text26)+nz(Forms![Work Order Summary Form]![Admin Material Sum 2]!Text5)+nz(Forms![Work Order Summary Form]![Work Order Summary Equipment subform]!Text7)

The formula does work when all of the subforms have data. It's when one or more of the subforms does not have data I receive #Error in the field.

[NeoPa]

Try :

=Nz(Forms![Work Order Summary Form].[Time Transaction Sum for WO Summary].Text26,0)+Nz(Forms![Work Order Summary Form].[Admin Material Sum 2].Text5)+Nz(Forms![Work Order Summary Form].[Work Order Summary Equipment subform].Text7)

If this returns a #Error then we know we're dealing with a value other than Null.

[cde]

I had this same problem with a set of subForms on a program I migrated from Access 2 to Access 2003. I found that setting the properties on the subforms to allow Edits and allow Additions etc. cleared the #error on fields refering to the subform. The same program under Access 2 worked and did not require the property reset, everything else was the same even the code of the fields showing the errors. I also used Nz() and the original code using IIF with Is Null. I also noticed the Nz() function is very very slow compared to IIF.

I hope this helps - I struggled on this for hours.

cde

[hjozinovic]

Hi there! I just solved the same problem with subreports and wanted to give something back too :-)

My situation was as follows: I had Main report and two subreports which calculated totals. I had one control on my Main report that calculated sum from both subreports.
When subreport was blank (had no data) the control on Main report was showing #Error.

To solve this put in Main controls record source the following statement (tip: press SHIFT+F2 for zoom!!):

=Nz(IIf(subReportName1.Report.HasData, subReportName1.Report!sumControl1, 0) + IIf(subReportName2.Report.HasData, subReportName.Report!sumControl2, 0), 0)

Hope it helps someone out!

Hrvoje

[DanicaDear]

hjozinovis's response had a couple of errors (; vs. , and missing ")" etc). This worked for me. Note I changed to my control/form names.

=Nz(IIf([subrptShopOrderTime].[Report].[HasData],[subrptShopOrderTime].[Report]![SOHours],0))

[twinnyfo]

DanicaDear,

Thanks! I fixed the typos for future generations.

Glad you found a solution that worked!

[DanicaDear]

Good, because it looks like I may be missing an extra 0 on the end myself, to complete the Nz command. Thanks twinnyfo!

[twinnyfo]

Glad I could hepp!

You hepped, too! You found the typo!