What to do when subreport used as textbox source has no data

N

Nancy Lytle

I have a main report with 3 subreports, all are related by the field
ProgramCode. There is a record in three of the reports (Main and 2
subreports) for every ProgramCode. I also use the values in the subreports
to populate a text box - txtAB = Sub1.field + sub2.field- sub3.field -
sub3.field. This works fine except for the case where the ProgramCode has
no record in Subreport3, then that subreport doesn't show and the textbox is
filled with error#. How do I get the textbox to use txtAB = Sub1.field +
sub2.field where there is no sub report for the linked field.

Hope I explained this okay.
TIA
Nancy L
 
D

Duane Hookom

You can set the control source on the main report to use the HasData
property of the subreport:
=IIf([srptMySub].Report.HasData, [srptMySub].Report.txtTotal, 0)
 
N

Nancy Lytle

That works, but now for the times when there is a subreport with data, the
txtbox come up with "error#".
This is the code I used on the Control Source for the txtAB on the Main
form.

=[njl_qryProgInitAuth].[Report]![Initial
Authorization]+[njl_qryProgChanges].[Report]![SumOfAuthorizationChanges]-IIf
([rptExec_SP].[Report].[HasData],[rptExec_Sp].[Report]![SumComm],0)-IIf([rpt
Exec_SP].[Report].[HasData],[rptExec_Sp].[Report]![SumOblig],0)

Any ideas?
Thanks,
N Lytle
Duane Hookom said:
You can set the control source on the main report to use the HasData
property of the subreport:
=IIf([srptMySub].Report.HasData, [srptMySub].Report.txtTotal, 0)

--
Duane Hookom
MS Access MVP


Nancy Lytle said:
I have a main report with 3 subreports, all are related by the field
ProgramCode. There is a record in three of the reports (Main and 2
subreports) for every ProgramCode. I also use the values in the subreports
to populate a text box - txtAB = Sub1.field + sub2.field- sub3.field -
sub3.field. This works fine except for the case where the ProgramCode has
no record in Subreport3, then that subreport doesn't show and the
Click to expand...
textbox
is
filled with error#. How do I get the textbox to use txtAB = Sub1.field +
sub2.field where there is no sub report for the linked field.

Hope I explained this okay.
TIA
Nancy L
Click to expand...
Click to expand...
 
D

Duane Hookom

I can't see anything wrong with thesyntax but I can't check the spelling.
Are you adding totals from 4 subreports?

--
Duane Hookom
MS Access MVP


Nancy Lytle said:
That works, but now for the times when there is a subreport with data, the
txtbox come up with "error#".
This is the code I used on the Control Source for the txtAB on the Main
form.

=[njl_qryProgInitAuth].[Report]![Initial
Authorization]+[njl_qryProgChanges].[Report]![SumOfAuthorizationChanges]-IIf([rptExec_SP].[Report].[HasData],[rptExec_Sp].[Report]![SumComm],0)-IIf([rpt
Exec_SP].[Report].[HasData],[rptExec_Sp].[Report]![SumOblig],0)

Any ideas?
Thanks,
N Lytle
Duane Hookom said:
You can set the control source on the main report to use the HasData
property of the subreport:
=IIf([srptMySub].Report.HasData, [srptMySub].Report.txtTotal, 0)

--
Duane Hookom
MS Access MVP


Nancy Lytle said:
I have a main report with 3 subreports, all are related by the field
ProgramCode. There is a record in three of the reports (Main and 2
subreports) for every ProgramCode. I also use the values in the subreports
to populate a text box - txtAB = Sub1.field + sub2.field- sub3.field -
sub3.field. This works fine except for the case where the ProgramCode has
no record in Subreport3, then that subreport doesn't show and the
Click to expand...
textbox
is
filled with error#. How do I get the textbox to use txtAB =
Click to expand...
Click to expand...
Sub1.field
+
Click to expand...
Click to expand...
 
N

Nancy Lytle

The totals are from 3 subreports, the first two come from separate
subreports and the last two come from one subreport.
Thanks,
Nancy L

Duane Hookom said:
I can't see anything wrong with thesyntax but I can't check the spelling.
Are you adding totals from 4 subreports?

--
Duane Hookom
MS Access MVP


Nancy Lytle said:
That works, but now for the times when there is a subreport with data, the
txtbox come up with "error#".
This is the code I used on the Control Source for the txtAB on the Main
form.

=[njl_qryProgInitAuth].[Report]![Initial
Click to expand...
Authorization]+[njl_qryProgChanges].[Report]![SumOfAuthorizationChanges]-IIf([rptExec_SP].[Report].[HasData],[rptExec_Sp].[Report]![SumComm],0)-IIf([rpt
Exec_SP].[Report].[HasData],[rptExec_Sp].[Report]![SumOblig],0)

Any ideas?
Thanks,
N Lytle
Duane Hookom said:
You can set the control source on the main report to use the HasData
property of the subreport:
=IIf([srptMySub].Report.HasData, [srptMySub].Report.txtTotal, 0)

--
Duane Hookom
MS Access MVP


I have a main report with 3 subreports, all are related by the field
ProgramCode. There is a record in three of the reports (Main and 2
subreports) for every ProgramCode. I also use the values in the
subreports
to populate a text box - txtAB = Sub1.field + sub2.field- sub3.field -
sub3.field. This works fine except for the case where the
Click to expand...
ProgramCode
has
no record in Subreport3, then that subreport doesn't show and the textbox
is
filled with error#. How do I get the textbox to use txtAB =
Click to expand...
Sub1.field
+
sub2.field where there is no sub report for the linked field.

Hope I explained this okay.
TIA
Nancy L
Click to expand...
Click to expand...
Click to expand...
 
N

Nancy Lytle

Thanks Duane, my reference to the subreport was incorrect so I went in and
corrected it and all is well,.
I've never used IIf before, so I am glad to learn a new trick.

Thanks again,
Nancy L
Duane Hookom said:
I can't see anything wrong with thesyntax but I can't check the spelling.
Are you adding totals from 4 subreports?

--
Duane Hookom
MS Access MVP


Nancy Lytle said:
That works, but now for the times when there is a subreport with data, the
txtbox come up with "error#".
This is the code I used on the Control Source for the txtAB on the Main
form.

=[njl_qryProgInitAuth].[Report]![Initial
Click to expand...
Authorization]+[njl_qryProgChanges].[Report]![SumOfAuthorizationChanges]-IIf([rptExec_SP].[Report].[HasData],[rptExec_Sp].[Report]![SumComm],0)-IIf([rpt
Exec_SP].[Report].[HasData],[rptExec_Sp].[Report]![SumOblig],0)

Any ideas?
Thanks,
N Lytle
Duane Hookom said:
You can set the control source on the main report to use the HasData
property of the subreport:
=IIf([srptMySub].Report.HasData, [srptMySub].Report.txtTotal, 0)

--
Duane Hookom
MS Access MVP


I have a main report with 3 subreports, all are related by the field
ProgramCode. There is a record in three of the reports (Main and 2
subreports) for every ProgramCode. I also use the values in the
subreports
to populate a text box - txtAB = Sub1.field + sub2.field- sub3.field -
sub3.field. This works fine except for the case where the
Click to expand...
ProgramCode
has
no record in Subreport3, then that subreport doesn't show and the textbox
is
filled with error#. How do I get the textbox to use txtAB =
Click to expand...
Sub1.field
+
sub2.field where there is no sub report for the linked field.

Hope I explained this okay.
TIA
Nancy L
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Totalling Row Data From Subreports 6
Displaying blank space when no data in subreport 5
Nested Subreports 1
Error in totals when no data in subreport 1
SynNeed to account for null values in subreports 2
value from subreport to be used in main 1
Conditional Subreport Record Source 2
Subreport with no records 8

Top