Conditionally saving a record

[Real Wally]

Hi,

I'd like to get a msgbox when closing a form that gives me the option to either save or not save the record, but only if changes have been made to the record.

I've got this so far but that doesn't work:

If Me.Dirty Then
MsgBox(StrMsg, vbQuestion + vbYesNo, "Save this Record") = vbYes
DoCmd.RunCommand acCmdSaveRecord
End If

It wants another 'Then' or so after the vbYes but that is incompatible with the 'Then' after If Me.dirty.

What's the trick?

Thanks,

Wally

 

[AncientOne]

A number of ways.
Dim Result
If Me.dirty then Result=MsgBox(.....) (not a block IF)
if Result=VbYes then doCmd......

Or If Me.Dirty then
If Msg box(...)=vbyes Then
DoCmd....
End if
EndIf

 

[Real Wally]

Hi AncientOne,

No luck with your code.
I can only write If and Then with a first capital leter, they get changed to capital automatically.

I've tried all sorts of variations. This is what I ended up with:

Private Sub close_Click()


Dim stDocName As String
Dim stLinkCriteria As String
Dim StrMsg As String
Dim Result

If Me.Dirty Then
Result = MsgBox(StrMsg, vbQuestion + vbYesNo, "Save this Record")
End If
If Result = vbYes Then
DoCmd.RunCommand acCmdSaveRecord
DoCmd.close
End If

If MsgBox(StrMsg, vbQuestion + vbYesNo, "Save this Record") = vbNo Then
DoCmd.close
End If
stDocName = "frmEdit"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

It's not really satisfactory as it asks me twice if I want to save when I close a 'dirty' form.

Further advice would be appreciated.

Wally

 

[ghudson]

This is not my prefered way to do this but this code will allow you to prompt the user
if they want to save or undo the current record changes using the forms BeforeUpdate event.

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Form_BeforeUpdate_Err
        
    Dim strMsg As String
    
    Beep
    
    strMsg = "Do you want to save your changes to the current record?" & vbNewLine & vbNewLine
    strMsg = strMsg & "Click ''Yes'' to Save changes or ''No'' to Discard changes."
    Beep
    If MsgBox(strMsg, vbQuestion + vbYesNo, "The current record has been altered") = vbYes Then
        'do nothing which allows the record to be saved
    Else
        DoCmd.RunCommand acCmdUndo
    End If
    
Form_BeforeUpdate_Exit:
    Exit Sub
    
Form_BeforeUpdate_Err:
    MsgBox Err.Number & " " & Err.Description
    Resume Form_BeforeUpdate_Exit
    
End Sub

06/13/2003 - I modified and removed the @ formatting since it only worked in Access 97.

HTH

 

[ghudson]

Check out my A better mouse trap? sample to see how I force a user to click my custom Save button to prevent them from leaving the current record if it is Dirty unless they either Save or Undo their changes to the current record.

HTH

 

[Real Wally]

Thanks for your suggestion.
I gave it a 30 seconds try and got an error message probably due to interference with some other code.
Í won't have time before Sunday to work on this but will give it a serious go then and will post the result.

Thanks for your help,

Wally

 

[Rich]

Public Function Conf()
Dim Msg, Style, Title, Response, MyString, frm As Form

Dim ctlCurrentControl As Control
Dim strControlName As String
Set ctlCurrentControl = Screen.ActiveControl
strControlName = ctlCurrentControl.Name

Beep
Msg = "You have altered the" & " " & strControlName & " " & "field from" & " " & "[" & Screen.ActiveControl.OldValue & "]" & " " & "To" & " " & "[" & Screen.ActiveControl & "]" & " " & "are you sure you wish to do this ?"
Style = vbYesNo + vbExclamation + vbDefaultButton1
Title = "Confirm record change"
Response = MsgBox(Msg, Style, Title)
If Response = vbNo Then
Screen.ActiveForm.Undo
Exit Function

End If
End Function



Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord = False Then
Call Conf
End If
End Sub

 

[AncientOne]

there are two types of If statements. One is a Block If , where you type the condition to be tested on the first line, ending in Then followed by what is to be done if the condition is TRUE and ending on a separate line with End If .

The other If statement is one where condition and action are on the same line:
e.g.
If (IAMMAD) Then ShootMe

This requires no End if statement.

If Me.Dirty Then
If MsgBox(StrMsg, vbQuestion + vbYesNo, "Save this Record") = vbYes
DoCmd.RunCommand acCmdSaveRecord
End If
End If

is an example of two nested Block Ifs and will work.

So will:
If Me.Dirty Then Result= MsgBox(StrMsg, vbQuestion + vbYesNo, "Save this Record")
If result= VBYes Then DoCmd.RunCommand acCmdSaveRecord


You may prefer Rich's method, which is more sophisticated.

In which case, put the first code snippet into a module and the second into the BeforeUpdate event of the form.

Thanks to Access's autocorrect feature if you type IF, it will automatically be changed to If. I just made a typo in my original post

 

[Real Wally]

Problem solved

Thanks GHudson, Rich and AncientOne for your advices and explanations. With a little more fiddling I've got it working now.

 

[Accessator]

Public Function Conf()
Dim Msg, Style, Title, Response, MyString, frm As Form

Dim ctlCurrentControl As Control
Dim strControlName As String
Set ctlCurrentControl = Screen.ActiveControl
strControlName = ctlCurrentControl.Name

Beep
Msg = "You have altered the" & " " & strControlName & " " & "field from" & " " & "[" & Screen.ActiveControl.OldValue & "]" & " " & "To" & " " & "[" & Screen.ActiveControl & "]" & " " & "are you sure you wish to do this ?"
Style = vbYesNo + vbExclamation + vbDefaultButton1
Title = "Confirm record change"
Response = MsgBox(Msg, Style, Title)
If Response = vbNo Then
Screen.ActiveForm.Undo
Exit Function

End If
End Function



Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord = False Then
Call Conf
End If
End Sub

Hi mate !, you're procedure looks great with the Screen.ActiveControl.Value thingy, but when I apply it on my code, your procedure isn't working or conflicting with my close button code which is:

Private Sub btnClose_Click()
On Error GoTo Err_btnClose_Click

If Me.Dirty Then
Dim strMsg As String
Dim iResponse As Integer

' Specify the message to display.
strMsg = "Do you wish to save the changes?" & Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard changes."

' Display the message box.
iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record to the Database ?")

' Check the user's response.
If iResponse = vbNo Then

' Undo the change.
Me.Undo

' Cancel the update.
Cancel = True
Me.AllowAdditions = False '--> so that the last blank new line is suppressed
End If
End If

DoCmd.Close

Exit_btnClose_Click:
Exit Sub

Err_btnClose_Click:
MsgBox Err.Description
Resume Exit_btnClose_Click

End Sub

any idea ?