#Error when no items are in stock. I want to display 0 instead

[joecosmides]

I have a form that has a subform. On the sub form it is setup as a
datasheet. There is a Quantity field and a Description field on that
sub form. I can calculate the totals with an unbound field named
Total1. I tried using this code. I tried using this code in the
control source of the unbound field:

=Sum(nz([Quantity],0))

On the main form I use another unbound field called Total2. The
control source is =SubFormF.Form!Total

Whenever there are no items on the list I always get a #Error in
Total2. I'm trying to make is show 0 or even the word "zero" would be
ok.

Thanks!

 

[Clifford Bass]

Hi Joe,

It looks like you are missing the 1 on the end of the subform's field
name.

=SubFormF.Form!Total1

 

[joecosmides]

Sorry, It's actually Total, not Total1.

 

[Ken Sheridan]

Two possible solutions spring to mind:

1. Trap the error with the IsError function in the expression used as the
control's ControlSource:

=IIf(IsError(SubFormF.Form!Total),0,SubFormF.Form!Total)

2 Add a function to the parent form's module:

Private Function GetTotal()

Dim frm As Form

Set frm = Me.SubFormF.Form

frm.Recalc
On Error Resume Next
GetTotal = frm.Total
If Err <> 0 Then GetTotal = 0

End Function

The call the function as the text box's ControlSource property:

=GetTotal()

You may find that there's some slight performance deficit whichever you use.

Ken Sheridan
Stafford, England

 

[Clifford Bass]

Hi Joe,

Curious. It works fine for me in Access 2007. What version are you
using? I did get the #Error when I mistyped a field name somewhere. Where
did you place the Total1 text box? If you temporarily shift the subform to
continuous forms view, does the value show up correctly in the Total1 text
box?

Clifford Bass

 

[Ken Sheridan]

It won't work if the subform's recordset is not updatable or if its
AllowAdditions property is False, as if the subform has no records there is
then nothing to reference, so I'd guess one of these is the case here.

Ken Sheridan
Stafford, England

 

[joecosmides]

Two possible solutions spring to mind:

1.  Trap the error with the IsError function in the expression used as the
control's ControlSource:

=IIf(IsError(SubFormF.Form!Total),0,SubFormF.Form!Total)

2  Add a function to the parent form's module:

Private Function GetTotal()

    Dim frm As Form

    Set frm = Me.SubFormF.Form

    frm.Recalc
    On Error Resume Next
    GetTotal = frm.Total
    If Err <> 0 Then GetTotal = 0

End Function

The call the function as the text box's ControlSource property:

=GetTotal()

You may find that there's some slight performance deficit whichever you use.

Ken Sheridan
Stafford, England

I have a form that has a subform. On the sub form it is setup as a
datasheet. There is a Quantity field and a Description field on that
sub form. I can calculate the totals with an unbound field named
Total1. I tried using this code. I tried using this code in the
control source of the unbound field:

=Sum(nz([Quantity],0))

On the main form I use another unbound field called Total2. The
control source is =SubFormF.Form!Total

Whenever there are no items on the list I always get a #Error in
Total2. I'm trying to make is show 0 or even the word "zero" would be
ok.

Thanks!- Hide quoted text -

- Show quoted text -

I'm using your IFF statement. For some reason, it shows a value of 0
even if there are items on the subform list. It also shows 0 if there
are no items on the list (which is good).

 

[joecosmides]

Hi Joe,

     Curious.  It works fine for me in Access 2007.  What version are you
using?  I did get the #Error when I mistyped a field name somewhere.  Where
did you place the Total1 text box?  If you temporarily shift the subform to
continuous forms view, does the value show up correctly in the Total1 text
box?

            Clifford Bass





- Show quoted text -

I accidently changed the query on the sub form and it did not have the
quantity field in it. It works great now, Thanks

 

[Clifford Bass]

Hi Joe,

Glad to hear it's working. Glad to help!

Clifford Bass