Record Change Confirmation

[JamesJ]

Access 2007 sp2
I'm using the following code I found on the web, on a Single Form, to have the user
choose to either save the record or not save the record or close the
message box and continue editing:

Private Sub cmdSaveAndClose_Click()

If Me.Dirty Then

Dim BlnOutcome As Integer
BlnOutcome = MsgBox("Record(s) have been added or changed." _
& vbCrLf & "Save the Changes?", vbYesNoCancel + vbQuestion, "Record Change")

Select Case BlnOutcome

Case vbCancel
Me!FieldName.SetFocus

Case vbYes
DoCmd.Close

Case vbNo
Me.Undo
DoCmd.Close

End Select

Else

DoCmd.Close

End If

End Sub

This works ok but I need to figure out what to do if the user hits the page-up or
page-down key and moves off the current record.The way it is now if the user moves
off the record the record gets saved. I want to be able to choose to save or not
but without closing the form when the user goes to another record.
I still want the option to continue to edit the current record even though the user
tries to move off the record.

Any help will be appreciated,
James

 

[Allen Browne]

Move the validation code into the BeforeUpdate event procedure of your
*form*.

That's the only way to ensure that the validation runs regardless of how the
user saves the record.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Access 2007 sp2
I'm using the following code I found on the web, on a Single Form, to have
the user
choose to either save the record or not save the record or close the
message box and continue editing:

Private Sub cmdSaveAndClose_Click()

If Me.Dirty Then

Dim BlnOutcome As Integer
BlnOutcome = MsgBox("Record(s) have been added or changed." _
& vbCrLf & "Save the Changes?", vbYesNoCancel +
vbQuestion, "Record Change")

Select Case BlnOutcome

Case vbCancel
Me!FieldName.SetFocus

Case vbYes
DoCmd.Close

Case vbNo
Me.Undo
DoCmd.Close

End Select

Else

DoCmd.Close

End If

End Sub

This works ok but I need to figure out what to do if the user hits the
page-up or
page-down key and moves off the current record.The way it is now if the user
moves
off the record the record gets saved. I want to be able to choose to save or
not
but without closing the form when the user goes to another record.
I still want the option to continue to edit the current record even though
the user
tries to move off the record.

Any help will be appreciated,
James

 

[JamesJ]

I considered that but I now have more dilemmas as I thought I would.
The form closes even though I click 'Cancel.' My wish is to be able to
return to the record for editing if I choose 'Cancel.' Even when I try to
move
to another record and click 'Cancel' it moves to the next record (if I
pressed PgDn).
I'm using the 'x' to close the form.

James

 

[Allen Browne]

Set up the form's BeforeUpdate like this:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If MsgBox("Save?", vbOkCancel, "Confirm") <> vbOk Then
Cancel = True
End If
End Sub

Now in your Cancel'n'close button, just use:
If Me.Dirty Then Me.Undo
DoCmd.Close acForm, Me.Name

And in your Save'n'close button:
If Me.Dirty Then Me.Dirty = False
DoCmd.Close acForm, Me.Name

Add error handling to the last one. If the save fails (because the user
didn't confirm or something went wrong), control will pass to the error
handler, so the form won't close.

If error handling is new, here's some info:
http://allenbrowne.com/ser-23a.html

The explicit save is necessary due to this bug in Access:
http://allenbrowne.com/bug-01.html

 

[JamesJ]

I believe you've got it!
I did change a couple things
In the Cancel button I don't want the form to close and I didn't want the
undo
and set the focus to the first field in the form.
And the Before Update works great.

I did discover another, though.
I can also disable the pgup and pgdn keys if the record gets dirty
preventing
the user form moving off the record.

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
' The Keycode value represents the key that
' triggered the event.
Select Case KeyCode
' Check for the PAGE UP and PAGE DOWN keys.
Case 33, 34

' Cancel the keystroke.
KeyCode = 0
End Select
End Sub
And the setting the Key Preview to if the record gets dirty in the On Dirty.

Error handling, what's that??
Yes I know I haven't any at all and I will be implementing it here soon.
In fact I have a book with the bookmark at the error handling chapter.

Thanks much,
James

 

[JamesJ]

Problem now is the Before Update fires even when I click the SaveAndClose
button
and before the form closes.
Which is normal but not desirable.

James

 

[Allen Browne]

Yes, you can take special action to trap PgUp and PgDown, but there are so
many ways that trigger a record save that it's no possible to trap them all.

Examples:
- apply a filter
- change the sort order
- press Shift+Enter
- click Save on the ribbon (or Records menu)
- requery the form
- assign a different RecordSource
- tab past the last entry, or shift+tab backwards past the first
- close the form
- close Access
- Alt+F4
and so on.

 

[Allen Browne]

You *don't* always want to query the user about whether to save? You're
quite happy to silently save in any case except if they click your command
button?

In that case, I've not understood what you want. Move the code back into the
button's Click event if you only want the confirmation when they click that
button.

 

[JamesJ]

Didn't realize so many things can save a record.
One of the main things I wanted to happen is to be able to stay on the
record
and continue to edit it whether I inadvertently try to move off the record
or
close the form and decide I didn't make all the changes I wanted.
I believe trapping the pgup and pgdn is working out ok for my needs.

Thanks much,
James