Datasheet view Subform Totals

[Ken Mylar]

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

[Wayne Morgan]

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

--
Wayne Morgan
MS Access MVP
"Ken Mylar" <km****@elch.org> wrote in message
news:11*********************@j52g2000cwj.googlegro ups.com...

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

[Ken Mylar]

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

[Wayne Morgan]

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.

--
Wayne Morgan
MS Access MVP
"Ken Mylar" <km****@elch.org> wrote in message
news:11*********************@u72g2000cwu.googlegro ups.com...

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

[Ken Mylar]

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

[Wayne Morgan]

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.

--
Wayne Morgan
MS Access MVP
"Ken Mylar" <km****@elch.org> wrote in message
news:11********************@i39g2000cwa.googlegrou ps.com...

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

[Ken Mylar]

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

[Wayne Morgan]

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.

--
Wayne Morgan
MS Access MVP
"Ken Mylar" <km****@elch.org> wrote in message
news:11**********************@j33g2000cwa.googlegr oups.com...

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