Hi Tim
This can be done but you will need to do it with a combination of queries and forms in your autoexec macro.
I managed to get my autoexec macro to take 2 differing paths depending on whether or not it found any differences between the total pledge and the annual pledges. This is how I did it :
I created a query that calculated the differences between the total pledge and the annual amounts, by donor. This query sums the annual amounts and then you can compare the total of the annual amounts to the initial pledge (per donor) and then filter out any differences that are <>0 with the following :
1st Query :
New Query, Add the 2 Tables (with the initial pledge and annual amounts), View Totals
Field 1 = Donor ID (or whatever variable name you have used, per first table)
Field 2 = Total Pledge (per first table)
Field 3 = Amt: Sum(IIf(IsNull([Amount]),0,[Amount])) - where "amt" is the amount allocated to each year per your 2nd table - don't forget to change the "Total" field to "Expression" and to use your variable name
Field 4 = Diff: [Pledge]-Sum(IIf(IsNull([Amount]),0,[Amount])) - set the criteria to <> 0 and change the "Total" to "Expression".
Save
This query will give you the donors where the total pledge <> the sum of the annual amounts. However, you can't use this successfully with a form and macro (I tried and failed) so you need a 2nd query, as follows :
2nd Query :
Create a 2nd query, add the first query to the 2nd query, view totals.
Field 1 = Donor, change "Total" to "Count".
Save
This 2nd query will count the number of donors where the initial pledge <> the sum of the annual amounts.
Create a new form, based on the 2nd query - add the one and only field being "CountofDonor".
Save
{There may be a quicker way of doing this - I'll leave that for the experts}
In your autoexec macro :
View Conditions
Action 1 : no condition, open the form (I used F_Count]
Action 2 : Condition = [Forms]![F_Count]![CountofDonor]=0, Action = Close the form
Action 3 : Condition = [Forms]![F_Count]![CountofDonor]=0, Action = Stop Macro
Action 4 : Stop Macro.
Save and Run.
This macro will keep the form open if there are >0 donors and close it if all is ok. I realise this isn't the exact solution you were looking for but it shows that what you are seeking can be done with queries (& forms!) and macros. To add more lines to your macro under the first condition, insert some new lines between actions 2 and 3 - but remember to copy the condition for each line. There is a shortcut to avoid re-typing the entire condition (3 periods I think) but I can't remember it at the moment.
You can also set other conditions under the null response between actions 3 and 4 (e.g. close the count form, open a data entry form showing the amounts to be fixed etc.) - for this you don't need any conditions.
You can then repeat this entire exercise for the 2nd test (i.e. regional annual allocations per donor <>1 or <>100%) - however, you might want to start the 2nd test before you "stop the macro" under the first condition on the first macro. (Getting too late to explain this last paragraph fully but I'm sure you know what I mean - just ask if you're not sure and I'll check again here in 12 hours time).
HTH, Andrew.
P.S. A couple of minor typo's were edited.
