Form Problem

R

Roger

I have a form based on a query, I would like to enter a name in a combo box
and have it bring up how much we paid to that charity.
I have tried the iif function and the Dlookup function, but can't get them
to work.
Since we pay charities several times a year, I would like the total paid.
There is nothing in help showing how to do this.
Thanks in advance for any help
Roger
 
D

Damian S

Hi Roger,

You could simply update your query so that it includes the sum of all
donations made, grouped by the other fields you have returned.

You would simply then need to (after you select an item from your combo
box), either move to the selected record which could display the amount you
wanted, or have the amount displayed in a text box beside the combo box with
the Control Source of =me.COMBOBOX.column(2), depending on how you are
wanting your form to behave.

Hope this helps.

Damian.
 
R

Roger

Thanks Damon & fredg
I won't have time to try these tonight but will tomorrow, I will let you
know how they work.
What I', trying to do is select the name of the charity from a combo box and
have the total show up in text box. Date is not needed and individual
donations are not needed just the grand total. The query is filtered by date

fredg said:
I have a form based on a query, I would like to enter a name in a combo box
and have it bring up how much we paid to that charity.
I have tried the iif function and the Dlookup function, but can't get them
to work.
Since we pay charities several times a year, I would like the total paid.
There is nothing in help showing how to do this.
Thanks in advance for any help
Roger
Click to expand...

I'm not sure on how you wish to display the information.

I'll assume you wish to show all the records for that Charity, the
DonationDate and the DonationAmount, and total up the contribution. In
other words CharityA was paid $100 four times last year for a total of
$400 and you wish to see each contribution date and amount as well as
the total for that year.
I'll also assume you do not have more than one charity using the same
identical name.

Here is a basic method to do this.

1) Add a Text Box to the Form Header. Name it "txtYear".
2) Add a Combo Box to the Form Header.

As Combo RowSource, write something like:

Select Distinct TableName.CharityName From TableName Order by
CharityName

Code the Combo Box AfterUpdate event:

If IsNull([txtYear]) Then
MsgBox "You must enter a year first."
Me![txtYear].SetFocus
Else
Me.Filter = "[CharityName] = """ & Me![ComboName] & """ And
Year(DonationDate]) = " & Me!tctYear
Me.FilterOn = True
End If

3) Add an unbound control to the Form Footer.
Set it's Control source to:
= Sum([AmountDonated])

4) Make the form's Default View property Continuous (or you could use
Single Form).

When you enter a year and select a charity, that charity's records for
just that year will be shown. The total amount paid will be shown in
the form footer.
Click to expand...
 
Top