Methods to undo changes on subforms

[Jeroen3131]

Hello,

I have a form with a subform and another subform inside the first subform. I know that if the user types in a value on one of the subforms, the data is autmatically committed (Autosaved) to the table which is bounded to the subform/textbox. When the user closes the main form a message prompts with "Save YES or NO?". If he selects NO I want the mainform to close without saving the records.

As far as I know there are two options:

1. Create a Unbound Form

This involves a lot of VBA programming and the use of INSERT INTO SQL statements to set values to each table.

2. Run a DELETE SQL statement

I can delete the record after it is committed. However the Primary Key ID will still be incremented. I don't know if there is a maximum value for Primary Key?


What option do you guys advise, or are there several other options?

[jforbes]

You have a pretty good handle on your situation. The max number for an AutoNumber is in the range of 2 Billion. I guess technically 4 Billion if negative numbers are included. I would run the Delete.

There might be a third option:
Create a Form with the View set to Single Form. Make it bound to the Sub Table. Then open it from your SubForm in acDialog and acFormAdd (Add only mode for inserting a record). If I remember right, you are keeping a history, so you wouldn't need to edit, but you could do that also with a separate button. You could use the MainForm and SubForm's current record to default the Fields on the Dialog Form. And use the AfterUpdate and AfterInsert to perform any linking code after the record is added/changed.

[Jeroen3131]

Thnx, the Delete method works fast and simple.
Still have 2,147,483,647 records to go :P