I have a Flavor Table with multiple FlavorDetails. My goal is to "Revise" the Flavor on the fly: A revise button would open a popup with the duplicated Flavor and it's duplicated FlavorDetails ready to be editted. A Save and Cancel button would either create or cancel the insertion of the new records. The part I haven't been able to wrap my head around would be the display of the related Detail records in a subform of the popup without the new Flavor record getting saved first. Should I approach this by saving the Flavor record first and having the cancel button delete the record or is there a better method? I have also considered assigning the FlavorID in the FlavorDetails record a number like 9999999 to display them in the subform and then reassigning those numbers to the newly created primary key of the Flavor after it is created upon the "Save" click. Please help! :D
1 Answers
I have had success using SELECT...INTO statements to create temporary tables to store your revisions, and then using VBA to validate revisions and UPDATE the main table.
For your application, the "Revise" button would capture the flavor to be edited from your form and create a temporary table. The pop up form's data source would be bound to the temporary table. Any revisions made in the form will automatically update the temporary table values.
Your "Save" command button would take the temp table data and update the permanent tables as necessary (have the ID be generated as normal if you append instead of update). You may want to delete the temporary table after the update to keep things tidy. The "Cancel" command button would just delete the temporary table and close the pop up form, keeping the original data in the main table intact and unedited.
