Delete record

[tt1611]

I have spent the whole day trying to figure this thing out and must say my patience has run out.

This is very simple logic. I have a form that opens as a popup and a new record when the user clicks a button (open form).

If the record is new and no data is inputted, when the user closes the form (either by clicking X or clicking a close form button) no record is created in the table (this is fine)

If the user opens an already existing record to view information and make no changes, the form should close just as it opened without any prompts.
However if a new record is created and details inputted into the form, if the user closes without hitting the save button, i want a prompt box to popup asking if they wish to exit without saving. If they hit yes, the created record is deleted and the form closes. If they hit no, the form close action is cancelled. The same is also true if they open an already existing record and make changes to it before closing.

I have tried every last scenario of code and some create records where they shouldnt be and other ask to delete existing records (as part of my code - see below) where i dont want them to. Can someone please help with this.

sample code

Private Sub Form_Unload(Cancel As Integer)

If Me.Form.CurrentRecord Then
Cancel = False
Else
If Me.Form.NewRecord = True Then
Cancel = False
 
Else
If Me.Form.NewRecord = False Then
response = MsgBox(prompt:="All unsaved data will be lost are you sure you want to close?", buttons:=vbYesNo)
If response = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
Cancel = False
Else
If response = vbNo Then
Cancel = True
 
Else
Cancel = True
End If
End If
End If
   
End Sub

This above may or may not make sense so please let me know if you need any more info on this.
Thank you.

 

[boblarson]

You're using the wrong event. You need the Before Update event and not the form's Unload event.

 

[tt1611]

??
Can i close a form using the before update event..thought i could only do this from Unload or close events

Are you saying putting a

docmd.close in the before update should work?

 

[boblarson]

??
Can i close a form using the before update event..thought i could only do this from Unload or close events

Are you saying putting a

docmd.close in the before update should work?

You can close it from there and you do your validations there as well, so something like:

If Len(Me.ControlName & "") = 0 Then
   Cancel = True
   If MsgBox("You missed one, do you want to cancel the record?", vbQuestion + vbYesNo, "Confirm") = vbYes Then
      Me.Undo
      DoCmd.Close acForm, Me.Name, acSaveNo
  End If
End If

 

[tt1611]

Just like i thought. The close action does not appear to work on Before or After Updates. I actually have my code in the afterupdate because I will like for the form to only request for a save if the user makes any changes

My code is as below

Private Sub Form_AfterUpdate()
Me.lstloan.Requery
If MsgBox("Are you sure you want to abandon changes to this record?", vbExclamation + vbYesNo) = vbYes Then
DoCmd.Close , , acSaveNo
Else
Cancel = True
End If
End Sub

I am getting an error message saying the close command was cancelled. Runtime error 2501.

Can you shed some light on this if possible?

 

[boblarson]

1. In order to cancel the record, if changes have occurred, you MUST use the BEFORE UPDATE event. There is NO CHOICE of another. You cannot use the After Update event to cancel an update because it has already happened.

2. the acSaveNo on the DoCmd.Close method does NOT refer to saving records. It has to do with saving DESIGN CHANGES of the form.

3. What do you mean -

The close action does not appear to work on Before or After Updates

 

[tt1611]

Hey Bob
Thank you for getting back. My code was updated in the after update event because if the user didnt make any changes i didnt want anything to happen. I only wanted a popup box if changes were made to the record or a new record was created.

What do you mean -


The close action does not appear to work on Before or After Updates

When i run the code above in the afterupdate event, i get an error runtime 2501 the close action was cancelled

. the acSaveNo on the DoCmd.Close method does NOT refer to saving records. It has to do with saving DESIGN CHANGES of the form.

So how do i get it to close and not save when the record is created if vbyes = true

 

[boblarson]

Well a few things about your code:

Private Sub Form_AfterUpdate()
Me.lstloan.Requery
If MsgBox("Are you sure you want to abandon changes to this record?", vbExclamation + vbYesNo) = vbYes Then

You can't abandon changes to this record in the after update event.

DoCmd.Close , , acSaveNo

Use
DoCmd.Close acForm, Me.Name, acSaveNo (leaving the Me.Name exactly as shown)

Else
Cancel = True

You can't issue a Cancel = True in an After Update event. It doesn't have that option. That is available on the Before Update event (for canceling an update) but not on the After Update event, because the record has already been saved before the After Update event fires.

 

[tt1611]

Ok
As per your suggestion, this is what i have now in my before update event. I really appreciate your patience looking into this.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.lstloan.Requery
If MsgBox("Are you sure you want to abandon changes to this record?", vbExclamation + vbYesNo) = vbYes Then
DoCmd.Close acForm, Me.Name, acSaveNo
Else
Cancel = True
End If
 
End Sub

I still get the runtime error 2501 close action was cancelled

 

[tt1611]

I have attached a test file where you can see your code being tested Bob and it fails on both vbyes and vbno

 

[boblarson]

In the Before Update event you need to move the Cancel = True to this position (and add the Me.Undo):

If MsgBox("Are you sure you want to abandon changes to this record?", vbExclamation + vbYesNo) = vbYes Then
Cancel = True
Me.Undo
DoCmd.Close acForm, Me.Name, acSaveNo

 

[tt1611]

Hey Bob
Quick update for you...should have got back earlier. The above didnt work. Still generates the error message. Ended getting what i needed by going through the close button onclick event with another piece of code. Thanks for looking into this anyway