Datasheet view Subform Totals

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ken Mylar - 16 Mar 2006 14:53 GMT

First a quick background on the form:

I have a form that is normally viewed in Single form mode. It has a
subform on it that is in datasheet view. The main form is for work
orders and the subform is for parts used on the work order. On the main
form I have a LaborTotal (from main form), PartsTotal (from subform)
and TotalCost (which is Labor + Parts). All works fine as is.

Now to the question:
I have a button on my main form to browse the work orders, which
basically opens up a copy of the same form but datasheet view, so you
can see all the work orders.

In the PartsTotal and TotalCost sections is #Error until you click the
little "+" sign at the left of the row to open the subform, then it
totals properly, even if you close the subform.

Is there any way to correct this? Without having to open this up and
having to click a couple thousand plus signs?

Any help would be appreciated.

Thanks,
Ken Mylar

Reply to this Message

Wayne Morgan - 16 Mar 2006 15:29 GMT

When in data sheet view, the subform isn't activated until you click the +
signs, as you've noted. I have played with this a little in the past and I
think that what I came up with was to turn off screen echo, open the
subform, close the subform, then turn screen echo on again. As you noted,
once the subform has been opened, it still works, even if you close it
again.

Example:
DoCmd.OpenForm "FormName"
Application.Echo False
Forms!FormName.SubdatasheetExpanded = True
DoEvents
Forms!FormName.SubdatasheetExpanded = False
Application.Echo True

Signature

Wayne Morgan
MS Access MVP

> First a quick background on the form:
>
[quoted text clipped - 20 lines]
> Thanks,
> Ken Mylar

Reply to this Message

Ken Mylar - 16 Mar 2006 16:22 GMT

This took away the #Error in both the Parts and the Totals columns.
However it leaves the Parts blank and gives the Total with only the
Labor, even though there is a part in the subform.

For example:
Results before were:
$120 #Error# #Error
Then when I clicked the "+" to open then close the subform, I got:
$120 $58 $178
Now with the new code for the same record I get:
$120 $120

Maybe it's the way I reference it:
PartsCost =[subfrmWODetailsExtended].[Form]![OrderSubtotal]
TotalCost =nz([Labor Cost])+nz([Parts Cost])

But it works if I manually open and close it.

Thanks for your help,
Ken Mylar

Reply to this Message

Wayne Morgan - 16 Mar 2006 17:32 GMT

This now sounds like a timing issue. Try changing the TotalCost to:

=Nz([subfrmWODetailsExtended].[Form]![OrderSubtotal]) + Nz([Labor Cost])

Also, before the DoEvents command, try a Me.Recalc command. This alone may
solve the problem, without the change above.

Signature

Wayne Morgan
MS Access MVP

> This took away the #Error in both the Parts and the Totals columns.
> However it leaves the Parts blank and gives the Total with only the
[quoted text clipped - 16 lines]
> Thanks for your help,
> Ken Mylar

Reply to this Message

Ken Mylar - 16 Mar 2006 18:31 GMT

Neither one of those worked either.

Here is the code that I had on this browse button before putting any
new code:

*******************************************************************************************************
Private Sub cmdBrowse_Click()

DoCmd.OpenForm "frmWorkOrdersBrowse", acFormDS, , , , acHidden

If IsLoaded("frmWorkOrdersBrowse") Then
Dim F As Form
Set F = Forms!frmWorkOrdersBrowse
F.RecordSource = Me.RecordSource
F.Filter = Me.Filter
F.FilterOn = True
F.OrderBy = Me.OrderBy
F.OrderByOn = True
F.Visible = True
Set F = Nothing
DoCmd.FindRecord Me![WorkOrder#]
End If

End Sub
*************************************************************************************
I've tried to just have the code expand the subforms and leave them all
open, just open and close the subforms, and a bunch of variations of
what you gave me and it always locks out the parts.

Thanks,
Ken

Reply to this Message

Wayne Morgan - 17 Mar 2006 14:00 GMT

The only other thing I can think of would be to force a ReCalc of the
subform then a ReCalc of the main form.

DoCmd.OpenForm "frmWorkOrdersBrowse"
Application.Echo False
Forms!frmWorkOrdersBrowse.SubdatasheetExpanded = True
DoEvents
Forms!frmWorkOrdersBrowse.subfrmWODetailsExtended.Form.Recalc
DoEvents
Forms!frmWorkOrdersBrowse.Recalc
DoEvents
Forms!frmWorkOrdersBrowse.SubdatasheetExpanded = False
Application.Echo True

I said "Me.Recalc" in the last message. The Recalc should actually be for
the main form you're opening.

Signature

Wayne Morgan
MS Access MVP

> Neither one of those worked either.
>
[quoted text clipped - 27 lines]
> Thanks,
> Ken

Reply to this Message

Ken Mylar - 17 Mar 2006 20:03 GMT

I found out what's going on, I'm just not sure how to fix it.

I put the first code you gave me back and still the same results of
course, Parts columns are all blank and the total cost is just a carry
over of the labor.

After looking at this for the last day, I realized that the very first
work order doesn't have any part on it. So for the hell of it I put a
part on the 1st work order and hit browse again. Now instead of all
being blank, they all have the cost of the part on the 1st work order.

The PartsCost points to OrderSubtotal on the subform.
PartsCost =[subfrmWODetailsExtended].[Form]![OrderSubtotal]

This browse form is supposed to be viewed in datasheet but if I open it
in Normal view it works fine.

I'm not sure how to make it look at each part cost instead of just the
first one.

Ken

Reply to this Message

Wayne Morgan - 17 Mar 2006 21:29 GMT

This is because there is really only one control on a continuous form that
is repeated multiple times. So, when the calculation gets the value from
Textbox1, it gets it from the Textbox1 in the current record. To work around
this, use a query to feed the continuous form and for calculations that you
want to occur on each row, do the calculation in the query as a Calculated
Field and bind what is currently the Calculated Control to this new field.
The query will do the calculation one row at a time and since the control on
the form is now bound, it will display the value for that row.

Signature

Wayne Morgan
MS Access MVP

>I found out what's going on, I'm just not sure how to fix it.
>
[quoted text clipped - 17 lines]
>
> Ken

Reply to this Message