Allow user to close a form without saving

[beacon]

Hi everybody,

I'm using Access 2003 and I have a form that has a custom cmdClose button. When pressed, a message box appears asking if the user wants to go back to a previous form.

The problem is that if the user has typed in anything, the record is getting saved and I'm trying to avoid that, if possible.

Here's my code:

Private Sub cmdCancel_Click()
    Dim canVal As Integer
    canVal = MsgBox("Would you like to begin a new assessment?", vbYesNoCancel + vbQuestion)
        If canVal = vbCancel Then
            DoCmd.CancelEvent
        ElseIf canVal = vbNo Then
            MsgBox "You will now be returned to the Main screen", vbOKOnly + vbExclamation
            DoCmd.Close acForm, Me.Name
            DoCmd.OpenForm "Switchboard", acNormal
        ElseIf canVal = vbYes Then
            If (Me.Dirty = True) Then
                Me.Undo
            End If
            DoCmd.OpenForm "Switchboard", acNormal
            DoCmd.Close acForm, Me.Name
        End If
End Sub

Am I missing something or is this possible?

Thanks...

[DonRayner]

You could try addig this between lines 7 & 8

DoCmd.DoMenuIte m acFormBar, acEditMenu, acUndo, , acMenuVer70

[beacon]

You could try addig this between lines 7 & 8

DoCmd.DoMenuIte m acFormBar, acEditMenu, acUndo, , acMenuVer70

What does that do? Does it prevent the record from getting saved? I'm not familiar with the DoMenuItem command.

[missinglinq]

The DoMenuItems really should be avoided these days. They're kept for backwards compatibility. This particular one is the same as

Me.Undo

which has the added benefit of being much shoter to enter.

Linq ;0)>

[missinglinq]

I'm a little confused. Is your purpose here to never save a record if this button is clicked? Currently you're not saving the record if the user responds Yes. If you place code as Don suggested you'll be dumping the record if the user chooses Yes or No.

Maybe it would help us help you if you explained your goal here in plain English.

Linq ;0)>

[beacon]

I'm a little confused. Is your purpose here to never save a record if this button is clicked? Currently you're not saving the record if the user responds Yes. If you place code as Don suggested you'll be dumping the record if the user chooses Yes or No.

Maybe it would help us help you if you explained your goal here in plain English.

Linq ;0)>

I have another command that I use to submit the form. That's the only command that I want to use to save the form.

This command is going to be used to return to the main menu without saving the record on the table. Basically, it's an easy way for the user of the app to "start over" without adding any wrong information.

I'm posting the code again because there was a mistake in line number 15:

Private Sub cmdCancel_Click()
    Dim canVal As Integer
    canVal = MsgBox("Would you like to begin a new assessment?", vbYesNoCancel + vbQuestion)
        If canVal = vbCancel Then
            DoCmd.CancelEvent
        ElseIf canVal = vbNo Then
            MsgBox "You will now be returned to the Main screen", vbOKOnly + vbExclamation
            DoCmd.Close acForm, Me.Name
            DoCmd.OpenForm "Switchboard", acNormal
        ElseIf canVal = vbYes Then
            If (Me.Dirty = True) Then
                Me.Undo
            End If
            DoCmd.OpenForm "frmCampusSelect", acNormal
            DoCmd.Close acForm, Me.Name
        End If
End Sub

[missinglinq]

Then just add lines 11, 12 and 13 of your posted code between lines 7 and 8. I think you also need to either reverse lines 14 and 15, or change line 15 to replace the Me.Name in

DoCmd.Close acForm, Me.Name.

with the actual name of your first form. Once you open frmCampusSelect it becomes the Me of Me.Name, so you need to explicitly name the form you're closing.

Linq ;0)>.

[beacon]

Then just add lines 11, 12 and 13 of your posted code between lines 7 and 8. I think you also need to either reverse lines 14 and 15, or change line 15 to replace the Me.Name in

DoCmd.Close acForm, Me.Name.

with the actual name of your first form. Once you open frmCampusSelect it becomes the Me of Me.Name, so you need to explicitly name the form you're closing.

Linq ;0)>.

So, theoretically, couldn't I just swap line 14 and 15 to ensure that the correct form is closed?

Also, should I put the...

if (me.dirty = true) then
     me.undo
end if

...in each section of the conditional statements to ensure that the form is clear if the user returns to it?

And, just to be sure...if the user selects the Close command, the form should close without saving, right? Is it a matter of having information in the form when the form is closed that determines whether or not a row gets saved?

[beacon]

I think I answered my own question. I decided to reword the first message box to reduce the number of options the user has, which subsequently reduced the number of conditions I have to code for.

Here's what I ended up with, in case some needs it in the future:

Private Sub cmdCancel_Click()
    Dim canVal As Integer
    canVal = MsgBox("You have elected to close the form without saving." & vbCr & vbCr & "Are you sure you want to continue?", vbYesNo + vbExclamation, "Close Form")
        If canVal = vbNo Then
            DoCmd.CancelEvent
        ElseIf canVal = vbYes Then
            MsgBox "You will now be returned to the Main screen", vbOKOnly + vbInformation, "Returning to Main"
            If (Me.Dirty = True) Then
                Me.Undo
            End If
            DoCmd.Close acForm, Me.Name
            DoCmd.OpenForm "Switchboard", acNormal
        End If
End Sub

Thanks for the help everybody...I'l l probably be back in the next few days with some more questions once I stumble upon them.

Until then...

[missinglinq]

"So, theoretically, couldn't I just swap line 14 and 15 to ensure that the correct form is closed?"

If you re-read the post, you'll see that's what I said.

"And, just to be sure...if the user selects the Close command, the form should close without saving, right?"

Yes, anytime you invoke Me.Undo it deletes any data entered, if a new record, and any data added since the last "save" if it's a previously existing record.

"Is it a matter of having information in the form when the form is closed that determines whether or not a row gets saved?"

If a new record has been created or an existing record edited, closing the form, in most cases, will result in the record being saved. Notice that I said in most cases!

One of many quirks Access has involves the use of DoCmd.Close to close a form. If a table has one or more fields that are defined as required or you have validation code in your form to insure that fields are populated or that their data is formatted in a certain way, and they do not conform to these validations or requirements, and you use DoCmd.Close, Access will dump the record, without issuing any warnings that is about to do so, and then close the form!

In your case this doesn't matter, because you want the record dumped. But in a situation where you wanted the record saved and want a warning message if the data doesn't comply, you need to always explicitly save the record before closing the form.

If Me.Dirty = True Then Me.Dirty = False
DoCmd.Close

When the record is saved, warnings about required fields or validation errors will be shown, allowing for correction before closing the form.

Your latest code looks like it should work for you just fine, now.

Linq ;0)>

[beacon]

Sorry Linq...I read that you said to reverse lines 14 and 15, but I don't think it registered that that's what you meant. It took me trying what you suggested for it to make sense, so please don't think I was being a jerk.

Thank you for all of your help. I know this post may have seemed a little strange, but those are the kinds of things I get to deal with when my company wants to change from using something like FoxPro to Access, but still want the exact same functionality.

Ciao for now...

[missinglinq]

No problem, beacon! "Suits" that want to use one product but make it look exactly like another product are the bane of all developers, along with those who don't know squat about programming but are going to tell you exactly how you need to do something!

Linq ;0)>