One form from multiple tables to append one record

[DrPat]

I did not explain my dilemma clearly in my last post, so I’ll try again.

My DB tracks sponsors for a non-profit organization. Demographics, donations, member who sold sponsorships, tickets to events, etc.

I have a form that draws data from multiple tables, plus allows for new data entry and edits. I would like to update a different table with that aggregated data.

The pertinent tables, forms and fields are as follows:

tblSponsorDemographics
SponsorID

tblSponsorshipLevel
SponsorshipTypeID

tblMembers
MemberID

frmEditSponsor
SponsorID (from tblSponsorDemographics)
SponsorshipTypeID (from tblSponsorshipLevel)
MemberID (from tblMembers)
(A command button opens frmPostPayment with filter= [SponsorID] on top of frmEditSponsor)

frmPostPayment
PaymentID (autonumber, PK)
SponsorID (from loaded frmEditSponsor)
SponsorshipTypeID (from loaded frmEditSponsor)
MemberID (from loaded frmEditSponsor)
CheckNumber, CheckDate, PostDate, Amount (all as data entry text boxes)

What I want to do at this point is to save all 8 fields on frmPostPayment to tblPayments.

Problem: Only PaymentID and the data entered on frmPostPayment (CheckNumber, etc.) are being saved. I would like to have the other 3 fields from the form saved to tblPayments as well.

(Since SponsorID is the pole that the rest of the DB dances around, I could probably get away with only updating SponsorID with the entered payment data. But I’m trying to avoid future aggravation by making tblPayments a complete entity. And SponsorID isn’t being saved anyway.)

I hope that explanation was more understandable. Any suggestions? Thanks!