How to make a Yes/No Message Box while clicking on button

Question

I would like to make a pop-up message box while clicking on "Save and Open New" button that saving a record like this one (within a Form):

Are you sure you want to save this record? Yes/No

And if the record is saved I would like to get another message box. So like this one: Record saved.

Answer 1

I don't understand how a command button fits in here. If you're talking about a bound form, you may want to intercept the automatic save operation which happens as the user navigates to another record. If that's your situation, you can ask the user in the form's before update event.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Const cstrPrompt As String = _
        "Are you sure you want to save this record? Yes/No"
    If MsgBox(cstrPrompt, vbQuestion + vbYesNo) = vbNo Then
        Cancel = True
    End If
End Sub

If the user clicks No, the update will be cancelled and she will remain on the same record with an opportunity to make different or no changes to that record. If you prefer instead to discard the pending changes, add Me.Undo to that procedure.

You can use the form's after update event to display the "Record saved" message.

You can do similar for the form's before insert and after insert events if desired.

Based on the update to your question, I think you're looking for something like this:

Private Sub cmdSaveAndNew_Click()
    Const cstrPrompt As String = _
        "Are you sure you want to save this record? Yes/No"
    If MsgBox(cstrPrompt, vbQuestion + vbYesNo) = vbYes Then
        If Me.Dirty Then
            Me.Dirty = False ' save the record
        End If
        DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
    End If
End Sub

Just don't forget that approach will not allow you to ask the user for confirmation before saving changes to the current record if the user navigates to a different record without using the command button, of if the user closes the form with unsaved changes to the current record.