Confirm changes in form. "Are you sure you want to save?"

[mauricionava]

Hi, I have a form where a lot of people uses at the same time entering data. I need to create a litle message with a confirmation question to save the changes.

The reason is that when they open the form the first record is empty so they can enter data on it but sometimes they wan to search for a record and enter a key by mistake and that's a record saved with only one letter by mistake when they scroll to another record. I need a confirmation that says are you sure you wan to save the changes?

Thanks

 

[mauricionava]

continued...

Or undo changes if the record is already saved.

Thanks.

 

[AccessGuruCarl]

Hi mauricionava,

Here is your message...

Dim ret
ret = MsgBox("Do you want to save this record?", vbYesNo, " ")
If ret = vbYes Then
    DoCmd.Save
else
    'Delete Current Record
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    On Error Resume Next
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
End If

I think you'll need to delete the record, not undo it depending on how your form.

The next step is where to place the code? I'm quessing FormDirty.

I think it would be easier to lock the form, and add an edit button. Here is a sample of some code I use.

Private Sub Form_Current()
Dim frm As Form
Dim ctl As Control
Dim intnewrec As Integer
Dim rst As DAO.Recordset
Dim lngCount As Long
    Set frm = Me
    Set rst = Me.RecordsetClone
'Set form navigtion
    With rst
        .MoveFirst
        .MoveLast
        lngCount = .RecordCount
    End With
    Me.txtRecordNo = Me.CurrentRecord
    Me.txtRecordCnt = "of " & lngCount
    intnewrec = frm.NewRecord   
    If intnewrec = True Then
        With frm
            .AllowDeletions = True
            .AllowEdits = True
        End With
    Else
        With frm
            .AllowDeletions = False
            .AllowEdits = False
        End With
    End If
Me.T_Name.SetFocus
Me.lblFrmStatus.Caption = "LOCKED"
Form.Caption = "Customer Form | Status: LOCKED"
Me.RectRecStatus.BackColor = 16764057 'Lt Blue

--------------------------

Private Sub cmdEdit_Click()
'Lock or Unlock Form Controls
Dim frm As Form
Set frm = Me
'Unlock Form Controls and Set Default Buttons
    If cmdEdit.Caption = "Un-Lock Record" Then
        With frm
            .AllowEdits = True
        End With
    'Change Button & Form Status
        cmdEdit.Caption = "Lock && Save"
        Me.lblFrmStatus.Caption = "UN-LOCKED"
        Form.Caption = "Customer Form | Status: UN-LOCKED"
        Me.RectRecStatus.BackColor = 10092543 'Lt Yellow
        Me.lblFrmStatus.FontWeight = 800
    'Set Timer to Flash Un-Locked Status
        Me.TimerInterval = 500
    'Set Menu Choices
        cmdDelete.Enabled = True
        cmdUndo.Enabled = True
        cmdSave.Enabled = False
        cmdSearch.Enabled = False
    'Disable Navigation and set focus to previuos control
        Screen.PreviousControl.SetFocus
        cmdAdd.Enabled = False
        cmdNext.Enabled = False
        cmdLast.Enabled = False
        cmdPrev.Enabled = False
        cmdFirst.Enabled = False
        cmdClose.Enabled = False
        Me.DateUpdated.Enabled = False
        Me.UpdatedBy.Enabled = False
    Else
'Lock Form Controls and Re-Set Default Buttons
'    Me.T_Name.SetFocus
    'Change Button & Form Status
        cmdEdit.Caption = "Un-Lock Record"
        Me.lblFrmStatus.Caption = "LOCKED"
        Form.Caption = "Customer Form | Status: LOCKED"
        Me.RectRecStatus.BackColor = 16764057 'Lt Blue
        Me.lblFrmStatus.ForeColor = vbBlack 'Reset text color to black
        Me.lblFrmStatus.FontWeight = 700
        Me.TimerInterval = 0
    'Set Menu Choices
        cmdDelete.Enabled = False
        cmdUndo.Enabled = False
        cmdSave.Enabled = False
        cmdSearch.Enabled = True
    'Re-Enable Navigation and set focus to previuos control
'        Screen.PreviousControl.SetFocus
        cmdAdd.Enabled = True
        cmdNext.Enabled = True
        cmdLast.Enabled = True
        cmdPrev.Enabled = True
        cmdFirst.Enabled = True
        cmdClose.Enabled = True
    'Set Date & User Update Value
        Me.DateUpdated.Enabled = True
        Me.DateUpdated.Value = Date
        Me.DateUpdated.Enabled = False
        Me.UpdatedBy.Enabled = True
        Me.UpdatedBy.Value = fOSMachineName
        Me.UpdatedBy.Enabled = False
    'Lock the form
        With frm
            .AllowEdits = False
            .Requery
        End With
    End If
End Sub

Of course you'll want to edit this a bit, because you open your form to a new record! The code to detect a new record can be found at

http://www.mvps.org/access/forms/frm0010.htm

Hope this helps.

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[AccessGuruCarl]

Forgot to mention....
FYI
You posted this in the Reports section of Access, not the Forms section, I just happened to make a post and noticed your's. Lucky for you I could answer this one!

You might want to repost in the forms section if you need further help.

Forgot to paste the Add Button Code.
You need to remember, If a user types anything in a bound form on a new record, it automatically creates the new record. Undo will not work, you need to delete the record.
Code for Add Button

Private Sub cmdAdd_Click()
On Error GoTo Err_cmdAdd_Click
Dim frm As Form
If cmdAdd.Caption = "Add Record" Then
'Adding New Record
    DoCmd.GoToRecord , , acNewRec
    'Change forms display status to unlocked
    Me.lblFrmStatus.Caption = "UN-LOCKED"
    Form.Caption = "Customer Form | Status: UN-LOCKED"
    Me.RectRecStatus.BackColor = 16777215
    'Disable Navigation
    cmdEdit.Enabled = False
    cmdNext.Enabled = False
    cmdLast.Enabled = False
    cmdPrev.Enabled = False
    cmdFirst.Enabled = False
    cmdClose.Enabled = False
    'Set Menu Choices
    cmdUndo.Enabled = True
    cmdDelete.Enabled = False
    cmdSave.Enabled = True
    cmdSearch.Enabled = False
    'Change Add Record Caption
    cmdAdd.Caption = "CANCEL NEW"
Else
'Cancelling New Record
    'Reset forms display status to locked
    Me.lblFrmStatus.Caption = "LOCKED"
    Form.Caption = "Customer Form | Status: LOCKED"
    Me.RectRecStatus.BackColor = 16764057 'Lt Blue
    'Re-enable Navigation
    cmdEdit.Enabled = True
    cmdNext.Enabled = True
    cmdLast.Enabled = True
    cmdPrev.Enabled = True
    cmdFirst.Enabled = True
    cmdClose.Enabled = True
    'Set Menu Choices
    cmdUndo.Enabled = False
    cmdDelete.Enabled = False
    cmdSave.Enabled = False
    cmdSearch.Enabled = True
    'Change Add Record Caption
    cmdAdd.Caption = "Add Record"
    'Goto 1st Record & Refresh Form
    DoCmd.GoToRecord , , acFirst
    Form.Refresh
End If
Exit_cmdAdd_Click:
    Exit Sub
Err_cmdAdd_Click:
    MsgBox Err.Description
    Resume Exit_cmdAdd_Click
End Sub

Good Luck

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[mp9]

Try this in the form's Before Update event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Select Case MsgBox("Do you want to save changes to this record?", vbYesNoCancel + vbQuestion)
    Case vbYes 'Go-ahead and save
        'Don't have to do anything
    Case vbNo 'Undo all changes, close without saving
        Me.Undo
    Case Else 'Cancel change, continue editing
        Cancel = True
    End Select
End Sub

http://www22.brinkster.com/accessory/

 

[mauricionava]

Thanks mp9, it works perfect.

But it still asks me eventhough I press the SAVE button.

Is there a way to cancel your code if I press the SAVE button?

 

[AccessGuruCarl]

mauricionava

After the save event,

Exit Sub

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[mauricionava]

There is an exit sub already after the save event.

Is there another way?

Thanks guys for your support

 

[AccessGuruCarl]

Paste the code so we can take a look at it.

You most likely just have to move the Cancel = True part.... to another part of the code!

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[mauricionava]

Here is my save button code:

Private Sub btnSave_Click()
Me.AllowEdits = True

On Error GoTo Err_btnSave_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_btnSave_Click:
    Exit Sub

Err_btnSave_Click:
    MsgBox Err.Description
    Resume Exit_btnSave_Click
    
End If
End If
End Sub

 

[AccessGuruCarl]

By the looks of it, you need to change where you enable edits or Remove your save Code.... It's not needed if the form controls are bound to the table!!!

Take a look at my Edit Button Code. This where I unlock the form, and enable the other buttons.

The save button should only have the code to save the record or appear to save then do something else, like Re-lock the form and other controls.

I'm guessing your form is bound to a table, and this is the error your getting. You can't Save Record at this time... Remove your save Code.... It's not needed!!!

Or if you want to go with BeforeUpdate Try this code below

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Ret

Ret = (MsgBox("Do you want to save changes?", vbYesNo, "Changes Detected"))

If Ret = vbNo Then
    DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
End If

End Sub

Hope this helps....

AccessGuruCarl
Programmers helping programmers
you can't find a better site.