Total Field on Form using Subform Total Field Value

H

hdfixitup

I have a form with a subform attached. The subform is always displayed in
datasheet view. I have placed a Total field on the form that references the
Total field on the subform. When the subform has records showing, the Total
field on the form displays the values correctly. However, I get the #Error
message when no records are present in the subform. I have tried IIF
statements using the HasData and IsNull features, only to get #Error. Any
ideas on how to make the Total field on the form read 0 when no records are
present on the subform. I have tried some examples below:

=IIf([Forms]![frmOrder]![fsubOrder].[HasData],[Forms]![frmOrder]![fsubOrder]![Total],0)

=IIf(IsNull([Forms]![frmOrder]![fsubOrder]![Total],0,([Forms]![frmOrder]![fsubOrder]![Total])

#Error on both; any help would be appreciated. Thanks
 
A

Allen Browne

If the subform has no records all (not even the new record), its Detail
section goes completely blank. Referring to the non-existent control in the
subform then gives an error. You cannot solve this with HasData, as the
property applies only to reports.

Instead, test the RecordCount of the Recordset of the Form in the subform
control. Example:

=IIf([fsubOrder].[Form].[Recordset].[RecordCount] = 0, 0,
Nz([fsubOrder].[Form]!Total], 0)

If the .Form bit is new, see:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html
 
O

Ofer Cohen

Try

IIf([fsubOrder].[Form].[RecordsetClone].[RecordCount]=0,0,[Forms]![frmOrder]![fsubOrder]![Total])
 
H

hdfixitup

I tried your suggestion and got a #Name? error. The formula for "Total" on
the subform is =Sum([Ext] and the mane of the total field on the form is
"GTotal". Any other suggestions

Ofer Cohen said:
Try

IIf([fsubOrder].[Form].[RecordsetClone].[RecordCount]=0,0,[Forms]![frmOrder]![fsubOrder]![Total])


--
Good Luck
BS"D


hdfixitup said:
I have a form with a subform attached. The subform is always displayed in
datasheet view. I have placed a Total field on the form that references the
Total field on the subform. When the subform has records showing, the Total
field on the form displays the values correctly. However, I get the #Error
message when no records are present in the subform. I have tried IIF
statements using the HasData and IsNull features, only to get #Error. Any
ideas on how to make the Total field on the form read 0 when no records are
present on the subform. I have tried some examples below:

=IIf([Forms]![frmOrder]![fsubOrder].[HasData],[Forms]![frmOrder]![fsubOrder]![Total],0)

=IIf(IsNull([Forms]![frmOrder]![fsubOrder]![Total],0,([Forms]![frmOrder]![fsubOrder]![Total])

#Error on both; any help would be appreciated. Thanks
Click to expand...
Click to expand...
 
O

Ofer Cohen

Try

IIf([fsubOrder].[Form].[RecordsetClone].[RecordCount]=0,0,[fsubOrder].[Form].[Total])

--
Good Luck
BS"D


hdfixitup said:
I tried your suggestion and got a #Name? error. The formula for "Total" on
the subform is =Sum([Ext] and the mane of the total field on the form is
"GTotal". Any other suggestions

Ofer Cohen said:
Try

IIf([fsubOrder].[Form].[RecordsetClone].[RecordCount]=0,0,[Forms]![frmOrder]![fsubOrder]![Total])


--
Good Luck
BS"D


hdfixitup said:
I have a form with a subform attached. The subform is always displayed in
datasheet view. I have placed a Total field on the form that references the
Total field on the subform. When the subform has records showing, the Total
field on the form displays the values correctly. However, I get the #Error
message when no records are present in the subform. I have tried IIF
statements using the HasData and IsNull features, only to get #Error. Any
ideas on how to make the Total field on the form read 0 when no records are
present on the subform. I have tried some examples below:

=IIf([Forms]![frmOrder]![fsubOrder].[HasData],[Forms]![frmOrder]![fsubOrder]![Total],0)

=IIf(IsNull([Forms]![frmOrder]![fsubOrder]![Total],0,([Forms]![frmOrder]![fsubOrder]![Total])

#Error on both; any help would be appreciated. Thanks
Click to expand...
Click to expand...
Click to expand...
 
H

hdfixitup

Same result #Name? I have checked spelling and NO duplicate names in any
fields. Any more suggestions? I believe the RecordsetClone or RecordCount
is the problem. Without the IIF, Recordset, or 0's, it does not produce a
#Name? error, just #Error.

Ofer Cohen said:
Try

IIf([fsubOrder].[Form].[RecordsetClone].[RecordCount]=0,0,[fsubOrder].[Form].[Total])

--
Good Luck
BS"D


hdfixitup said:
I tried your suggestion and got a #Name? error. The formula for "Total" on
the subform is =Sum([Ext] and the mane of the total field on the form is
"GTotal". Any other suggestions

Ofer Cohen said:
Try

IIf([fsubOrder].[Form].[RecordsetClone].[RecordCount]=0,0,[Forms]![frmOrder]![fsubOrder]![Total])


--
Good Luck
BS"D


:

I have a form with a subform attached. The subform is always displayed in
datasheet view. I have placed a Total field on the form that references the
Total field on the subform. When the subform has records showing, the Total
field on the form displays the values correctly. However, I get the #Error
message when no records are present in the subform. I have tried IIF
statements using the HasData and IsNull features, only to get #Error. Any
ideas on how to make the Total field on the form read 0 when no records are
present on the subform. I have tried some examples below:

=IIf([Forms]![frmOrder]![fsubOrder].[HasData],[Forms]![frmOrder]![fsubOrder]![Total],0)

=IIf(IsNull([Forms]![frmOrder]![fsubOrder]![Total],0,([Forms]![frmOrder]![fsubOrder]![Total])

#Error on both; any help would be appreciated. Thanks
Click to expand...
Click to expand...
Click to expand...
 
O

Ofer Cohen

Can you post what you have now?

Before that, run the form when the are records in the sub form.

Check what the field in the sub form return by openning the immidiate window
(Ctrl+G), type in it

?Forms![MainFormName]!fsubOrder.Form!Total

And press Enter, what did you get back?
Also, in the Sum field write
=Sum(Nz([Ext],0))

To replace Null with 0

--
Good Luck
BS"D


hdfixitup said:
Same result #Name? I have checked spelling and NO duplicate names in any
fields. Any more suggestions? I believe the RecordsetClone or RecordCount
is the problem. Without the IIF, Recordset, or 0's, it does not produce a
#Name? error, just #Error.

Ofer Cohen said:
Try

IIf([fsubOrder].[Form].[RecordsetClone].[RecordCount]=0,0,[fsubOrder].[Form].[Total])

--
Good Luck
BS"D


hdfixitup said:
I tried your suggestion and got a #Name? error. The formula for "Total" on
the subform is =Sum([Ext] and the mane of the total field on the form is
"GTotal". Any other suggestions

:

Try

IIf([fsubOrder].[Form].[RecordsetClone].[RecordCount]=0,0,[Forms]![frmOrder]![fsubOrder]![Total])


--
Good Luck
BS"D


:

I have a form with a subform attached. The subform is always displayed in
datasheet view. I have placed a Total field on the form that references the
Total field on the subform. When the subform has records showing, the Total
field on the form displays the values correctly. However, I get the #Error
message when no records are present in the subform. I have tried IIF
statements using the HasData and IsNull features, only to get #Error. Any
ideas on how to make the Total field on the form read 0 when no records are
present on the subform. I have tried some examples below:

=IIf([Forms]![frmOrder]![fsubOrder].[HasData],[Forms]![frmOrder]![fsubOrder]![Total],0)

=IIf(IsNull([Forms]![frmOrder]![fsubOrder]![Total],0,([Forms]![frmOrder]![fsubOrder]![Total])

#Error on both; any help would be appreciated. Thanks
Click to expand...
Click to expand...
Click to expand...
 
H

hdfixitup

I changed the expression in the Total null values Nz;

I opened the form, Hit Ctrl+G and typed
?Forms![frmOrder]!fsubOrder.Form!Total and an answer below
102.704

There is one record showing at 102.74 on the subform as indicated

I removed the record on the subform;reran Ctrl+G and got

RunTime Error '2427
You entered an expression that has no value.

[Ext] is a calculated field defined in the query of the subform as Ext:
[OnHand]*[Cost]

[Total] is a calculated field on the subform, not in the query itself
=Sum(Nz([Ext],0))

[GTotal] is a field on the Main Form referenced to Total on the subform as:

IIf([fsubOrder].[Form].[RecordsetClone].[RecordCount]=0,0,[fsubOrder].[Form].[Total])

I truly appreciate your patience. Thanks

Ofer Cohen said:
Can you post what you have now?

Before that, run the form when the are records in the sub form.

Check what the field in the sub form return by openning the immidiate window
(Ctrl+G), type in it

?Forms![MainFormName]!fsubOrder.Form!Total

And press Enter, what did you get back?
Also, in the Sum field write
=Sum(Nz([Ext],0))

To replace Null with 0

--
Good Luck
BS"D


hdfixitup said:
Same result #Name? I have checked spelling and NO duplicate names in any
fields. Any more suggestions? I believe the RecordsetClone or RecordCount
is the problem. Without the IIF, Recordset, or 0's, it does not produce a
#Name? error, just #Error.

Ofer Cohen said:
Try

IIf([fsubOrder].[Form].[RecordsetClone].[RecordCount]=0,0,[fsubOrder].[Form].[Total])

--
Good Luck
BS"D


:

I tried your suggestion and got a #Name? error. The formula for "Total" on
the subform is =Sum([Ext] and the mane of the total field on the form is
"GTotal". Any other suggestions

:

Try

IIf([fsubOrder].[Form].[RecordsetClone].[RecordCount]=0,0,[Forms]![frmOrder]![fsubOrder]![Total])


--
Good Luck
BS"D


:

I have a form with a subform attached. The subform is always displayed in
datasheet view. I have placed a Total field on the form that references the
Total field on the subform. When the subform has records showing, the Total
field on the form displays the values correctly. However, I get the #Error
message when no records are present in the subform. I have tried IIF
statements using the HasData and IsNull features, only to get #Error. Any
ideas on how to make the Total field on the form read 0 when no records are
present on the subform. I have tried some examples below:

=IIf([Forms]![frmOrder]![fsubOrder].[HasData],[Forms]![frmOrder]![fsubOrder]![Total],0)

=IIf(IsNull([Forms]![frmOrder]![fsubOrder]![Total],0,([Forms]![frmOrder]![fsubOrder]![Total])

#Error on both; any help would be appreciated. Thanks
Click to expand...
Click to expand...
Click to expand...
 
O

Ofer Cohen

Do ou have an equal sign before the IIf?

=IIf([fsubOrder].[Form].[RecordsetClone].[RecordCount]=0,0,[fsubOrder].[Form].[Total])

--
Good Luck
BS"D


hdfixitup said:
I changed the expression in the Total null values Nz;

I opened the form, Hit Ctrl+G and typed
?Forms![frmOrder]!fsubOrder.Form!Total and an answer below
102.704

There is one record showing at 102.74 on the subform as indicated

I removed the record on the subform;reran Ctrl+G and got

RunTime Error '2427
You entered an expression that has no value.

[Ext] is a calculated field defined in the query of the subform as Ext:
[OnHand]*[Cost]

[Total] is a calculated field on the subform, not in the query itself
=Sum(Nz([Ext],0))

[GTotal] is a field on the Main Form referenced to Total on the subform as:

IIf([fsubOrder].[Form].[RecordsetClone].[RecordCount]=0,0,[fsubOrder].[Form].[Total])

I truly appreciate your patience. Thanks

Ofer Cohen said:
Can you post what you have now?

Before that, run the form when the are records in the sub form.

Check what the field in the sub form return by openning the immidiate window
(Ctrl+G), type in it

?Forms![MainFormName]!fsubOrder.Form!Total

And press Enter, what did you get back?
Also, in the Sum field write
=Sum(Nz([Ext],0))

To replace Null with 0

--
Good Luck
BS"D


hdfixitup said:
Same result #Name? I have checked spelling and NO duplicate names in any
fields. Any more suggestions? I believe the RecordsetClone or RecordCount
is the problem. Without the IIF, Recordset, or 0's, it does not produce a
#Name? error, just #Error.

:

Try

IIf([fsubOrder].[Form].[RecordsetClone].[RecordCount]=0,0,[fsubOrder].[Form].[Total])

--
Good Luck
BS"D


:

I tried your suggestion and got a #Name? error. The formula for "Total" on
the subform is =Sum([Ext] and the mane of the total field on the form is
"GTotal". Any other suggestions

:

Try

IIf([fsubOrder].[Form].[RecordsetClone].[RecordCount]=0,0,[Forms]![frmOrder]![fsubOrder]![Total])


--
Good Luck
BS"D


:

I have a form with a subform attached. The subform is always displayed in
datasheet view. I have placed a Total field on the form that references the
Total field on the subform. When the subform has records showing, the Total
field on the form displays the values correctly. However, I get the #Error
message when no records are present in the subform. I have tried IIF
statements using the HasData and IsNull features, only to get #Error. Any
ideas on how to make the Total field on the form read 0 when no records are
present on the subform. I have tried some examples below:

=IIf([Forms]![frmOrder]![fsubOrder].[HasData],[Forms]![frmOrder]![fsubOrder]![Total],0)

=IIf(IsNull([Forms]![frmOrder]![fsubOrder]![Total],0,([Forms]![frmOrder]![fsubOrder]![Total])

#Error on both; any help would be appreciated. Thanks
Click to expand...
Click to expand...
Click to expand...
 
H

hdfixitup

YES; just forgot to type it in the response. I tried the following;

=IIf([Forms]![frmOrder]![fsubOrder].[RecordesetClone].[RecordCount]=0,0,[Forms]![frmOrder]![fsubOrder]![Total])

I got rid of the #Name? error and back to #Error.

Ofer Cohen said:
Do ou have an equal sign before the IIf?

=IIf([fsubOrder].[Form].[RecordsetClone].[RecordCount]=0,0,[fsubOrder].[Form].[Total])

--
Good Luck
BS"D


hdfixitup said:
I changed the expression in the Total null values Nz;

I opened the form, Hit Ctrl+G and typed
?Forms![frmOrder]!fsubOrder.Form!Total and an answer below
102.704

There is one record showing at 102.74 on the subform as indicated

I removed the record on the subform;reran Ctrl+G and got

RunTime Error '2427
You entered an expression that has no value.

[Ext] is a calculated field defined in the query of the subform as Ext:
[OnHand]*[Cost]

[Total] is a calculated field on the subform, not in the query itself
=Sum(Nz([Ext],0))

[GTotal] is a field on the Main Form referenced to Total on the subform as:

IIf([fsubOrder].[Form].[RecordsetClone].[RecordCount]=0,0,[fsubOrder].[Form].[Total])

I truly appreciate your patience. Thanks

Ofer Cohen said:
Can you post what you have now?

Before that, run the form when the are records in the sub form.

Check what the field in the sub form return by openning the immidiate window
(Ctrl+G), type in it

?Forms![MainFormName]!fsubOrder.Form!Total

And press Enter, what did you get back?
Also, in the Sum field write
=Sum(Nz([Ext],0))

To replace Null with 0

--
Good Luck
BS"D


:

Same result #Name? I have checked spelling and NO duplicate names in any
fields. Any more suggestions? I believe the RecordsetClone or RecordCount
is the problem. Without the IIF, Recordset, or 0's, it does not produce a
#Name? error, just #Error.

:

Try

IIf([fsubOrder].[Form].[RecordsetClone].[RecordCount]=0,0,[fsubOrder].[Form].[Total])

--
Good Luck
BS"D


:

I tried your suggestion and got a #Name? error. The formula for "Total" on
the subform is =Sum([Ext] and the mane of the total field on the form is
"GTotal". Any other suggestions

:

Try

IIf([fsubOrder].[Form].[RecordsetClone].[RecordCount]=0,0,[Forms]![frmOrder]![fsubOrder]![Total])


--
Good Luck
BS"D


:

I have a form with a subform attached. The subform is always displayed in
datasheet view. I have placed a Total field on the form that references the
Total field on the subform. When the subform has records showing, the Total
field on the form displays the values correctly. However, I get the #Error
message when no records are present in the subform. I have tried IIF
statements using the HasData and IsNull features, only to get #Error. Any
ideas on how to make the Total field on the form read 0 when no records are
present on the subform. I have tried some examples below:

=IIf([Forms]![frmOrder]![fsubOrder].[HasData],[Forms]![frmOrder]![fsubOrder]![Total],0)

=IIf(IsNull([Forms]![frmOrder]![fsubOrder]![Total],0,([Forms]![frmOrder]![fsubOrder]![Total])

#Error on both; any help would be appreciated. Thanks
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

Add the .Form bit:
=IIf([Forms]![frmOrder]![fsubOrder].[Form].[RecordesetClone].[RecordCount]=0,0,[Forms]![frmOrder]![fsubOrder].[Form]![Total])

That helps, especially in Access 2003.

If this is Access 2007, it won't work.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

hdfixitup said:
YES; just forgot to type it in the response. I tried the following;

=IIf([Forms]![frmOrder]![fsubOrder].[RecordesetClone].[RecordCount]=0,0,[Forms]![frmOrder]![fsubOrder]![Total])

I got rid of the #Name? error and back to #Error.

Ofer Cohen said:
Do ou have an equal sign before the IIf?

=IIf([fsubOrder].[Form].[RecordsetClone].[RecordCount]=0,0,[fsubOrder].[Form].[Total])

--
Good Luck
BS"D


hdfixitup said:
I changed the expression in the Total null values Nz;

I opened the form, Hit Ctrl+G and typed
?Forms![frmOrder]!fsubOrder.Form!Total and an answer below
102.704

There is one record showing at 102.74 on the subform as indicated

I removed the record on the subform;reran Ctrl+G and got

RunTime Error '2427
You entered an expression that has no value.

[Ext] is a calculated field defined in the query of the subform as Ext:
[OnHand]*[Cost]

[Total] is a calculated field on the subform, not in the query itself
=Sum(Nz([Ext],0))

[GTotal] is a field on the Main Form referenced to Total on the subform
as:

IIf([fsubOrder].[Form].[RecordsetClone].[RecordCount]=0,0,[fsubOrder].[Form].[Total])

I truly appreciate your patience. Thanks

:

Can you post what you have now?

Before that, run the form when the are records in the sub form.

Check what the field in the sub form return by openning the immidiate
window
(Ctrl+G), type in it

?Forms![MainFormName]!fsubOrder.Form!Total

And press Enter, what did you get back?
Also, in the Sum field write
=Sum(Nz([Ext],0))

To replace Null with 0

--
Good Luck
BS"D


:

Same result #Name? I have checked spelling and NO duplicate names
in any
fields. Any more suggestions? I believe the RecordsetClone or
RecordCount
is the problem. Without the IIF, Recordset, or 0's, it does not
produce a
#Name? error, just #Error.

:

Try

IIf([fsubOrder].[Form].[RecordsetClone].[RecordCount]=0,0,[fsubOrder].[Form].[Total])

--
Good Luck
BS"D


:

I tried your suggestion and got a #Name? error. The formula
for "Total" on
the subform is =Sum([Ext] and the mane of the total field on
the form is
"GTotal". Any other suggestions

:

Try

IIf([fsubOrder].[Form].[RecordsetClone].[RecordCount]=0,0,[Forms]![frmOrder]![fsubOrder]![Total])


--
Good Luck
BS"D


:

I have a form with a subform attached. The subform is
always displayed in
datasheet view. I have placed a Total field on the form
that references the
Total field on the subform. When the subform has records
showing, the Total
field on the form displays the values correctly. However, I
get the #Error
message when no records are present in the subform. I have
tried IIF
statements using the HasData and IsNull features, only to
get #Error. Any
ideas on how to make the Total field on the form read 0
when no records are
present on the subform. I have tried some examples below:

=IIf([Forms]![frmOrder]![fsubOrder].[HasData],[Forms]![frmOrder]![fsubOrder]![Total],0)

=IIf(IsNull([Forms]![frmOrder]![fsubOrder]![Total],0,([Forms]![frmOrder]![fsubOrder]![Total])

#Error on both; any help would be appreciated. Thanks
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

Adding data to Subform and save and running a total price calc 0
Delete record error form / subform 1
Populate main form from subform record 0
referencing a value 5
Subform 7
main form totals fail when subform total is null 5
subform total behaving strangely 2
Total Detail on Continuous Form 5

Top