I need Help: Auto set yes/no to yes on entry of data in a form.

[JJordan]

First all the background to get to the Questions:

I am working on a database for a charity that I am on the Board of, Three Trees, to track families that participate in our programs and donors that provide us with support. I have a Main Table (IndContacts) where I store all contact information for everyone (Names, address, phone, e-mail, etc.) In the same table, I have two Yes/No Fields: Donor and Family. We store a lot more infomraion about families (childrens names, etc.) than we do about donors so I have another table for family information and I have a table called "IndDonations" where I record all donations for people whose names appear in the IndContacts table. The IndDonationas table is linked to the IndContacts table by the primary key (auto-generated number) in the IndContacts table. The IndDonations table also has its own Primary Key which is basically used to track Receipts (and called "Receipt_Number").

I use a form to enter donations which pulls down a combo box with last name first name for everyone in the IndContacts database and therefore only lets you enter donations for people that are already in the IndContacts table. You enter a donation_date, Donation_amount, Donation_type (all from the IndDonation Table).

What I want is to know how to write a command to cause the Donor box in the IndContacts table to be True (checked) whenever a donation is entered for someone. So for example, when I type in a donation amount, it automativcally sets the Donor box to yes in the other table. Everything I try to do gives me error messages and does not check the box Yes (like this message: "The current filed must match the join key '?' in the table that serves as the 'one' side of the one to many relationship. Enter a record with the desired key value and then make the entery with the desired join key in the many-only table." ) The two tables are joined in a One to Many join (one contact to many donations).

My problem is I am very new at this. maybe I am trying to do things wrong in my design, but I will need to do something similar to check a yes/no box as Yes whenever I run a report for "IndDonations" to generate receipts -- the yes/no for the "Receipt_Generated" filed will need to set to Yes whenver the report is run (for all that are in the report) so that we know receipts have bene generated.

 

[AlanS]

Probably the best way to do this would be to have your main form (not just the combo box) bound to the contacts table, and a subform (perhaps in continuous form view) bound to the donations table. When the LinkChild and LinkMaster properties of the subform control are properly set, as you move from one contact record to another in the main form, the subform will display only those donation records that are related to that contact. You can use the Wizard to set up the combo box, specifying that when you select an item from the combo box, you want Access to locate a record in the table (in the main form).

In the subform, create an AfterInsert event procedure, and in that procedure put a line of code like this:

Me.Parent!Donor = True

which instructs Access to mark the Donor field as True in the current record in the main (parent) form, after you insert a new record in the subform.

For the receipt checkbox, you'll probably need to generate a temporary table containing the records for which a receipt is to be printed. The receipts report will run from that table. You'll also need an update query, based on the same temporary table, which will mark the contacts table receipt checkbox as True for all contacts who have records in the temporary table. When it's time to do receipts, first run the receipts report. Then, when you're satisfied they've printed properly, run the update query.

 

[vangogh228]

AlanS: Wouldn't that allow an entry of 0 to check the box? I think the checkbox could actually be a calculated formula, couldn't it?

IIF(FieldA>0,"Yes", "No")

Would that work? Any querying for reports or mailmerge could just look at the FieldA with criteria of >0. In fact, there really wouldn't be a need for a checkbox at all in the table if it is always dependent on the donations field and you could always query on the amount being >0... (right ?)

 

[AlanS]

If there could be a 0 amount transaction, then yes, it would show as a donor if for a 0 amount. If you eliminate the field and query the transaction table instead, that will work but could create problems down the road if (a) the transaction table gets so big that the query slows down unacceptably, or (b) the transaction table needs to be purge of old records and a donor has no current transactions

 

[Rich]

Why do you need a check box at all? Just look in your donations table to see who has donated. Use a query to return those who have/ have not made donations

 

[JJordan]

I want to do it this way so that I can search/sort my IndContacts table for just donors to create mailers. When I use the IndDonations table to create a donor list, I get multible listings for the same donor (and therefore multiple mailing lablels, copies of the report, etc.) I am sure there is an easier way becuase this is too hard. I will keep reading the book I bought (Access 2002 Bible) but what it does not tell you much about is how to write SQL commands.

I tried the subform method and am close to getting it to work, but I think I need the IndDonations table to be the Parent table and the IndContacts to be the child (at least so far that is the only way I can get the data entry to work properly i.e., the data I want to enter is the Donation information, not the contact information.) Is the command to update the child the same just substituting child for parent (i could not get it to work that way).

By the way, thanks for the help. I am learning, but it is SLOW. Next time I guess I will just BUY a database instead of volunteering to learn how to create one and save money for us.

 

[Pat Hartman]

You just needed to modify the first thing you tried (good instincts) so that you got a distinct list of donors.

To get a distinct list of Donors from the IndDonations table:

query1:
Select Distinct DonorID
From IndDonations;

Then join that query to the the IndContacts table to obtain the name and address info rather than joining the IndDonations table directly.

query2:
Select q.DonorID, t.LastName, t.FirstName, etc
From query1 as q inner join IndContacts as t on q.DonorID = t.DonorID;