Form_Unload vs Form_Close

I

iamthenev

New member
Local time
Today, 18:27
Joined
Jun 17, 2013
Messages
7
I have a form which users complete to submit a service request. The completed fields are saved to a table. As soon as users started completing the form, a new record is saved in the table.

I would like to code it such that if they close the window (or possibly click a 'cancel' button'), the newly created record will be deleted but all the other records remain.

I tried the following:

Code: 
Private Sub Form_Unload(Cancel as Integer)
 
    If MsgBox("Cancel Service Request?", vbYesNo) = vbYes Then
    MsgBox "Your Service Request has not been submitted."
    DoCmd.GoToRecord , , acLast
    DoCmd.RunCommand acCmdDeleteRecord
 
    Else
    Exit Sub
 
    End If
 
End Sub

This only words however is the user starts completing the form and thus a new record is completed. If they open the form, don't change anything, and then close it, no new record is created and the code deletes the most recent record.

Any suggestions?

And thank you in advance to everyone who contributes to this site... I have already found many useful suggestions and solutions. A wonderful learning tool.
 
Hmm... thank you for your response but I'm not sure I follow. I'll try to clarify.

The table in question holds all the service requests (e.g., requests 1, 2, and 3). The form adds new requests to the table. If the form is opened and closed with no changes whatsoever, no new entry is added to the table. If the form is opened and any change is made to a field, a new entry is added to the table (e.g., request 4).

My desire is to have the form control whether or not a new entry is added to the table. I have a submit button set up to save the request and close the form. I have a cancel button which I want to code so that it deletes the request (using a simple DoCmd.Close).

The issue is I am not sure how to code the button so that it will delete request 4 without touching 1, 2, or 3.

In the code I entered above, if the form is opened and a change made, clicking cancel will delete the new entry (request 4). However, if the form is opened and no change is made, clicking cancel will delete request 3, which is not what I want.

I hope this makes it a bit clearer....
 
Your approach is not the one for which Access is designed.

Access, by default, saves any record that has been changed. If you are editing a record and move away from it, or from the form showing the record, the record will be saved. Unless you tell Access not to save it. To stop the record from saving, you cancel the update in the form's BeforeUpdate event. Search the forum for BeforeUpdate. It is useful to be able to deal with the BeforeUpdate, also because in that event you can verify whether all the data are as desired, and then let Access save ... or not, as the case may be.
 
Here's a hack I use that follows spikepl's suggestion. Before saving a New Record, or saving an existing Record that has been edited, it asks the if they want to save it. This code will execute regardless of how they leave the Record, whether they use the standard close buttons, a custom close button or simply by closing the Form, itself. If they answer 'no,' the New Record is dumped or the changes to the existing Record are dumped.

Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not (Me.NewRecord) Then
  If MsgBox("Would You Like To Save The Changes To This Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save Changes to Record ???") = vbNo Then
   Me.Undo
  End If
Else
  If MsgBox("Would You Like To Save This New Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save This New Record ???") = vbNo Then
   Me.Undo
  End If
End If
End Sub
Linq ;0)>
 
This is exactly what I was looking for. Thank you Linq! I am still new to VBA so seeing the code is incredibly helpful in understanding how it works.

missinglinq said:
Here's a hack I use that follows spikepl's suggestion. Before saving a New Record, or saving an existing Record that has been edited, it asks the if they want to save it. This code will execute regardless of how they leave the Record, whether they use the standard close buttons, a custom close button or simply by closing the Form, itself. If they answer 'no,' the New Record is dumped or the changes to the existing Record are dumped.

Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)
Code: 
[B]If Not (Me.NewRecord) Then[/B]
[B] If MsgBox("Would You Like To Save The Changes To This Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save Changes to Record ???") = vbNo Then[/B]
[B]  Me.Undo[/B]
[B] End If[/B]
[B]Else[/B]
[B] If MsgBox("Would You Like To Save This New Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save This New Record ???") = vbNo Then[/B]
[B]  Me.Undo[/B]
[B] End If[/B]
[B]End If[/B]
[B]End Sub[/B]
Linq ;0)>
Click to expand...
 
Okay, follow-up question: is it possible to cancel the BeforeUpdate event to prevent the form from closing?" For example (modified the second half of your provided code):

Code: 
[B]Else[/B]
[B]If MsgBox("[COLOR=red]Are You Sure You Want To Cancel This New Record?[/COLOR]", vbQuestion + vbYesNo + vbDefaultButton1, "[COLOR=red]Cancel[/COLOR] New Record ???") = vb[COLOR=red]YES[/COLOR] Then[/B]
[B] Me.Undo[/B]
[B]Else[/B]
[B][COLOR=red] Cancel=True[/COLOR][/B]
[B]End If[/B]

Would this be enough so that if the user clicks 'No', the the code stops the form from closing to allow the user to continue completing it?

missinglinq said:
Here's a hack I use that follows spikepl's suggestion. Before saving a New Record, or saving an existing Record that has been edited, it asks the if they want to save it. This code will execute regardless of how they leave the Record, whether they use the standard close buttons, a custom close button or simply by closing the Form, itself. If they answer 'no,' the New Record is dumped or the changes to the existing Record are dumped.

Code: 
[B]Private Sub Form_Unload(Cancel As Integer)[/B]
[B]If Not (Me.NewRecord) Then[/B]
[B]If MsgBox("Would You Like To Save The Changes To This Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save Changes to Record ???") = vbNo Then[/B]
[B] Me.Undo[/B]
[B]End If[/B]
[B]Else[/B]
[B]If MsgBox("Would You Like To Save This New Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save This New Record ???") = vbNo Then[/B]
[B] Me.Undo[/B]
[B]End If[/B]
[B]End If[/B]
[B]End Sub[/B]
Linq ;0)>
Click to expand...
 
First off, the code needs to be in the Form_BeforeUpdate event, as I gave it to you, instead of in the Form_Unload event.

Next, if you want to do Validation, to see if certain Fields are populated, and stop the saving of the Record if they're not, and allow the user to correct the omission, that can be done, but that's a different question. You just need to decide exactly what it is you want to accomplish and we can guide you from there.

Linq ;0)>
 
Whoops, not sure how that Form_Upload got in there. It is correctly Form_BeforeUpdate in my code.

In my form, there is a Submit Request button and a Cancel Request button. (I know that nothing is actually being "submitted", but the user will think it is, which is what matters.) What I am trying to do is force the user to click Submit Request to save the record. Clicking Cancel Request currently deletes the record and confirm to the user that the request was not submitted. Clicking submit performs a little validation and if all passes, saves the record. Clicking the 'X' now deletes the record as well, but I was hoping to adjust the code to return the user to the form if the don't want to delete the record. In other words, if the user tries to 'X' out the form, instead of asking "Would you like to save this new record", they would be asked "Are you sure you want to cancel your request?". Clicking 'Yes' would delete the record (i.e., trigger the Me.Undo) whereas clicking 'No' would simply return them to the form (i.e., cancel the form save and the form close).

I hope this clarifies what I am trying to accomplish! Here is the code for the buttons:

Submit button
Code: 
Private Sub Submit_Request_Button_Click()
 
If IsNull([Requested By]) Then
   MsgBox "Please enter a Requestor in the Requested By field.", vbInformation, "Cannot submit request"
ElseIf IsNull(Priority) Then
   MsgBox "Please select a Priority from the Drop-down menu.", vbInformation, "Cannot submit request"
ElseIf [Request] = "Enter details of the request" Then
   MsgBox "Please complete the Request field with the details of your request.", vbInformation, "Cannot submit request"
ElseIf IsNull([Received On]) Then
   MsgBox "Please select the date from the Date Requested field.", vbInformation, "Cannot submit request"
Else
   DoCmd.RunCommand acCmdSaveRecord
   DoCmd.GoToRecord , , acNewRec
End If
 
End Sub

Cancel button
Code: 
Private Sub Cancel_Request_Button_Click()
 
   If Me.Dirty = True Then
       If MsgBox("Are you sure you want to cancel your request?", vbQuestion + vbYesNo + vbDefaultButton1, "Cancel Service Request?") = vbYes Then
           Me.Undo
           MsgBox "Your Request has been cancelled.", vbInformation, "BD Service Request"
       'Else
           'do nothing
       End If
   Else
       MsgBox "A Service Request has not been started.", vbInformation, "BD Agent Service Request"
   End If
 
End Sub

missinglinq said:
First off, the code needs to be in the Form_BeforeUpdate event, as I gave it to you, instead of in the Form_Unload event.

Next, if you want to do Validation, to see if certain Fields are populated, and stop the saving of the Record if they're not, and allow the user to correct the omission, that can be done, but that's a different question. You just need to decide exactly what it is you want to accomplish and we can guide you from there.

Linq ;0)>
Click to expand...
 
Thank you Spikepl for the example!

One thing I've noticed is that it seems set up for already entered inforation. In my example, I have a my form coded so that it opens to a new entry automatically, all the fields are blank. If you start completing the form, say completing 2 of the 4 fields, then click the X, I get the correct prompt which can either save the entry, discard it , or cancel to return me to the form. But in clicking cancel, the 2 completed fields erase again.

I guess once the Form_BeforeUpdate event is triggered, the info either needs to be save or will be gone if the event is cancelled?

spikepl said:
Play with attached
Click to expand...
 
Try the attached. I added an On Undo handler which fixes the problem.
 

Attachments

You must log in or register to reply here.

Similar threads

T
Cancel,Save and Delete buttons for Order form
Replies
11
Views
749
Pat Hartman
P
D
Solved Setting a field to null on Dirty
Replies
10
Views
516
dullster
D
T
Solved Attache files from Subform in main form to Outlook
Replies
11
Views
216
theinviter
T
R
How do I save the layout of a subform after modifying it on the main form's on open event?
Replies
12
Views
366
RickSanchez
R
P
unable to open previously saved form
Replies
9
Views
453
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom