Creating a New Record using a complicated script
I have 3 tables in this example that I am seeking help with. They are as follows; CM (client mgmt), AM (asset mgmt) and AH (asset history). All 3 tables are related to each other. CM relates to AM by the clients first and last name. AM is related to AH by an ID#. CM is related to AH by first and last name.
If I have a client named Jane Doe and she was issued a laptop (L-039) on 5-5-05, returned the laptop on 5-5-06. I want the AH table to reflect a record showing that Jane was assigned L-039 laptop during the time mentioned by creating a record in that table noted by an automatic AH record number.
At the same time I want the laptop (L-039) to stay in the AM table, losing only the client's name assigned to it (Jane Doe) so that I may be able to re-issue it out to John or Paul Doe.
So. . .with that being said here are the particulars; I have a CM layout that has a portal from the AM table that displays both (::issued date//::returned date) fields on a row along with other important fields such as, (::ID, ::Type, and ::name) for the assets assigned to the Jane.
I want to be able to enter a (::returned date) in the return date field within the AM portal on the CM layout, thus causing a chain reaction of:
1. creating a new AH record in the AH table
2. copying all the key fields associated with that portal row and pasting that information into matching fields on the AH table
3. erasing the clients name from the asset record on the AM table leaving Laptop (L-039) in the table but without Jane's name associated.
4. and returning to the original CM Layout after being prompted with a note asking "Are you sure you want to make these changes at this time?"
5. "Yes or No" Yes will take you back to the CM Layout with all the changes made and No will return you back to the CM layout with no changes made.
WOW!, I hope this is clear because I think I have just confused myself.