message box problem

[Tony Williams]

I have a command button on a form that the user clicks on to close the form
after inputting data. If the record is a new one I want a message box to pop
up that asks them to confirm whether or not they want to save the record.
I'm using this code on the OnClick property
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
If Me.NewRecord Then
MsgBox "Are you sure you want to save this new record?"
End If
DoCmd.Close
Exit_cmdClose_Click:
Exit Sub

Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click

End Sub
But it doesn't work can anyone tell me why?
TIA
Tony Williams

 

[Graham R Seach]

Tony,

If Me.NewRecord Then
If vbYes = MsgBox("Are you sure you want to save this new record?")
Then DoCmd.Close
End If

However, I think this should go in the form's BeforeUpdate event:
If Me.NewRecord Then
Cancel = (vbYNo = MsgBox("Are you sure you want to save this new
record?"))
End If

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Tony Williams]

Thanks Graham I'll take your advice
Tony

Tony,

If Me.NewRecord Then
If vbYes = MsgBox("Are you sure you want to save this new record?")
Then DoCmd.Close
End If

However, I think this should go in the form's BeforeUpdate event:
If Me.NewRecord Then
Cancel = (vbYNo = MsgBox("Are you sure you want to save this new
record?"))
End If

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Tony Williams]

Thanks Graham It works but how do I change the OK to Yes and put a No
button on the message so that if the user changes their mind they can undo
everything they've input?
TIA
Tony

 

[Graham R Seach]

Tony,

To trap a variety of responses you can either return the response into a
variable and then check the value of that variable, or return the response
as part of a Select Case block.

1. Returning the response into a variable:
Dim lReturn As Long
lReturn = MsgBox("Are you sure you want to save this new record?")
If lReturn = vbYes Then
'blah blah blah
ElseIf lReturn = vbCancel Then
'blah blah blah
End If

2. Returning the response into a Select Case block:
Select Case MsgBox("Are you sure you want to save this new record?")
Case vbYes
'blah blah blah
Case vbNo
'blah blah blah
End Select

To offer the user a variety of buttons to choose, check the list of
available options using either Access Help or Intellisense.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia