Example:
I have a database that tracks personnel for a boys and girls club. When the personnel manager wants to delete a member I have set it up to archive instead of deleting the record. #1 so that they may restore a member & #2 so that the historical data can be reported on.
Below are the tables and replica tables that the SQL appends from them deletes from.
Expand|Select|Wrap|Line Numbers
- tbl_Members/tbl_A_Members
- tbl_Contacts/tbl_A_Contacts
- tbl_Medical/tbl_A_Medical
- tbl_ReportCards/tbl_A_ReportCards
- tbl_ProgramAttendance/tbl_A_ProgramAttendance
- tbl_EventAttendance/tbl_A_EventAttendance
The process to "move" a member is simply an append statement and delete statement for each table to a replicated table that serves as the archive, obviously the member being last because of the referential integrity.
So my VBA ends being lengthy because there is a String, SQL statement, and DoCmd.RunSQL for each table. (twice, once for append and once for deletion).
Example (sudo code)
Expand|Select|Wrap|Line Numbers
- Dim SQL1 AS String
- Dim SQL2 AS String
- strSQL1 = "SELECT.."
- strSQL2 = "DELETE.."
- DoCmd.RunSQL strSQL1
- DoCmd.RunSQL strSQL2
Is their any way to simplfy/loop this to save face? this is my first command and I will have to do this another 15 times (7 more entities to move, and 8 restore features)
This method works PERFECT, just the code is long...
Any help or guidance would be much obliged.