Deleting a new record (1 Viewer)

[namron]

Hi

I have a form to create a new client record which contains a tab control, the various tabs contain sub forms for creating related client information on other tables e.g an 'Address History'table.
The first tab contains the main basic information about the client held in the 'Clients' table.

Occasionally users want to abandon the creation of the new client for whatever reason. To accomodate this I created a button on the first tab called 'Exit without saving' which triggers an 'Undo' action, shows a confirmation msgbox and closes the form.

The problem I have is that this only works if they have not gone into any of the other tabs to enter information. As soon as they leave the 'Main' tab, the client record is saved.

Is there any way I can prevent the record from saving until the user is ready to commit what they have entered on the various tabs into the various tables?

Or are there any suggestions for dealing with this scenario?

Thanks

Norman

 

[June7]

Try BeforeUpdate event to confirm they want to proceed and if they do cannot cancel. Might be able to do something with Transactions method which is rather advanced coding I have used once. Otherwise, not really.

Maybe forms on other tabs should be subforms. Why is 'Main' tab not the 'Main' form?

 

[namron]

Hi, thanks for the reply. The 'Main' tab contains fields bound to the 'Clients' table. The other tabs contain subforms bound to related tables.

 

[June7]

So main tab does not have subform? Comments still apply.

 

[GinaWhipp]

Not really, you are going to have to delete those records first and then *Exit without Saving*. Perhaps this will help...

https://www.access-diva.com/vba17.html

 

[Pat Hartman]

In the world of relational databases, the parent record must be saved BEFORE you can create a child record and Access is a very helpful tool so it ensures that each record gets saved when you either move to a new record or to a subform because Access KNOWS that you need the PK of the parent record to use as the FK for your subform record. How else would you be able to include a foreign key in the child record?

So, that leaves you with prompting in the BeforeUpdate event of the client record to ask if the user wants to save the new record, in which case, you can cancel the save. OR, allowing the save and adding a delete button that lets the user delete the record after it has been saved. Of course, in many applications, records cannot be deleted so you have to work around this if that is your business rule.

WHY does a user get so far into the data entry before knowing if he wants to actually save the record?

One potential solution (which I don't like because it has other issues) is to always create new records in a set of temp tables. Only when the user says he is done and wants to move the data to the production table do you append the parent record using an append query followed by an append query for each set of child records and the final step is to delete the parent record in the temp table set and let cascade delete handle purging all dependent child records. If this floats your boat, we can work on developing it further so you see the issues. Personally, I ONLY use this technique when I have to import a set of data that must "balance" and be complete before being applied. And there's lots of other code and processes you need to create in order to use the method effectively including learning how to use transactions. When you are working with sets of data, it is not valid to have some records added and some not added so the transaction ensures an all or nothing application of the temp data to the permanent tables.

 

[namron]

Hi

Thanks everyone for your comments and pointing me in the right direction.

I've been able to use the BeforeUpdate event in the form to request the user's confirmation that the new record should be committed before being able to proceed to enter data in other tabs/subforms.

Norman