How to make [x] (close form) button exit form without updating table

[S400]

We have been trying to get this subform (see pic below) to exit and return to parent form WITHOUT actually updating the record into the database table when the user clicks [X] or [Cancel].
Everything we have tried has failed
We even tried executing SQL to delete the record being added, but either we got that wrong, or at least it didn't work.

Can someone please give us the code we need to make the [X] and [Cancel] buttons close the form without update (while the [Update] button still does just that).
Please be complete/clear in the answer: we are inexperienced with Access, and won't be able to piece together an answer with only isolated snippets of code if we can't work out where to put them.

NB: The form shown below has been designed to allow only an Insert/Add record into the table.
(ie. Update and delete functions have been disabled)

[Micron]

If you edit any field of a form that is bound to a recordsource (i.e. table or updatable query) Access automatically updates the record if you close the form and won't prompt unless something the user did violates data integrity. That is how it works. Your pic shows a record hasn't been saved, and that is crucial to knowing what to do.

The correct event for this often depends on the process. Possibly, you can use the form BeforeUpdate event and test if a record has been edited and prompt if it has - if the user clicks the form window X. If for some reason, the record is already saved and the user closes the form, then form BeforeUpdate won't prevent creating a record - and that can be caused by clicking on a Save button. I think you'll need a 2 prong approach, but it depends on your db. Perhaps try this in the form BeforeUpdate event (air code - untested):

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim response As Integer

If Me.Dirty = True Then
   response = MsgBox("Do you want to save this record?", vbYesNo)
   If response = vbNo Then Cancel = True
End If

End Sub

That should prevent an unsaved record from being saved. If it still happens when user clicks on something else, you'll have to explain the process in more detail but pics won't be necessary. Likely the record will have to be deleted. If that hasn't worked thus far, then there is something wrong with the way that has been created.

[Gicu]

I think you might also want o have a look at the Undo method:https://www.microsoft.com/en-us/micr...ngs-in-access/

https://stackoverflow.com/questions/...closing-a-form

Cheers,

[S400]

Hi Micron,

I tried the BeforeUpdate coding you suggested - which work technically but then causes another problem.
The when Cancel is set to True, the user then sees a system generated message which I can't suppress.

The message is "You can't save this record at this time ...... Do you want to close the database object anyway?" (in between the two sentences, there a few lines explaining something may have gone wrong, etc..
It asks for a [Yes] [No] answer.

I have tried using DoCmd SetWarnings False in an attempt to suppress this system message but, probably because it requires a Yes/No answer (?), the message still appears.

Questions:-
- Is there a way to suppress this particular system message ?
or if not
- Is there a different type of Form which can be used which is not "bound to a recordsource" and which could be used to explicitly add the record to the table only on clicking on say the [Update] button (see picture in my original post)?

Many thanks in advance for any answers !

[Micron]

I think the answer is in post 3. I believe I forgot the Undo method. That would mean the code I wrote would be cancelling the update, but further actions such as closing the form would raise an error because Access wants to save a record when doing so (by default) but cannot because the update was cancelled. However, there is leftover data in the control that still remains, so what to do with it but warn the user? Try

If response = vbNo Then 
  Cancel = True
  Me.Undo
End If

That is the problem with air code - it comes from within the air space between my ears, and there is lots of that. Do follow up on the link that Gicu provided. Oh, and probably best to remove the Set Warnings code you added.

[Welshgasman]

Hmm, seems to have asked the question today again?
https://www.accessforums.net/showthread.php?t=83681

[S400]

OK - finally achieved it.
In the end we needed our own boolean switch to indicate when the data is completely valid, and in all other cases clicking on [x] will now delete the record from the table.
Additional complication was caused by cases when the user had pressed the [Esc] key, and when this has happened and the data is incomplete then of course we have to bypass the delete operation

[Minty]

Having read this I think you simply need to undo the attempted record insert or changes. Not actually delete anything.
That's what the various answers where steering you towards.

The logic flow is

Prevent it saving if not completed (Form Before Update Event),
If you want to leave then undo everything, cancel and leave.
Else back to the form to finish it off.
Rinse and repeat.

[S400]

Hi @Minty,
We did try Undoing - using more than one approach, but just could not get it to undo in all circuntances (or sometime in any of them).
The form seems to write a record at the outset, because we have programmatically filled in some of the fields on load. Whether this was the problem with Undo or not I don't know.
In the end, a Delete worked fine, and works on all possible methods of exiting the form: right-click>Close, Close button [X], [Cancel] button, and all three of these after first pressing the [Escape] key to reset the form before exiting.

[Minty]

If your vba set any values in bound controls at form load (possibly your sequence number?), then you have effectively created a record at that point, which may be why you needed to then delete that record, rather than undoing it.
If you use a default value instead they are not considered to be "live/new data" so aren't considered.

Just random thoughts.