You can easily find out whether your user clicked on OK or on Cancel on a modal dialog. This example also prevents the user from unloading the form, and thereby prevents you from inadvertently reloading the form when you reference the properties of controls on the form:
Option ExplicitPrivate mUserHitOK As BooleanPublic Property Get UserHitOK() As Boolean UserHitOK = mUserHitOKEnd PropertyPrivate Sub cmdCancel_Click() mUserHitOK = False Call HideEnd SubPrivate Sub cmdOK_Click() mUserHitOK = True Call HideEnd SubPrivate Sub Form_QueryUnload(Cancel As Integer, UnloadMode _ As Integer) If UnloadMode = vbFormControlMenu Then Cancel = True cmdCancel.Value = True End IfEnd Sub
You only need this code to check the user action:
Call frmModalDialog.Show(vbModal)If frmModalDialog.UserHitOK Then ' Do Something HereElse ' Do Something Else HereEnd IfCall Unload(frmModalDialog)Set frmModalDialog = Nothing