Form: Exit without saving?

[Beard36]

I've got a long and complicated problem, but what it boils down to is this -

When a user clicks on the X icon to close a form I want to throw up a question - "Exit without saving?" where either they click Yes and the form closes, any changes they made forgotten, or they click No and the form stays open, the changes they've made remain visible, but the changes are NOT saved to the database.

This is the problem: say the form is open, the user makes a change and clicks X. I put the prompt in Form_BeforeUpdate to try to capture any change saving. If they say Yes (exit without saving) then I go Me.Undo and allow the form to close. If they say No they I need to cancel the update and cancel the close (done by cancel=true in Form_Unload).

This shouldn't be a problem but when I do it, I get the error message -

You can't save this record at this time.

Microsoft Office Access may have encountered an error while trying to save a record.
If you close thie object now, the data changes you made will be lose. Do you want to close the database object anyway?

Yes No

This makes sense in a way I guess - Access' close procedure is to save changes then exit form. When it tries to do this "something" stops it from working as it expects (ie. I've cancelled the update), so it gives me a warning.

If I could just cancel this warning then all my problems would go away (until I find some new ones, at least!

Is this possible?

PS: I'm working on this project in Access and don't have a huge amount of experience with it, which is why I'm struggling with problems like this, and why I've been hammering this forum a little (well, like 3 questions in as many days) - once again, the help given on this board is of much value and very much appreciated. Thanks!

 

[Crowley16]

docmd.close acform, "frmName, acSaveNo

 

[RoyVidar]

Hi!

Not 100%, but I think this message is a form error, perhaps the 2169. You could try adding the following in the forms error event:

[tt]if dataerr = 2169 then
response = acdataerrcontinue
end if[/tt]

- which should basically instruct Access to continue the "ordinary" process without displaying the message (or do a msgbox dataerr to find the correct number to trap for).

Roy-Vidar

 

[OhioSteve]

The short answer is that it will not be easy to do what you describe. Generally, we bind data sources to Access forms. This means that the form will automatically update the data source. Consequently, it will be difficult to say, "come back data, don't go into that table". It will be like trying to catch the wind.

If you really want to do this, you will need to reduce your reliance on binding. You will need to use macros and/or VB code. I would be willing to assist you, but it will be a pretty signifigant task. It might take 8-10 hours to implement the changes.

 

[Beard36]

Crowley: I was under the impresson that the acSaveNo in that context refers to design changes on the form. Certainly, alas, it won't exit the form without saving record data changes.

RoyVidar: I had quite high hopes for this plan, and you were right - error 2169 is the one in question. However, after trapping this error and selecting "No" (don't quit without saving changes), it displays the form again but.. the changes have been lost, the form is showing the values that are saved in the table again.

OhioSteve: your mention of playing withing the binding in code got me thinking - I had previously thought about having a set of controls that were unbound, populated when I went into edit mode with the current values and then I could change/update/save/not save their values as I liked and not update the table until I was completely happy. However, this felt like a lot of work and I was concerned that it would involve updating the code everytime I put a new editable field on the form.

However, THIS got thinking - instead of doing the work manually (maybe having to create these unbound controls by hand, or at least setting up references to them in code), I could use the Controls collection in Access.

When the X is clicked, the user is prompted "Exit without saving" from one of two places - either Form_Unload, if they have made no changes to the field, or Form_BeforeUpdate, if they HAVE made changes. The Form_Unload version is fine because there are no change to worry about keeping or ignoring. What I needed was for changes to be not Updated to the database, but remembered on the form. So in Form_BeforeUpdate, I loop through all of the controls of the form and create an array storing the name of each editable control and its state (in my case this is especially easy - I just have textboxes and comboboxes and so just record their .value property).

Then I call Me.Undo so that the Update, which automatically happens when the user clicks on the X, doesn't save any data in the database, then the Form_Unload sub is allowed to either go ahead and close (depending upon what the user replied to the "Exit?" question) or restore the values to the controls from this array and then NOT close the form.

Ok, I hope I've made myself clear. Basically I just thought it would be rude to ask questions and then not post when I've found a suitable answer, but if I've rambled on and bored you to tears with the ins and outs of the end result then I'm sorry!

(Also, I have come across a couple of similar(ish) questions to this across the 'web but with no definite answers. My answer here might not be the best, but it works for me and is adaptable to other situation, so maybe it will be useful to someone else one day!).

Once again, thanks for everyone's time.

 

[RoyVidar]

Hi again!

Nice to hear you've found a working solution!

I didn't realise you also needed to preserve the unsaved changes, just focused on removing the message (healing the symptoms, but not the disease;-)). So - here's another way that might also achiev this:

Remove the "x" in the upper corner, and use your own close button to close the form (and also trap for other possibilities of closing the form, as for instance CTRL+F4, CTRL+W).

Here's a simple routine involving a close button, keypreview of the form set to yes, trapping of the mentioned combinations (which will invoke the close routine), and relying on the error from performing me.dirty when it's cancelled in the forms before update:

[tt]Private Sub cmbClose_Click()
Call myclose
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If (MsgBox("save or cancel", vbOKCancel) = vbCancel) Then
Cancel = True
End If
End Sub

Private Sub myclose()
If Me.Dirty Then
On Error Resume Next
Me.Dirty = False
' Force a save, invoking the before update
If (Err.Number = 2101) Then
' Trapping for the "The setting you entered
' isn't valid for this property" error which
' means you've cancelled the before update.
Err.Clear
Else
DoCmd.Close acForm, Me.Name
End If
Else
DoCmd.Close acForm, Me.Name
End If
End Sub

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
If ((Shift And acCtrlMask) > 0) Then
If (KeyCode = vbKeyF4) Or (KeyCode = vbKeyW) Then
Call myclose
End If
End If
End Sub[/tt]

Roy-Vidar

 

[RoyVidar]

Must be out of focus today, here also trying to ensure the message pops up only when closing the form, and disable the keycode of the shortcut keys...

[tt]Public mfClose As Boolean

Private Sub cmbClose_Click()
mfClose = True
Call myclose
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If mfClose Then
If (MsgBox("save or cancel", vbOKCancel) = vbCancel) Then
Cancel = True
End If
mfClose = False
End If
End Sub

Private Sub myclose()
If Me.Dirty Then
On Error Resume Next
Me.Dirty = False
' Force a save, invoking the before update
If (Err.Number = 2101) Then
' Trapping for the "The setting you entered
' isn't valid for this property" error which
' means you've cancelled the before update.
Err.Clear
Else
DoCmd.Close acForm, Me.Name
End If
Else
DoCmd.Close acForm, Me.Name
End If
End Sub

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
If ((Shift And acCtrlMask) > 0) Then
If (KeyCode = vbKeyF4) Or (KeyCode = vbKeyW) Then
KeyCode = 0
mfClose = True
Call myclose
End If
End If
End Sub

Private Sub Form_Load()
mfClose = False
End Sub[/tt]

Roy-Vidar

 

[Beard36]

I had thought about disabling the X button since on the actual form I'm using - I didn't explain all this originally, I thought I post was quite long enough! - I have two buttons: "Save" and "Discard Changes". Basically you make the changes and then click Save and they're stored. Or you click on "Discard" and you get a message asking if you're sure, if so then changes are lost and the form closes, otherwise cancel the close. This part was a WHOLE lot easier than handling the X button because nothing was forcing an Update (which the X does).

But the point is, I could have disabled the X because all the functionality the user needs is on the form. However, in the interests of user friendliness/consistency/whatever you want to call it, I really wanted to keep the X button. It's just there, staring at you, daring you to shut the form! And if I kept wanting to use it to close the form even though I knew the Save and Discard buttons could handle closing the form, I was sure my users would get confused.

On the other hand, I hadn't thought about all the work that would be involved in catching the [ALT]-[F4] etc.. combination if I had disabled the X button, so thanks for all that info in case I ever need it another time.

Well, it's 5 o'clock and time to leave the office now. Once again I've been amazed not only at the knowledge readily given out here, but the speed. Thanks again everyone.

 

[OhioSteve]

It is possible to manage all of a form's operations using macros and brief code snippets. If you would like to explore that option, post.

 

[Beard36]

I'd be interested to see how I'd go about setting it up. I think I'm mostly happy with the current set up, but if there is a better way I'd certainly like to consider it.

(ie. I don't want you to have to go crazily out of your way, but if you could give me some pointers on how I would manage the form's operations as you've described I would very much appreciate it).

Cheers.

 

[OhioSteve]

Let's start with a couple of questions

1) Does the form have subforms?
2) Does the underlying table have a primary key?

 

[Beard36]

The form does not have subforms and the table doesn't have a primary key.

 

[OhioSteve]

Okay, let's start working on a form that allows the user to browse a table, but the form does not have any bound data.

I always try to break complex problems into simpler problems. Let's start with a form that can display an integer value and increment it. This is a good first step, because later we will need to select a record based on an integer field in the form.

1) Create a form with no control source.
2) Create a text field with no control source. Call it "markerField".
3) Create two buttons. Do NOT let the wizard write any code for them. Their labels will be "forward" and "back".
4) Create a macro called "backwards". It will have one action, set value. Use this text as a model for the arguments~

[Forms]![Form3]![markerField]
[Forms]![Form3]![markerField]-1

5) Create a macro called "forwards". It will have one action, set value. Use this text as a model for the arguments~

[Forms]![Form3]![markerField]
[Forms]![Form3]![markerField]+1

6) Browse the form's properties and find "on open". Put some code in that property. The code will be "Me.markerField = 1".

7) Browse the backwards button's properties. Find the "onclick" property and add this code:
If Me.markerField > 1 Then
DoCmd.RunMacro "backwards"
End If

7) Browse the forwards button's properties. Find the "onclick" property and add this code:
If Me.markerField < 10 Then
DoCmd.RunMacro "forwards"
End If

After you save everything, test the form. It should open without an error message, and markerField should be 1. You should be able to use the buttons to increment markerField between 1 and 10.

Note:
I don't work in Access frequently. Unfortunately, I don't recall the syntax for getting a field value from a query. We will need to get that syntax, because we will soon be writing stuff like ~

x = field a from query b

I also don't recall how to do a set value without using a macro. That's why I used a macro.

 

[OhioSteve]

Okay, I can give you some additional instructions now.

1) Create a table called "myTable". It should have two fields "marker" (autonumber) and "myField" (text). The autonumber field will populate automatically. Type some text into the other field. For now, make ten records.

2) Create a query called "myQuery". Use this syntax similar to this~
SELECT myTable.marker, myTable.myField
FROM myTable
WHERE (((myTable.marker)=[Forms]![Form3]![markerField]));

3) Add a field to your form. Call the field textField.

4) Add a second set value action to the forwards macro. Use arguments similar to this~
[Forms]![Form3]![textField]
DLookUp("myField","myQuery")
Add this new argument AFTER the existing argument.

5) Add the exact same argument to the backwards macro. Add it AFTER the existing argument.

Now the form should allow you to browse myField for the first ten records in the table.

Post after you complete all of this, or when you have a question. I won't post again until I hear from you.

 

[Beard36]

Sorry I've taken so long to get back to you on this. I've been mad busy this week. I have been able to try what you've written above, and had a play around with it. It has given me some ideas that I might be able to experiment with in the future, but with the current workload I don't know if I can really afford to go branching out into new ideas at the moment.

However, I want to say thanks again for the time you put in so far and the offer of more. Cheers.

 

[Rothericus]

Hi there,

I have just read with interest this problem re the close button. It seems I have been having similar problems to Beard36. I too have been building an Access database for the first time. I am 99% of the way there and have only just realised that neither of my two buttons: 'Discard' and 'Save & Exit' appear to work as intended! The VB do.cmd close form etc with the acSaveNo property simply does not seem to work. (ie the changes go through regardless) Nor does the acPrompt property. Given the database is for non-technical users I wanted the system to be pretty much foolproof, and so decided to substitute the close button for these two buttons. This is immensely frustrating at this stage and I was hoping someone could help.

Rothericus

 

[Beard36]

Well maybe I'll be able to share some of my experience here then!

The first problem is that the save options with DoCmd.CloseForm only refer to (so I understand) design changes on the form - not the actual data.

Can you give a little more clarification re: what isn't working with the buttons? I would guess that the "Save & Exit" button is working fine, but "Discard" isn't. Is that correct?

This is because when the form is closed (or the current record changed, etc..) data changes are automatically saved (regardless of the acSaveYes/No options). But if you wanted to close the form without saving changes you would have code on the "Discard" button that included the line Me.Undo before closing the form - this "forgets" all changes to the data since the last save.

 

[Rothericus]

Excellent. Thanks very much, using 'Me.undo' seems to work fine. I still cannot get the acPrompt property to work but can live without it. This final 1% of the project has wasted an enormous amount of time - I'm not sure I could have worked this out from the help files, and I'm surprised there is nothing (prominent) on this in the documentation. Maybe there is! Doesn't seem entirely intuitive.

Thanks again, I'll now be able to get this out on time in intended form.

R

P.S. Having broswed about a little, I agree on the quality of this site.

 

[Beard36]

Are you trying to get acPrompt to work to ask if you're sure you want to Exit and Discard changes, etc..?

If so, I think the easiest thing to do would be to use a msgbox in the code that's called when the button is pressed. You could just ask every time they press Discard whether they're sure they want to exit, or you could check the form's Me.Dirty property which indicates whether or not there are any changes to discard.

 

[OhioSteve]

If you ever want to continue learning about forms without data sources, post. I will see the post.