P
Paulo
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
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