Return the maximum entry from a subfrm

[kysterathome]

Hi all,

I have a question regarding filtering.

The setup:
Main form: frmData contains subfrmTransactions (continuous form based on QuerySubfrmTransactions).

I would like to have a field in frmData (txtMaximum) that returns the highest record entered in the fields txtOrderAmount in subfrmTransactions.

Do I have to add a new field in the QuerySubfrmTransactions and specify the criteria "Maximum" ? How would I go about that?
I tried many combinations (for instance, I entered Maximum:max[txtOrderAmount], but it does not appear to be equal to Excel...

I am grateful for an indications that will put me on the right path.

Kysterathome

 

[willir]

Kysterathome

No you do not need to add a control to the subform. You do need to now the RecordSource of the subform.

There are several ways to approach this. My first thought would be to create a function -- can be used as a ControlSource for an unbound text field, rather than a long-winded ControlSource.

You need to pass the foreign key used within the subform (to link the main form), and any filters applied to the subform.

Using your OrderAmount as an example...
Assumptions:
- OrderID - foreign key for subform, primary key on main form, longer interger
- ProdType - you order products of different types, some times you review an order for specific types, text
- ProdPrice - price of ordered product
- tblOrderDetail - table used to capture order details

Function FindMaxOrder(lngOrderID As Long, _
& Optional strProdType As String) As Currency

Dim strWhere As String, strQ As String

strQ = Chr$(34)

If Nz(lngOrderID, 0) Then
   If Len(Nz(strProdType, "")) Then
       strWhere = "OrderID = " & lngOrderID _
       & " and ProdType = " & strQ & strProdType & strQ
   Else
       strWhere = "OrderID = " & lngOrderID
   End If

   FindMaxOrder = Nz(DMax("ProdPrice", "tblOrderDetail", _
   & strWhere), 0)

Else
   FindMaxOrder = 0
End If

End Function

This is a more complex example since you choose to pass or not pass the optional variable ProdType. But using a complex condition demonstrates how the function can accommodate different conditions.

On the main form, you would create an unbound text box. The ControlSource would be...
=FindMaxOrder(OrderID)
... where OrderID is the name of a control field on the form.

If you applied a filter, such as product type in my example, in addition to changing the record source for the subform, you would change the control source for the unbound text box to...
=FindMaxOrder(OrderID, cmbProdType)
... where cmbProdType would be a combo box used to filter the product type.

Richard

 

[kysterathome]

Willir,

phew... a lot of characters in your code

I tried to reconstruct your point, and currently understand what has to be put in the control source, so that the code is read... for the rest I am not sure.

Indeed, I forgot to mention one thing: I need to indicate the max amount, but of course also the respective category (Investor, in my case). The setup of the subfrm (I assume that the parent form is not relevant, other than that the parent and child is linked via ID) is as follows:

Context of subform: made to enter amounts specific investors have bought of a security (the security is specified in the parent form, and the individual transactions are in the subform, subfrmTransactions):

In the subform, I am entering InvestorName (InvestorNameID linked to another table tblInvestorDetails) and OrderAmount.

Now, in the parent form I would want to have a txt field that indicates which Investor (InvestorName in subfrm) bought how much, or rather the most(OrderAmount in Subfrm).

Later, I also want the overall average of all OrderAmounts (but I guess this is just to replace Max with Average, or something in that direction...

Would your code still apply to my situation?