The VBA Save Command

[data808]

I been using a method for saving for a long time because I have always had trouble with using the Docmd.Save command. The process I have created or should I say workaround I have used all this time is that I have a save button that the user clicks on, and what this button actually does is go to the previous record and back to the current record forces the record to be saved. Then to create the image of a regular save for the user, while that is happening, I also have code in the before update event of the form that will ask the user if they want to save the record? If they click yes, then it will save (go to previous record, then back to current record). If they click no, it will undo.



So its been working ok for a really long time but its not perfect. The annoying part is that if you click no, it will undo all the changes you made to the record. Sometimes you may click the save button and then realize that you want to add something else to the record and click no because you don't want to save yet, and there goes your record. The whole reason why I had to create such a workaround is because I could never get the save command to really work the way I thought it should. Does anyone have code or suggestions on how to have a normal save process? I just want something similar to what happens in microsoft word.

You make changes, you click the save button, it saves.
You make changes, you try to close the document, or in some cases in Access (you try to navigate to another form or record), it prompts to ask if you want to save the changes, you click yes, it saves, you click no, it undoes the changes.

Does anyone have something like that?

[June7]

Maybe you want 3 options:

Yes, save and return to record.

No, don't save, cancel update and return to record but don't undo any thing.

Cancel, don't save and don't retain edits (undo all).

The prompt can have Yes/No/Cancel buttons.

[data808]

Do you have an example code for using 3 options? Yes/No/Cancel?

I could never get it to work with this code:

If MsgBox("Save Record?", vbYesNoCancel, "Save Record") = vbYes Then
DoCmd.RunCommand acCmdSaveRecord
ElseIf MsgBox("Save Record?", vbYesNoCancel, "Save Record") = vbNo Then
DoCmd.RunCommand acCmdUndo
Else
Me.txtCustName.SetFocus
End If

I was thinking of coding this behind a button that takes you to another record within a form. Got any ideas how to get this to work? This code structure works if I used vbYesNo but once I add the cancel to the mix, it doesn't work. Here is how I used the vbYesNo:

If MsgBox("Save Record?", vbYesNo, "Save Record") = vbYes Then
DoCmd.Save
Else
DoCmd.RunCommand acCmdUndo
End If

[June7]

With 3 options, usually need a variable for the MsgBox response. Example:

intResponse = MsgBox("Save Record?", vbYesNoCancel, "Save Record")
If intResponse = vbCancel Then
...
ElseIf intResponse = vbNo Then
...
Else
...
End If

Or try Select Case structure:

Select Case MsgBox("Save Record?", vbYesNoCancel, "Save Record")
Case vbCancel
...
Case vbNo
...
Case vbYes
...
End Select