Cancel FORM CLOSE if another SUB finds errors 2

[jdegeorge]

Hi

I have conditions in the Form's BEFORE UPDATE property that when met, I get the desired error message box and the OK button:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    Dim strMsg As String

    If Not IsNull([ctlDateClosed]) And ((Not IsNull(Me!ctlResolution)) Or Me!ctlResolution <> "") And [ctlGap_Status] = 1 Then
        strMsg = "RESOLUTION and DATE CLOSED fields cannot be entered"
        strMsg = strMsg & vbCrLf & "without changing the STATUS to CLOSED."
        strMsg = strMsg & vbCrLf & "Click OK to correct."
        MsgBox strMsg, 16, "Conversion Gap Analysis"
        Me.Undo
        Exit Sub
    ElseIf Not IsNull([ctlDateClosed]) And (IsNull(Me!ctlResolution) Or Me!ctlResolution = "") And [ctlGap_Status] <> 1 Then
        strMsg = "STATUS cannot be set to CLOSED"
        strMsg = strMsg & vbCrLf & "and DATE CLOSED cannot be entered without"
        strMsg = strMsg & vbCrLf & "entering a detailed RESOLUTION."
        strMsg = strMsg & vbCrLf & "Click OK to correct."
        MsgBox strMsg, 16, "Conversion Gap Analysis"
        Me.Undo
        Exit Sub
    Else
        If ctlAssessment = "" Then
            Dim msg, DgDef, Response As Variant
            Beep
            msg = "You must enter a detailed Assessment.  " _
                    & vbCrLf & "Click OK to try again!"
            DgDef = MB_OK + MB_ICONINFORMATION + MB_DEFBUTTON2
            Response = MsgBox(msg, DgDef, Title)
            Me!ctlAssessment.SetFocus
            DoCmd.RunCommand acCmdUndo
            Exit Sub
        Else
            DoEvents
            Exit Sub
        End If
    End If
    
End Sub

However, if this code is triggered after I click the close form button, the form closes after I click the OK button on the message, rather than remaining open so I can fix the error. This is the code behind the CLOSE button:

Private Sub btnClose_Click()

    DoCmd.Close acForm, Me.NAME, acSaveYes
    
End Sub

How can I get the Form's BEFORE UPDATE property to cancel the FORM CLOSE command in addition to giving me the error message? I'm lost!

Thanks!


Jim DeGeorge

 

[CautionMP]

Move the code from [tt]Form_BeforeUpdate()[/tt] into it's own procedure that returns a True/False (boolean) value:

Public Function RecordInvalid() As Boolean
    Dim strMsg As String
    'Initialize
    RecordInvalid = False
    If Not IsNull([ctlDateClosed]) And ((Not IsNull(Me!ctlResolution)) Or Me!ctlResolution <> "") And [ctlGap_Status] = 1 Then
        strMsg = "RESOLUTION and DATE CLOSED fields cannot be entered"
        strMsg = strMsg & vbCrLf & "without changing the STATUS to CLOSED."
        strMsg = strMsg & vbCrLf & "Click OK to correct."
        MsgBox strMsg, 16, "Conversion Gap Analysis"
        Me.Undo
        [b]RecordInvalid = True
        Exit Function[/b]
    ElseIf Not IsNull([ctlDateClosed]) And (IsNull(Me!ctlResolution) Or Me!ctlResolution = "") And [ctlGap_Status] <> 1 Then
        strMsg = "STATUS cannot be set to CLOSED"
        strMsg = strMsg & vbCrLf & "and DATE CLOSED cannot be entered without"
        strMsg = strMsg & vbCrLf & "entering a detailed RESOLUTION."
        strMsg = strMsg & vbCrLf & "Click OK to correct."
        MsgBox strMsg, 16, "Conversion Gap Analysis"
        Me.Undo
        [b]RecordInvalid = True
        Exit Function[/b]
    Else
        If ctlAssessment = "" Then
            Dim msg, DgDef, Response As Variant
            Beep
            msg = "You must enter a detailed Assessment.  " _
                    & vbCrLf & "Click OK to try again!"
            DgDef = MB_OK + MB_ICONINFORMATION + MB_DEFBUTTON2
            Response = MsgBox(msg, DgDef, Title)
            Me!ctlAssessment.SetFocus
            DoCmd.RunCommand acCmdUndo
            [b]RecordInvalid = True
            Exit Function[/b]
        Else
            DoEvents
        End If
    End If
End Function

Then you can use this in both the [tt]BeforeUpdate()[/tt] and [tt]Close()[/tt] events:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Cancel = RecordInvalid
End Sub
Private Sub btnClose_Click()
  If Not RecordInvalid Then 
    DoCmd.Close acForm, Me.NAME, acSaveYes
  End If
End Sub

Hope this helps,
CMP

Funny thing about being unemployed, weekends don't mean quite so much, just means you get to hang out with your working friends. Primus

 

[jdegeorge]

CMP

I'm working through this but am having trouble with syntax on form fields:

IsNull([ctlDateClosed]) And (IsNull(Me!ctlResolution) Or Me!ctlResolution = "") And [ctlGap_Status] <> 1 Then

It's not liking the "Me!" syntax. There are several forms for which I want to use this code. Can I make it generic or do I have to have several functions like

IsNull([Forms]![frmGapEdit]![ctlDateClosed]) And (IsNull([Forms]![frmGapEdit]![ctlResolution]) Or [Forms]![frmGapEdit]![ctlResolution] = "") And [Forms]![frmGapEdit]![ctlGap_Status] <> 1 Then

Jim DeGeorge

 

[jdegeorge]

CMP

I tried something generic:

    If Not IsNull(ctlDateClosed) And ((Not IsNull(ctlResolution)) Or (ctlResolution) <> "") And (ctlGap_Status) = 1 Then
        strMsg = "RESOLUTION and DATE CLOSED fields cannot be entered"
        strMsg = strMsg & vbCrLf & "without changing the STATUS to CLOSED."
        strMsg = strMsg & vbCrLf & "Click OK to correct."
        MsgBox strMsg, 16, "Conversion Gap Analysis"
        RecordInvalid = True
        Exit Function
    ElseIf Not IsNull(ctlDateClosed) And (IsNull(ctlResolution) Or (ctlResolution) = "") And (ctlGap_Status) <> 1 Then
        strMsg = "STATUS cannot be set to CLOSED"
        strMsg = strMsg & vbCrLf & "and DATE CLOSED cannot be entered without"
        strMsg = strMsg & vbCrLf & "entering a detailed RESOLUTION."
        strMsg = strMsg & vbCrLf & "Click OK to correct."
        MsgBox strMsg, 16, "Conversion Gap Analysis"
        RecordInvalid = True
        Exit Function

No matter what I do: fill in all expected fields, fill in one but not the rest, or do nothing at all, it thinks the second condition is met.

Could it be the field references? If I have to add all the "[Forms]![frmXXXX]!" syntax and create multiple procedures I'll do that.


Jim DeGeorge

 

[CautionMP]

It sounds like you are trying to put this in a standard module which is why the [tt]Me.[/tt] is not working.

Include it in the form module, the same place the [tt]Before_Update[/tt] and [tt]Close[/tt] procedures occur.

CMP

Funny thing about being unemployed, weekends don't mean quite so much, just means you get to hang out with your working friends. Primus

 

[jdegeorge]

CMP

I wish I could give you more than 1 star! This is great!

Thanks ever so much.

Jim DeGeorge