A more elegant solution probably exists with VBA, but I will show you one way to do it with macros:
First create a "Named Macro" in your table that tracks Donors, and we'll call it UpdateParticipation. Then create two parameters for the macro - first one is prmAction and that will determine if you inserted or deleted an item from your donation table. Second parameter is prmDonorID and that will identify which record in the table to update the status for.
The named macro will check the participation box if a donation is added, and uncheck the participation box if a donation is deleted. The macro will look like this:
If [prmAction]="Inserted" Then
Look Up A Record In DonorsTable
Where Condition = [DonorsTable].[ID]=[prmDonorID]
Alias
EditRecord
Alias
SetField
Name DonorsTable.Participated
Value = True
End EditRecord
Else If [prmAction]="Deleted" Then
Look Up A Record In DonorsTable
Where Condition = [DonorsTable].[ID]=[prmDonorID]
Alias
EditRecord
Alias
SetField
Name DonorsTable.Participated
Value = False
End EditRecord
End If
Now whenever you are in the DonationsTable and insert/delete a record, you want to trigger the named macro.
So in DonationsTable create an After Insert Event that looks like this:
RunDataMacro
Macro Name DonorsTable.UpdateParticipation
Parameters
prmAction = "Inserted"
prmDonorID = [DonationsTable].[DonorID]
When you delete a record it's a little trickier - if you are deleting the second donation from a donor, they are still participating so you don't want to uncheck the box in DonorsTable. So create an After Delete Event that looks like this:
Look Up A Record In SELECT DonationsTable.DonorID FROM DonationsTable;
Where Condition = [DonorID]=[Old].[DonorID]
Alias
StopMacro
RunDataMacro
Macro Name DonorsTable.UpdateParticipation
Parameters
prmAction = "Deleted"
prmDonorID = [DonationsTable].[DonorID]
That will have you covered if you insert or delete a donation. You will want to create a similar setup if an existing record in DonationsTable is edited, where the DonorID is changed from one to another.
