Pass subform total to main form

[BruceM]

I have an Access 2003 database for purchase orders. It includes a main
table for POs, and a related table for line items. It is a fairly standard
set-up. It is in the context of a larger project including vendors. There
are other tables including vendors and products, but they are not relevant
to the question at hand.
The line items are on a continuous subform (bound to the line items table)
on the main Purchase Order form. In the subform footer is a calculated text
box: =Sum([Quantity] * [UnitPrice])
Quantity and Unit Price are fields in the line items table (tblPO_Items).
The calculated control works properly.
The database will employ User-Level security with user-specific logins.
Authorized users can click buttons to approve the Purchase Order on various
levels (administrative, quality, etc.). Clicking the button inserts the
CurrentUser into a field. This is done in the main PO record, and therefore
on the main PO form.
When the Purchase Order total is above a certain amount an additional level
of approval is needed. In that case I want another text box, hidden in most
cases, to be visible on the form (for the CurrentUser who approves on the
new level). I came up with a function that works in the Purchase Order
form's code module:

Public Function Over5K()

Dim lngQty As Long
Dim curUnit As Currency
lngQty = Nz(DSum("Quantity", "tblPO_Items", "PO_ID = " & Me.PO_ID), 1)
curUnit = Nz(DSum("UnitPrice", "tblPO_Items", "PO_ID = " & Me.PO_ID), 1)

Select Case lngQty * curUnit
Case Is < 5000
Me.txtApproval.Visible = False
Case Else
Me.txtApproval.Visible = True
End Select

End Function

I call the function from the form's Current event if it is not a new record.
The Nz for lngQty and curUnit is because if a user starts a PO but does not
select a line item, there is an invalid use of null error.

I find myself thinking there is a better way than DSum to accomplish what I
need. However, I can't reference the calculated control directly, and Sum
doesn't work.

I would like to call the function (or otherwise show/hide the txtApproval
text box) from the subform after each new record is added. When the total
exceeds 5000, show txtApproval; otherwise keep it hidden. However, although
I tried placing the function in a Standard Module, I can't figure out how to
reference the controls. That is, I can't use Me.txtApproval, so I will need
to Dim frm as Form and use that instead of Me, or something like that, but I
can't seem to get my brain around how to do that.

My whole approach may well be flawed, so please don't think I am necessarily
looking for a way to salvage the current code.

 

[Steve McLeod]

In a subform you can reference parent controls and properties with
Parent.propertyname or Parent.controlname. In the parent form the reference
to the subform is Me.subformcontrolname.Form.controlname or propertyname.

If this are two separate forms then the problem gets more complicated. I
just answered that in a previous post, "Forms - Call Data from Another Table."

--
Pictou

I have an Access 2003 database for purchase orders. It includes a main
table for POs, and a related table for line items. It is a fairly standard
set-up. It is in the context of a larger project including vendors. There
are other tables including vendors and products, but they are not relevant
to the question at hand.
The line items are on a continuous subform (bound to the line items table)
on the main Purchase Order form. In the subform footer is a calculated text
box: =Sum([Quantity] * [UnitPrice])
Quantity and Unit Price are fields in the line items table (tblPO_Items).
The calculated control works properly.
The database will employ User-Level security with user-specific logins.
Authorized users can click buttons to approve the Purchase Order on various
levels (administrative, quality, etc.). Clicking the button inserts the
CurrentUser into a field. This is done in the main PO record, and therefore
on the main PO form.
When the Purchase Order total is above a certain amount an additional level
of approval is needed. In that case I want another text box, hidden in most
cases, to be visible on the form (for the CurrentUser who approves on the
new level). I came up with a function that works in the Purchase Order
form's code module:

Public Function Over5K()

Dim lngQty As Long
Dim curUnit As Currency
lngQty = Nz(DSum("Quantity", "tblPO_Items", "PO_ID = " & Me.PO_ID), 1)
curUnit = Nz(DSum("UnitPrice", "tblPO_Items", "PO_ID = " & Me.PO_ID), 1)

Select Case lngQty * curUnit
Case Is < 5000
Me.txtApproval.Visible = False
Case Else
Me.txtApproval.Visible = True
End Select

End Function

I call the function from the form's Current event if it is not a new record.
The Nz for lngQty and curUnit is because if a user starts a PO but does not
select a line item, there is an invalid use of null error.

I find myself thinking there is a better way than DSum to accomplish what I
need. However, I can't reference the calculated control directly, and Sum
doesn't work.

I would like to call the function (or otherwise show/hide the txtApproval
text box) from the subform after each new record is added. When the total
exceeds 5000, show txtApproval; otherwise keep it hidden. However, although
I tried placing the function in a Standard Module, I can't figure out how to
reference the controls. That is, I can't use Me.txtApproval, so I will need
to Dim frm as Form and use that instead of Me, or something like that, but I
can't seem to get my brain around how to do that.

My whole approach may well be flawed, so please don't think I am necessarily
looking for a way to salvage the current code.

 

[BruceM]

I guess I didn't explain myself well enough. The problem is not referencing
the control, but rather how to perform the "subform total" math I
demonstrated with my use of DSum. One of my questions was whether there is
a better way than DSum of accomplishing that. However, upon reflection DSum
will not work as intended anyhow, because the total Quantity * the total
UnitPrice is not the same mathematically as the sum of Quantity * UnitPrice
for each record.
It was late yesterday when I tackled this, and my brain must have gotten
stuck, because today I came in and saw that I could use the following in a
public function in a standard module:

Select Case Forms!frmPO!fsubPO_Items.Form!txtTotal
Case Is < 5000
Forms!frmPO!txtApproval.Visible = False
Case Else
Forms!frmPO!txtApproval.Visible = True
End Select

txtTotal is the text box in the subform footer that contains the calculation
=Sum([Quantity]*[UnitPrice]). I can call the function from either the main
form's Current event or the subform's After Update event. However, there is
a problem when calling it from the subform's After Update event, which is
that the text box txtApproval does not hide/unhide right away. However,
when I set a break point at Select Case and step through the code it does
hide/unhide txtApproval. I tried adding Refresh and Repaint to the code,
but still txtApproval's Visible property does not change based on the value
in txtTotal on the subform unless I step through the code. I think what is
going on is that when stepping through the code I am proceeding through the
code slowly enough to give txtTotal a chance to perform the calculation, but
when running the code from the After Update event there is no such break in
the action.

So I guess the question is how to determine:
Sum([Quantity]*[UnitPrice]
before the form's update is complete.

In a subform you can reference parent controls and properties with
Parent.propertyname or Parent.controlname. In the parent form the
reference
to the subform is Me.subformcontrolname.Form.controlname or propertyname.

If this are two separate forms then the problem gets more complicated. I
just answered that in a previous post, "Forms - Call Data from Another
Table."

--
Pictou

I have an Access 2003 database for purchase orders. It includes a main
table for POs, and a related table for line items. It is a fairly
standard
set-up. It is in the context of a larger project including vendors.
There
are other tables including vendors and products, but they are not
relevant
to the question at hand.
The line items are on a continuous subform (bound to the line items
table)
on the main Purchase Order form. In the subform footer is a calculated
text
box: =Sum([Quantity] * [UnitPrice])
Quantity and Unit Price are fields in the line items table (tblPO_Items).
The calculated control works properly.
The database will employ User-Level security with user-specific logins.
Authorized users can click buttons to approve the Purchase Order on
various
levels (administrative, quality, etc.). Clicking the button inserts the
CurrentUser into a field. This is done in the main PO record, and
therefore
on the main PO form.
When the Purchase Order total is above a certain amount an additional
level
of approval is needed. In that case I want another text box, hidden in
most
cases, to be visible on the form (for the CurrentUser who approves on the
new level). I came up with a function that works in the Purchase Order
form's code module:

Public Function Over5K()

Dim lngQty As Long
Dim curUnit As Currency
lngQty = Nz(DSum("Quantity", "tblPO_Items", "PO_ID = " & Me.PO_ID), 1)
curUnit = Nz(DSum("UnitPrice", "tblPO_Items", "PO_ID = " & Me.PO_ID),
1)

Select Case lngQty * curUnit
Case Is < 5000
Me.txtApproval.Visible = False
Case Else
Me.txtApproval.Visible = True
End Select

End Function

I call the function from the form's Current event if it is not a new
record.
The Nz for lngQty and curUnit is because if a user starts a PO but does
not
select a line item, there is an invalid use of null error.

I find myself thinking there is a better way than DSum to accomplish what
I
need. However, I can't reference the calculated control directly, and
Sum
doesn't work.

I would like to call the function (or otherwise show/hide the txtApproval
text box) from the subform after each new record is added. When the
total
exceeds 5000, show txtApproval; otherwise keep it hidden. However,
although
I tried placing the function in a Standard Module, I can't figure out how
to
reference the controls. That is, I can't use Me.txtApproval, so I will
need
to Dim frm as Form and use that instead of Me, or something like that,
but I
can't seem to get my brain around how to do that.

My whole approach may well be flawed, so please don't think I am
necessarily
looking for a way to salvage the current code.