Close form without saving record?

[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.

 

[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!

 

[directormac]

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.

@Pat Hartman : question on best practice, only tangentially related. In the above example, if ALL you needed to do was make sure the value for a given field is unique, is there a benefit to using a custom code in the BeforeUpdate (or even the form level ValidationRule property) vs. setting that field to Indexed / NoDuplicates at the table level? I recall that indexing comes with a performance cost... but if you don't need the scale of 100k or even 10k records, is that overhead acceptable? (still working SLOWLY on the "animals" project, the EAV tips were VERY helpful...)