By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,560 Members | 949 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,560 IT Pros & Developers. It's quick & easy.

#Error from no data in a subFORM in Access 2003

P: 58
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.
Nov 27 '06 #1
Share this Question
Share on Google+
13 Replies


NeoPa
Expert Mod 15k+
P: 31,602
What is the Control Source of your control set to?
I don't get that ever with my simple bound controls.
Nov 27 '06 #2

P: 58
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.
Nov 27 '06 #3

P: 58
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.
Nov 27 '06 #4

PEB
Expert 100+
P: 1,418
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????
Nov 27 '06 #5

NeoPa
Expert Mod 15k+
P: 31,602
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.
Nov 27 '06 #6

P: 58
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.

Expand|Select|Wrap|Line Numbers
  1. =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.
Nov 28 '06 #7

NeoPa
Expert Mod 15k+
P: 31,602
Try :
Expand|Select|Wrap|Line Numbers
  1. =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.
Nov 28 '06 #8

P: 1
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
Dec 7 '06 #9

100+
P: 167
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!!):
Expand|Select|Wrap|Line Numbers
  1. =Nz(IIf(subReportName1.Report.HasData, subReportName1.Report!sumControl1, 0) + IIf(subReportName2.Report.HasData, subReportName.Report!sumControl2, 0), 0)
Hope it helps someone out!

Hrvoje
Oct 16 '07 #10

100+
P: 256
hjozinovis's response had a couple of errors (; vs. , and missing ")" etc). This worked for me. Note I changed to my control/form names.
Expand|Select|Wrap|Line Numbers
  1. =Nz(IIf([subrptShopOrderTime].[Report].[HasData],[subrptShopOrderTime].[Report]![SOHours],0))
Dec 18 '18 #11

twinnyfo
Expert Mod 2.5K+
P: 3,325
DanicaDear,

Thanks! I fixed the typos for future generations.

Glad you found a solution that worked!
Dec 18 '18 #12

100+
P: 256
Good, because it looks like I may be missing an extra 0 on the end myself, to complete the Nz command. Thanks twinnyfo!
Dec 18 '18 #13

twinnyfo
Expert Mod 2.5K+
P: 3,325
Glad I could hepp!

You hepped, too! You found the typo!
Dec 18 '18 #14

Post your reply

Sign in to post your reply or Sign up for a free account.