Subform Subform Subform

[gmenon100 via AccessMonster.com]

Dear Friends,

Thanks in advance for any response from you.

I have a peculiar situation: I am using ADP with SQL backend.

I have a form Shpmtfrm, which has a subform EntrySubfrm, which inturn has a
subform InvSubsubfrm, which has a subform Stydetsubsubsubfrm.

I have designed this in such that when the shpmtfrm opens, an employee can
enter styles, qty and price in stylesubsubsubfrm. Based on this information,
InvSubsubfrm updates its totalqty, invval field adding all style qty totals
and invvalue. The employee can then add/create new invoices. After each such
invoice is created, the totals for all such invoices are updated on the
EntrySubfrm "totalinvval" field and "AllInvQty" field. The employee can then
add/create new Entry record and the totals of invvalue, qty is summed on the
Shpmtfrm.

In short, Each shipment can have one or more Entry records and each Entry
record can have one or more invoices and similarly each invoice can have one
or more styles. And I need to have the complete summed figures on the
Shpmtfrm.

Each of the form is bound to a table or a query. I am not sure if I have
presented sufficient information.

Appreciate any response.
Gmenon

 

[Sylvain Lafontaine]

Three choices:

1- You perform the calculation in the source (sql statement, stored
procedure, etc.) of each form/subform and display them in a bound control.

2- You add text boxes and use the aggregate functions such as Sum and Count
as the Control Source:

= Count ([IdInvoice])

Notice that you cannot write things like Count(*) or with more complex
expressions inside the parenthesis (like Count(iif([IdInvoice]>0,1,0) ) but
you can write expressions like:

=Sum([Repas1]) + Sum([Repas2]).

=2*[TextNbRepas]

=2*[Form]![TextNbRepas]

=2*[Forms]![f_Choix]![TextNbRepas]


Notice that you cannot use [Me] in replacement of [Form] or [Report]. For
the subforms, you will have to write the name of the control containing the
subform followed by [Form] before accessing the individual text controls:

=[NameOfTheSubFormControl].[Form]![TextNbRepas]

And 3- you can use the various events to update your own text boxes.

 

[gmenon100 via AccessMonster.com]

Firstly let me thank you for your response and sorry for the delay in
replying as i was out of town.

I have copied the code i used to update my main form, but I am unable to go
to the last record I updated after the refresh, actually when I apply this
code, it refresh the 1st parent, then the 2nd parent and finally the mainform
and takes me to the 1st field of the main form instead of the sub, sub
subform. May be I am not putting things in right order:

Private Sub Form_AfterUpdate()
DoCmd.RunCommand acCmdSaveRecord

Dim lngdetailid As Long ' Requeries my sub, sub,
subform
lngdetailid = Me.detailid
Me.Requery
With Me.RecordsetClone
.Find "detailid=" & lngdetailid
If Not .EOF Then
Me.Bookmark = .Bookmark
End If
Dim lngorderid As Long ' Requeries my sub, subform
lngorderid = Me.Parent.OrderID
Me.Parent.Requery
Me.Parent!Text179 = Me.Parent!Text137
Me.Parent!EntDuty = Me.Parent!Dutytots
Me.Parent!Locshp = Me.Parent.Parent.Parent!ShpTotLoc
Me.Parent!intshp = Me.Parent.Parent.Parent!ShpTotInt
With Me.Parent.RecordsetClone
.Find "orderid=" & lngorderid
If Not .EOF Then
Me.Parent.Bookmark = .Bookmark
End If
Dim lngEntryid As Long '
Requeries my mainform
lngEntryid = Me.Parent.Parent.EntryID
Me.Parent.Parent.Requery
With Me.Parent.Parent.RecordsetClone
.Find "Entryid=" & lngEntryid
If Not .EOF Then
Me.Parent.Parent.Bookmark = .Bookmark
End If

End With
End With
End With

End Sub


This routine takes me on the mainform's 1s field instead of the field
detailid. What I am looking to do as explained is make entry on the sub sub
subform, which after update will update values on the sub, subform and also
on the mainform. Each of the subforms is bound to a table and I want the
Parent to hold grandtotals, the subform to hold subtotals and the subsubform
to hold subsubtotals. as each of these totals are required to perform
calculations on the other forms.

Three choices:

1- You perform the calculation in the source (sql statement, stored
procedure, etc.) of each form/subform and display them in a bound control.

2- You add text boxes and use the aggregate functions such as Sum and Count
as the Control Source:

= Count ([IdInvoice])

Notice that you cannot write things like Count(*) or with more complex
expressions inside the parenthesis (like Count(iif([IdInvoice]>0,1,0) ) but
you can write expressions like:

=Sum([Repas1]) + Sum([Repas2]).

=2*[TextNbRepas]

=2*[Form]![TextNbRepas]

=2*[Forms]![f_Choix]![TextNbRepas]

Notice that you cannot use [Me] in replacement of [Form] or [Report]. For
the subforms, you will have to write the name of the control containing the
subform followed by [Form] before accessing the individual text controls:

=[NameOfTheSubFormControl].[Form]![TextNbRepas]

And 3- you can use the various events to update your own text boxes.

Dear Friends,

[quoted text clipped - 31 lines]

Appreciate any response.
Gmenon

 

[Sylvain Lafontaine]

Huh? I am supposed to understand this?

Like I said earlier, there are many ways to compute sums on either the
client or the server side. You should make some tests to find which one is
better suited to you.

Also, I seem to have noticed some bugs with the use of Me.RecordsetClone and
ADODB recordsets since the latest service packs. I now prefer to use
Me.Recordset.Clone instead and set the returned recordset in a variable:

Dim rs as ADODB.Recordset
Set rs = Me.Recordset.Clone

For DAO recordsets, I don't know but usually, all recordsets are of type
ADODB in an ADP project.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Firstly let me thank you for your response and sorry for the delay in
replying as i was out of town.

I have copied the code i used to update my main form, but I am unable to
go
to the last record I updated after the refresh, actually when I apply this
code, it refresh the 1st parent, then the 2nd parent and finally the
mainform
and takes me to the 1st field of the main form instead of the sub, sub
subform. May be I am not putting things in right order:

Private Sub Form_AfterUpdate()
DoCmd.RunCommand acCmdSaveRecord

Dim lngdetailid As Long ' Requeries my sub, sub,
subform
lngdetailid = Me.detailid
Me.Requery
With Me.RecordsetClone
.Find "detailid=" & lngdetailid
If Not .EOF Then
Me.Bookmark = .Bookmark
End If
Dim lngorderid As Long ' Requeries my sub, subform
lngorderid = Me.Parent.OrderID
Me.Parent.Requery
Me.Parent!Text179 = Me.Parent!Text137
Me.Parent!EntDuty = Me.Parent!Dutytots
Me.Parent!Locshp = Me.Parent.Parent.Parent!ShpTotLoc
Me.Parent!intshp = Me.Parent.Parent.Parent!ShpTotInt
With Me.Parent.RecordsetClone
.Find "orderid=" & lngorderid
If Not .EOF Then
Me.Parent.Bookmark = .Bookmark
End If
Dim lngEntryid As Long '
Requeries my mainform
lngEntryid = Me.Parent.Parent.EntryID
Me.Parent.Parent.Requery
With Me.Parent.Parent.RecordsetClone
.Find "Entryid=" & lngEntryid
If Not .EOF Then
Me.Parent.Parent.Bookmark = .Bookmark
End If

End With
End With
End With

End Sub


This routine takes me on the mainform's 1s field instead of the field
detailid. What I am looking to do as explained is make entry on the sub
sub
subform, which after update will update values on the sub, subform and
also
on the mainform. Each of the subforms is bound to a table and I want the
Parent to hold grandtotals, the subform to hold subtotals and the
subsubform
to hold subsubtotals. as each of these totals are required to perform
calculations on the other forms.

Three choices:

1- You perform the calculation in the source (sql statement, stored
procedure, etc.) of each form/subform and display them in a bound control.

2- You add text boxes and use the aggregate functions such as Sum and
Count
as the Control Source:

= Count ([IdInvoice])

Notice that you cannot write things like Count(*) or with more complex
expressions inside the parenthesis (like Count(iif([IdInvoice]>0,1,0) )
but
you can write expressions like:

=Sum([Repas1]) + Sum([Repas2]).

=2*[TextNbRepas]

=2*[Form]![TextNbRepas]

=2*[Forms]![f_Choix]![TextNbRepas]

Notice that you cannot use [Me] in replacement of [Form] or [Report]. For
the subforms, you will have to write the name of the control containing
the
subform followed by [Form] before accessing the individual text controls:

=[NameOfTheSubFormControl].[Form]![TextNbRepas]

And 3- you can use the various events to update your own text boxes.

Dear Friends,

[quoted text clipped - 31 lines]

Appreciate any response.
Gmenon