Design Advice Needed: Assigning IDs and Avoiding Duplication of Info & Keystrokes

[CWC]

I am a self-taught beginner, so I thank you in advance for your patience!



I am designing a database for a symbolic "adopt an animal" program and have hit a snag.

I have three tables:
Animal Table with an AnimalID, names, photo

Personal Info Table with a ParentID, name, contact info

Adoption Table with the AdoptionID (PK), animal adopted, date adopted, date shipped, Adoptor ID (ParentID of the Adoptor), Recipient ID (ParentID of the Recipient)

My problem is with the ParentID. I need to input information for both the Adoptor and the Recipient, but I want each individual to have an assigned ID (ParentID) that will be reused for all future adoptions. For example, today John Smith adopts an animal, so he is the Adoptor. Then tomorrow, Sally Sample adopts an animal as a gift for John Smith. Now John Smith is the Recipient.
I want to be able to run a query for John Smith's ParentID to see his complete Adoptor/Recipient adoption history.

If I had two separate tables, Adoptor and Recipient, John Smith would be in both tables with two different IDs. So I made one Personal Info table to store all personal data with one ParentID for each person. I had the ParentID as an autonumber. In my Adoptor and Recipient subforms, I used a combo box to autofill the individual's data based on the last name, from a query of the fields in the Personal Info Table, which worked for every field except the ParentID, because it's an autonumber.

I need the database to autofill the individual's data, including ParentID, if they've already been entered into the database as either an adoptor or recipient, without adding a new ID every time. If they are not yet in the database, I need to be able to enter new data with a new ParentID auto-generated.

Is this even possible? Do I need to change the whole structure of my database, or is there a smaller tweak to be made?

Thanks for taking the time to help me!

[June7]

Why do you have separate subforms? Won't each adoption transaction be a single record?

If you want to be able to add parents 'on the fly' during data entry, explore the combobox NotInList event.

Can build a query that includes the PersonalInfo table twice, one each joined to the AdopterID and RecipientID fields. Then apply same filter criteria under both fields with OR operator - each criteria on separate rows. This should return all records for any given ParentID regardless of their role.

Actually, a normalized structure would be like:

tblAdoption
ID
AnimalID
AdoptionDate
ShipDate

tblAdoptionDetails
AdoptionID
AdoptionRole (adopter, recipient)
ParentID

This could mean multiple records for each adoption transaction - allowing for joint adoptions. Your present structure allows only one person for each role.

BTW, might want to check on spelling of adopter/adoptor,

[CWC]

Thanks for your help.

I thought I needed two subforms because each adoption will have an adopter and a recipient, but the information for both individuals had to go into the same Personal Info Table.

I've modified the tables as you suggested and removed the AdopterID and RecipientID due to the addition of the Adoption Details table. This makes more sense. Thank you.

What I'm still not seeing is how to enter personal details with the autonumber ParentID, but have the form first look to see if the person is already in the Personal Info Table. My form will fill in the information for a previously entered individual based on a query of the Personal Info Table, but the snag is the ID. I can't figure out how to have it import the ID if it already exists, and autogenerate one if it's a new person.

I looked at the combobox NotInList event, but won't I still hit the same autonumber snag?

Thank you!

[June7]

I don't understand the issue.

Use form/subform arrangement. Main form bound to tblAdoption (with a combobox for AnimalID) and subform bound to tblAdoptionDetails (with a combobox for PersonID).

Select existing item from combobox list or by typing. If desired item not in list then need to add to the source table. This can be accomplished 'on the fly' during data entry with code behind the NotInList event. The autonumber ID will generate with data entry in table.

[CWC]

I'm back to working on this database, and I'm still encountering a problem with the ParentID being an autonumber. When I fill in the subform based on the MemberID (which will just be a unique number from a different donor database), everything works except the ParentID, because it's an autonumber. I tried to use a combo box for the ParentID as suggested, but I couldn't get it to work. I'm also not sure where to apply the NotInList event.

I need to input the member number, and have all the other fields autofill if the person is already in the Personal Info table. If they are not, I need to enter their information into the form, including an autonumber ParentID.

Here is a Dropbox link to my database.

Thanks for your help on this!