table tStaff with fields tStfID, tStfFulNm, tStfStrt, tStfZip, tStfPh, tStfSort
and table tEvent with fields tEvntID, tEvntDay, tEvntTm, tEvntStfLnk (this field is the link to the tStaff table), tEvntType, tEvntDscrpt
tStaff Table Records like:
1, "Bunny, Easter", "123 Egg Lane", 12345, 123-456-7891, "Y"
2, "Clause, Santa", "25 Christmas Way", 98765, "987-654-3211", "Y"
3, "Frost, Jack", "-32 Degrees Str", 00000, "777-666-55555","N"
tEvents Table Like
tEvntID, tEvntDay, tEvntTm, tEvntStfLnk tEvntType tEvntDscrpt
1 1/3/2021 10:00, 1 "Egg Collection" "Go to the chicken Coup"
2 2/9/2021 13:00, 1 "Egg Collection" "Go to the chicken Coup"
3 11/2/2021 8:00, 2 "Make Toys" "Working in the Toy Shop"
4 11/15/2021 7:00, 2 "Hot Chocolate" "Make Hot Chocolate"
5 11/30/2021 12:00 2 "Check Naughty List" "Got to observation library"
6 10/5/2021 22:00 3 "Make Frost" "Cover the plants with frost"
So... If the parent record as a "Y" in tStfSort field, then write a child record that has the next tEvntID, and where tEvntDay = Date() and tEvnTime = Now(), and tEvnType says "Auto Generated", and tEvntDscrpt = "Record added automatically by system"
So when I run the Query or VBA Code it looks at t he first record in the Staff tabel and when it finds the "Y" it then proceeds to create a child record automatically that is properly linked with a date and time stamp and the fact that it was Auto Generated etc... see below. It would then do the Same thing for Clause, Santa, but since Frost, Jack had an "N" it would skip that record and go to the next one, until it had read and processed all records
tStaff Table Records like:
1, "Bunny, Easter", "123 Egg Lane", 12345, 123-456-7891, "Y"
tEvents Table
1 1/3/2021 10:00, 1 "Egg Collection" "Go to the chicken Coup"
2 2/9/2021 13:00, 1 "Egg Collection" "Go to the chicken Coup"
7 11/18/2021 14:00 1 "Auto Generated" "Record added automatically by system"