Close form without saving record? (1 Viewer)

[Irenaught]

Im very new so this could be a dumb question but here goes. I have a form which adds records to a database of donors. On the form i have a close form button with the code that follows -

DoCmd.Close acForm, "Add Donor Form", acSaveNo

whenever the close button is pressed it saves the record to the database be it half filled empty.. whatever. I would like this to just simply close the form and not write to the table. Any help with this would be greatly appreciated. Thanks.

 

[Irenaught]

Ok I solved this myself but it took a long time to figure out so I will post it here in case anyone else has this problem and reads it. The simple answer is before your "DoCmd.Close" you need to put the statement "Me.Undo" that's it quick and painless then it doesnt matter what info is in the form it just closes it.

 

[CEH]

What do you do when you WANT to save the record.... I think what your getting into is looking for all fields to be filled or not save? Do a search on "Validation" Lots of info on this forum. With an "Undo" on close you would never have any records in your table....

 

[Irenaught]

It does work. When I want to add a record I push the "Add Record" buttoon instead of the "Close Form" button which is intended to close the for without submitting the data wether the fields are filled in or not. I will look through the validation stuff thanks for the tip.

 

[missinglinq]

Unless you're the only one using this form, I'd make sure the 'close form' button is clearly marked so that users understand it will dump the data entered!

 

[CEH]

I would take it a step further and put a message box on the close event to make sure you wanted data dumped on close. As Missinglinq said... If you are the only one using this DB then.... whatever you like... But if you have other users this is REALLY going to confuse them!

 

[fosterg]

Thanks Irenaught, I had the same problem, and your fix worked well. As to warning users, obviously it doesnt hurt, but i feel like if a user were faced with two buttons "add record" and "close form" the results of each are clear, so I wouldnt sweat it...

 

[SOS]

and just an FYI for those that might see this thread -

The acSaveNo part of this code:
DoCmd.Close acForm, "Add Donor Form", acSaveNo

has nothing to do with saving records. It has to do with saving design changes to the form. A lot of people get confused and think it has to do with saving changes to records but it doesn't.

 

[Irenaught]

Thanks for adding the info about saving the form not the record that os good info and I agree with the last guy as well, people are smart enough to know the difference between "close form" and "add record"

 

[tt1611]

Here's a question i have on this. I am trying to debug a database i have created and my code looks like this.
Private Sub Form_Unload(Cancel As Integer)
Dim response As Integer
response = MsgBox(prompt:="All unsaved data will be lost are you sure you want to close?", buttons:=vbYesNo)
If response = vbYes Then
Me.Undo
Cancel = False
Exit Sub
Else
Cancel = True
End If
End Sub

In my situation, be it empty or half filled, data in the form is still writing to the database. Anytime i open the form and hit the close button, my message box come up just right and then if the user hits yes to confirm close, i go to the table and the empty record is still there. Does anyone have any thoughts on this?

 

[tt1611]

I have now got the below to work partially

Private Sub Form_Unload(Cancel As Integer)
Dim response As Integer
response = MsgBox(prompt:="All unsaved data will be lost are you sure you want to close?", buttons:=vbYesNo)
If response = vbYes Then
Me.Undo
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Cancel = False
Exit Sub
Else
Cancel = True
End If
End Sub

First warning comes up as per my message box and second warning as per docmd code.

First warning yes. second warning yes = deletes from table
first warning no = cancels form close and you're back at the form
first warrning yes and second no. errors out to

runtime error 2501
The DoMenuItem was cancelled

Has anyone got any ideas on this?

 

[SOS]

It should be (I believe):

Private Sub Form_Unload(Cancel As Integer)
Dim response As Integer
response = MsgBox(prompt:="All unsaved data will be lost are you sure you want to close?", buttons:=vbYesNo)
 
   If response = vbYes Then
      [COLOR=red]Cancel = True[/COLOR]
      Me.Undo
      Exit Sub
   Else
      Cancel = True
   End If
End Sub

I don't see the need for the DoMenuItem things. What are they anyway? I hate that old (outdated) code. DoCmd.RunCommand items are much more legible as you can see what they are doing.

 

[missinglinq]

Nothing in posts 10-12 is going to do what you're trying to do here! By the time Form_Unload fires, the record is going to already be saved and Undo is not going to dump the record. The Form_BeforeUpdate event is the proper one for this type of thing, and someyhing like this is usually done

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 Record ???") = vbNo Then
  Me.Undo
 End If
End If
End Sub

 

[tt1611]

Thanks missinglinq. I will try that code on the before_update event today and see how I get on.

 

[arichins]

Nothing in posts 10-12 is going to do what you're trying to do here! By the time Form_Unload fires, the record is going to already be saved and Undo is not going to dump the record. The Form_BeforeUpdate event is the proper one for this type of thing, and someyhing like this is usually done

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 Record ???") = vbNo Then
  Me.Undo
 End If
End If
End Sub

Thanks for your reply Linq. I was having this same problem, and I knew how to handle it with a 'close' button, but wanted to know how to handle it if the user closes the whole window Access rather than using the form button. I tried the on_close event, but found the record had already been written by the time that event is triggered.

 

[chanvba]

Thank you missinglinq,
Your suggestion was useful for me too.

 

[AICMurf]

I just found this forum and made my account. I was looking for how to clear a customer edit form to keep it from saving mis-information. I don't know that I found that here but I thought I might chime in on the button label discussion. As a new access user I would disagree that an unknowledgeable user may assume that a button that says simply "Close Form" should understand that it means to close without saving. One of the very first things I learned was that exiting a record will automatically save any edits to that record. Therefor, closing a form designed to edit or add a record without taking action to revert changes would automatically update that record. So MY uneducated assumption would be that exiting a form without any other action would save the record to the table in the very state that it was in when I closed the form. That is the very situation I am here to learn how to overcome.

 

[arnelgp]

you can use Transaction on your form so that Any edit, or addition of record can be Reverted back.
see this demo.

 

[Pat Hartman]

@AICMurf ,
Welcome. I agree with your disagreement of the methods suggested in the old thread.

You have absolute control over whether or not a record gets saved if you simply use the correct event. There is no need for a lot of code or using transactions. You just need to put your validation code into the Form's BeforeUpdate event. Think of this event as the flapper at the end of a funnel. If the flapper is closed, the record is not saved. If the flapper is open, the record is saved.

You can get fancy and write a lot of code to force people to press a particular button in order to save but the simplest solution is to just prevent the save if the record is incomplete or contains errors. Here is an example with validation on two fields. Notice that the validation does NOT undo anything. It is far better for the user to see his error and fix it rather than have to retype the whole entry. Canceling the event leaves the form dirty. The user cannot leave the form without going through this sub. The only way out is to either fix the errors or use esc twice to cancel the update. In some cases where I'm checking permissions and the user is not authorized to make any updates, then I will use Me.Undo to clear the update and then use cancel to cancel the event so the user can exit gracefully.

The form's BeforeUpdate event CANNOT be bypassed. It is ALWAYS the LAST event that runs before a record is saved. So regardless of what prompted the save, this event catches the action of saving and allows you to validate and cancel the update if the record is invalid. There is only one case where I use the BeforeUpdate event of a control and that is if I want to stop the user early in a long data entry process. For example, if SSN is a required field and it must be unique, then I add a BeforeUpdate procedure to ensure the SSN is unique so I can give the user a heads up that he isn't going to be able to actually save the record if the SSN isn't present or isn't unique. However, the code goes in two places, the control level BeforeUpate event and also in the Form level BeforeUpdate event. In this case, to avoid duplication, I have the form level event call the control level event so the code gets executed in to places but only written once.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsDate(Me.DOB) Then
    If Me.DOB > Date() Then
        msgbox "Date of Birth may not be > Today.", vbOKOnly
        Cancel = True
        Me.DOB.SetFocus
        Exit Sub
    Else
        If DateDiff("y", Me.DOB, Date()) > 80 Then
            IF msgbox("Employee is > 80 years old.  Is this OK?", vbYesNo) = vbYes Then
            Else
                Cancel = True
                Me.DOB.SetFocus
                Exit Sub
            end If
        End If
    End If
End If   
    If Me.LastName & "" = "" Then
        MsgBox "Last Name is required.", vbOKOnly
        Cancel = True
        Me.LastName.SetFocus
        Exit Sub
    End If
    .... other validation
End Sub

 

[AICMurf]

@Pat Hartman

Thank you for the information. I can see that I am going to learn a lot from these forums! I am so new that I am not that deep into the functions and coding of access yet. I am old and ALL of my knowledge is back to when I was in school and did a little basic programming on a trs-80. I have a lot of catching up to do!