If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional Totals in Subform
I have a main form with several combo boxes, and a subform. Based on the
selection made in the combo boxes, the corresponding records are shown in the subform. There are also totals (and calculations as well, like Revenue-Costs=Margins) in the subform footer, using Text Boxes. These totals are based only on the records shown, so they change with each new combination of selections in the combo boxes in the main form. Everything is fine so far. But what if I want to show a conditional total in the subform footer, that is, sum only those records (from the ones currently shown) that meet a specific criteria? I tried to do this using the Dsum function in a text field, but I am struggling to find the right arguments for the function. The first argument is not difficult, it is just the numeric field(s) that you want in the total. But how can I define correctly the second (domain) and third (criteria) argument, if I am referring to only the current set of records (filtered by the combo boxes) shown in the subform (and not the whole query on which the subform is based). For example, I might add the following code to each of the combo boxes: Private Sub Combo_Channel_Lostfocus() With Me.[Sub Bids Tracking].Form ‘the name of the subform .TextBox123 = DSum("[Quantity]*[Unit Price]", Second Argument, Third Argument) End With End Sub Can you make any suggestions? Is my explanation of the problem clear enough? Thanks in advance, Paulo |
Thread Tools | |
Display Modes | |
|
|