Access problems closing form with incomplete record

Question

I am trying to create a form in datasheet view, with certain fields pre-populated.

If a user clicks the X to close the form while a record is incomplete, I cannot find a way to gracefully give them the option of returning to the dirty record to correct it.

BeforeUpdate runs but setting Cancel=True doesn't stop the form closing.

Form_Unload runs and setting Cancel=True prevents close, but wipes the form contents (I haven't checked, but it appears to apply an Undo).

If I had control over when the X to close the form was enabled I might be able to avoid this. But I don't, and since I'm in datasheet view, I can't offer a close button.

The problem is that while I can use BeforeUpdate to tell the user there is a problem and I am returning them to the form to correct it, I am made a liar if the trigger action is form close (rather than record save/navigation). So the next message they receive is oops, we're closing you without saving, bad luck (your fault but too late now)!

Not to mention that despite cancelling update, the user receives a "You can't save this record at this time" message before the Form_Unload begins.

Any suggestions? Am I missing something?

wazz wazz · Accepted Answer · 2017-12-12T23:33:59

you have to play around with several events for this. at the top of the code add a variable to track form errors:

Dim frmErr As Boolean

when the form loads, and when there's an entry-error, set the error to true

Private Sub Form_Load()
    frmErr = True
End Sub

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    frmErr = True
End Sub

when the user tries to close the form:

Private Sub Form_Unload(Cancel As Integer)

    If Not Me.Dirty Then 'user hasn't done anything.
        If MsgBox("form is unchanged. close?", vbYesNoCancel, "really?") = vbYes Then
            frmErr = False
        End If
    End If

    If frmErr = True Then
        Cancel = True
    End If
End Sub

this is a basic outline. you might have to play around with the Form_Dirty event as well as before update to make it work the way you need.

Access problems closing form with incomplete record

1 Answers