I'll explain it the best I can but it has been a while. I don't have the time to take it on right now.
- Create 2 tables that mirror the main and sub tables.
- Give user choice to create new or edit or review (combobox?). When choice is made append main and sub records to temp tables, pass choice to OpenArgs ("new" or "edit" or "view") to form. Or use read only instead of view.
- In form load event code set properties according to the choice. If view, lock records using one of the methods available to you. Easiest might be to set recordsource to a query using snapshot type of recordset, else change all Allow properties accordingly
https://learn.microsoft.com/en-us/of...orm.allowedits
- If new, don't append to the tables. Fill out the form as usual.
- If edit, append temp records based on which
main record user indicated they want to edit (same as for view).
- In form close event, a variable would be set by your "Save & Close" button. That button should not even be enabled if the form is not dirty. Only provide for Close. If they click close you can prompt to save changes if desired and applicable (i.e. not for view only) and form is dirty. Variable contains your clue to save changes or not:
- if not (because of discarding changes or read only), delete records from temp tables, close form. Done.
- if yes and new, append records to main tables, delete temp records, close form
- if yes and edit, run update sql on main tables using temp field values, delete temp records
, close form. Done
You could even do this for forms separately i.e allow main but not sub record edits or vice versa by referring to main form open args
Cautions -
- I never had an error in updating or appending. Might want to look into transactions though, just in case.
- In a multi user environment should work ok if temp tables are in front end. I wouldn't worry about FE bloat but one could enforce a CR if db reaches a certain size on closing.
- if multi user and db is not split and you just won't do that, you need to id records based on user info, otherwise you delete everyone's records.