Creating a new record in a table related to 2nd table via 3rd tabl

Creating a new record in a table related to 2nd table via 3rd tabl

Post by RGFubn » Fri, 31 Dec 2004 05:09:05


I have three tables:

tPayments: PK is PaymentID, which is autoincrement
tDonations: PK is DonationID, which is autoincrement
tDonationsToPayments: PK is PaymentID + DonationID

A donation record will have one or more related payment records
A payment record will have zero or one related donation record

I have a main form based on tDonations, with a subform based on a query
pulling from tDonationsToPayments and tPayments

The donation record is created first, then the payment record(s). But in
order for the tDonationsToPayments table to be populated, both the DonationID
and the PaymentID have to already exist. The DonationID does already exist,
but the PaymentID does not, since the payment record hasn't been created yet.

Do I need to change the design of the query that underlies the subform? Do I
need to change the design of the main form? Do I need to run some VBA
procedure that first creates the payment record, then fills in the
tDonationsToPayments table?

Thanks for your help.

Danny
 
 
 

Creating a new record in a table related to 2nd table via 3rd tabl

Post by S2V2aW » Fri, 31 Dec 2004 19:51:01

Danny,

What if you changed the design of your tDonations table and added a foreign
key (PaymentID). This would be added when payment is made. If there are no
payment records nothing would be displayed in your subform, but when they
appear, records would populate the subform as you desire. I am not sure what
purpose the tDonationsToPayments table serves other than to tie these records
together and modifying the database design as described would achieve that
for you.

Hope that helps!

Kevin

 
 
 

Creating a new record in a table related to 2nd table via 3rd tabl

Post by Duane Hook » Sat, 01 Jan 2005 01:26:32

If you can have a payment without a related donation record then I would no
have a compound pk in tDonationsToPayments that includes the DonationID. It
seems to me the donation is optional and should not necessarily be related
to tDonations.

--
Duane Hookom
MS Access MVP