Access Form Close Coding

Question

Hello, I am trying to figure out how to keep my form from closing in Access before all of the data input fields are populated in that form. I am completely new to Access and am teaching myself as I go. Please let me know of any tips or tricks that will help me along the way. Thank you. 

-Rob

Answer 1

In addition to a Close event a form has an Unload event which executes before the Close event.  The Unload event procedure has a Cancel argument whose return value can be set to TRUE.  Cancelling the unloading of the form's recordset in this way also prevents the closing of the form, the former being a prerequisite of the latter, so the Close event is not executed.

So the data error resulting from any required column (field)  not having data, when handled in the form's Error event procedure, must also set the return value of the Unload event procedure's Cancel argument to TRUE.  As these are two separate procedures the fact that an error has occurred must be made known to the Unload event procedure.  This is done by setting the value of a module level variable, i.e. one declared in the form's module's declarations area as Private.  The value of the variable is thus exposed to the Unload event procedure and can be used in its code to set the return value of the Cancel argument to TRUE.

As Duane has said this is not trivial and would be a big ask for a novice developer.  In fact it's not as simple as you might think for an experienced developer.  It becomes simpler if you force the user to save and close the form via command buttons on the form, as these can be enabled/disabled on the basis of the current state of the record by means of code in the form's module, i.e. the Save button only becomes available when the form contains unsaved data in the current row, and the Close button is only available when the current row has either not been edited, or its edited data (including when entering a new record) has successfully been saved.

You'll find an example of the above in SaveDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly. 

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

In this little demo file, as well as validating at form level that all required columns have data, validation is also applied at individual control level by forcing the user to enter data into the controls in a specific order.  The entire code for my form's module is as below, and this can provide a model  for others who wish to reproduce the functionality in their own databases, but this again is quite a lot to ask of a novice developer.  You have nothing to lose by trying however, so if you want to give it a go I would not discourage you from doing so.  But do try to follow the advice given by Richard Feynman to his students in the quote below my signature, and study the code carefully to understand how it works.  I'll be happy to clarify any points about which you might be unsure.  Here's the module's code:

Option Compare Database

Option Explicit

Dim blnSaved As Boolean

Dim blnAllowClose As Boolean

Dim blnAllowUndo As Boolean

Private Sub cboFindContact_AfterUpdate()

    Const CANNOT_MOVE_TO_RECORD = 3709

    Const MESSAGE_TEXT = "Cannot move to record.  Save or undo current record first."

    Dim ctrl As Control

    Set ctrl = Me.ActiveControl

    If Not IsNull(ctrl) Then

        If ctrl = 0 Then

            Me.FilterOn = False

        Else

            On Error Resume Next

            Me.Filter = "ContactID = " & ctrl

            Select Case Err.Number

                Case CANNOT_MOVE_TO_RECORD

                'iinform user and set combo box to current contact

                MsgBox MESSAGE_TEXT, vbInformation, "Warning"

                ctrl = Me.ContactID

                Case 0

                ' no error

                Me.FilterOn = True

                Case Else

                ' unknown error - inform user

                MsgBox Err.Description, vbExclamation, "Error"

            End Select

        End If

    End If

End Sub

Private Sub cmdClose_Click()

    ' set variable to allow form to close

    blnAllowClose = True

    DoCmd.Close acForm, Me.Name

End Sub

Private Sub cmdSave_Click()

   Const MESSAGETEXT = "Save record?"

   If Me.Dirty Then

       ' if user confirms set variable to True and attempt to save record

       If MsgBox(MESSAGETEXT, vbQuestion + vbYesNo, "Confirm") = vbYes Then

           blnSaved = True

           On Error Resume Next

           RunCommand acCmdSaveRecord

           ' if record cannot be saved set variable to False

           If Err <> 0 Then

               blnSaved = False

           Else

               ' disable buttons

               ' move focus to another control first

               Me.FirstName.SetFocus

               Me.cmdSave.Enabled = False

               Me.cmdUndo.Enabled = False

               Me.cmdClose.Enabled = True

           End If

       Else

           blnSaved = False

       End If

   End If

End Sub

Private Sub cmdUndo_Click()

    blnAllowUndo = True

    ' undo edits

    Me.Undo

    blnAllowUndo = False

    ' disable buttons

    ' move focus to another control first

    Me.FirstName.SetFocus

    Me.cmdSave.Enabled = False

    Me.cmdUndo.Enabled = False

    Me.cmdClose.Enabled = True

End Sub

Private Sub ContactCode_BeforeUpdate(Cancel As Integer)

    Dim strCriteria As String

    If IsNull(Me.ContactCode) Then

        MsgBox "Distinct contact code must be entered", vbExclamation, "Invalid Operation"

    Else

        ' ensure value entered does not duplicate another contact's code

        strCriteria = "ContactCode = """ & Me.ContactCode & _

            """ And ContactID <> " & Me.ContactID

        If Not IsNull(DLookup("ContactID", "Contacts", strCriteria)) Then

            MsgBox "Contact code duplicates that of another contact.", _

                vbExclamation, "Invalid Operation"

            Cancel = True

        End If

    End If

End Sub

Private Sub ContactCode_GotFocus()

    If IsNull(Me.FirstName) Then

        MsgBox "First name must be entered", vbExclamation, "Invalid Operation"

        Me.FirstName.SetFocus

    ElseIf IsNull(Me.Lastname) Then

        MsgBox "Last name must be entered", vbExclamation, "Invalid Operation"

        Me.Lastname.SetFocus

    End If

End Sub

Private Sub DoB_GotFocus()

    If IsNull(Me.FirstName) Then

        MsgBox "First name must be entered", vbExclamation, "Invalid Operation"

        Me.FirstName.SetFocus

    ElseIf IsNull(Me.Lastname) Then

        MsgBox "Last name must be entered", vbExclamation, "Invalid Operation"

        Me.Lastname.SetFocus

    End If

End Sub

Private Sub FirstName_BeforeUpdate(Cancel As Integer)

    If IsNull(Me.FirstName) Then

        MsgBox "First name must be entered", vbExclamation, "Invalid Operation"

    End If

End Sub

Private Sub Form_AfterUpdate()

   ' reset variable to False

   blnSaved = False

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

    Dim strMessage As String

    Dim strCriteria As String

    ' cancel update if variable is False,

    ' i.e. save button has not been clicked

    If Not blnSaved Then

       Cancel = True

       Exit Sub

    End If

    ' ensure first and last names have been entered

    If IsNull(Me.FirstName) Then

        MsgBox "First name must be entered", vbExclamation, "Invalid Operation"

        Cancel = True

        Me.FirstName.SetFocus

        Exit Sub

    End If

    If IsNull(Me.Lastname) Then

        MsgBox "Last name must be entered", vbExclamation, "Invalid Operation"

        Cancel = True

        Me.Lastname.SetFocus

        Exit Sub

    End If

    ' ensure contact code entered and dose not duplicate value in another row

    If IsNull(Me.ContactCode) Then

        MsgBox "Distinct contact code must be entered", vbExclamation, "Invalid Operation"

        Me.ContactCode.SetFocus

    Else

        ' ensure value entered does not duplicate another contact's code

        strCriteria = "ContactCode = """ & Me.ContactCode & _

            """ And ContactID <> " & Me.ContactID

        If Not IsNull(DLookup("ContactID", "Contacts", strCriteria)) Then

            MsgBox "Contact code duplicates that of another contact.", _

                vbExclamation, "Invalid Operation"

            Cancel = True

            Me.ContactCode.SetFocus

        End If

    End If

  ' ensure names not duplicated

    strCriteria = "FirstName = """ & Me.FirstName & _

        """ And LastName = """ & Me.Lastname & _

        """ And ContactID <> " & Me.ContactID

    If Not IsNull(DLookup("ContactID", "Contacts", strCriteria)) Then

        strMessage = Me.FirstName & " " & Me.Lastname & _

            " already exists in database."

        MsgBox strMessage, vbExclamation, "Invalid Operation"

        Cancel = True

    End If

End Sub

Private Sub Form_Close()

    ' restore navigation pane

    DoCmd.SelectObject acForm, Me.Name, True

    DoCmd.Restore

End Sub

Private Sub Form_Current()

   ' reset variable to False

   blnSaved = False

   ' disable buttons

   ' move focus to another control first

   Me.FirstName.SetFocus

   Me.cmdSave.Enabled = False

   Me.cmdUndo.Enabled = False

   Me.cmdClose.Enabled = True

End Sub

Private Sub Form_Dirty(Cancel As Integer)

   ' enable buttons

   Me.cmdSave.Enabled = True

   Me.cmdUndo.Enabled = True

   Me.cmdClose.Enabled = False

End Sub

Private Sub Form_Error(DataErr As Integer, Response As Integer)

   Const IS_DIRTY = 2169

   Const MESSAGETEXT = "Record will not be saved."

   ' suppress system error message if form

   ' is closed while record is unsaved,

   ' NB: changes to current record will be lost

   If DataErr = IS_DIRTY Then

        MsgBox MESSAGETEXT, vbExclamation, "Warning"

        Response = acDataErrContinue

   End If

End Sub

Private Sub Form_Open(Cancel As Integer)

    ' minimize navigation pane

    DoCmd.SelectObject acForm, Me.Name, True

    DoCmd.Minimize

End Sub

Private Sub Form_Undo(Cancel As Integer)

    ' only allow record to be undone via Undo button

    Cancel = Not blnAllowUndo

End Sub

Private Sub Form_Unload(Cancel As Integer)

    ' prevent closure if not via button

    Cancel = Not blnAllowClose

End Sub

Private Sub Lastname_BeforeUpdate(Cancel As Integer)

    If IsNull(Me.Lastname) Then

        MsgBox "Last name must be entered", vbExclamation, "Invalid Operation"

    End If

End Sub

Private Sub Lastname_GotFocus()

    If IsNull(Me.FirstName) Then

        MsgBox "First name must be entered", vbExclamation, "Invalid Operation"

        Me.FirstName.SetFocus

    End If

End Sub

Answer 2

Hi Rob,

The first thing I would recommend is to change the Cycle property of the form to Current Record.

In Access, as soon as a record loses focus, it is saved to the table. If the Cycle property is set All Records, when the user tabs out of the last control in the Tab Order, Access moves to the next record (or a new one). By setting it to Current Record, focus will move to the first control in the Tab Order. This forces the user to take some action to commit the record. 

The next step would then be to provide some action. In the Form Design you set it so that the Close Button is not shown, forcing the User to use a Close or Save button that you supply.  You can also not show the Navigation bar and supply your own "VCR" buttons to control movement between records.

Answer 3

Hi Robertice,

In your table design you can make all of the fields required. This won't allow saving a record without all of the fields having a value. If a user attempts to close a form with incomplete data they will get an error message. You can control the error message via VBA code but this might be a bit beyond a "completely new" developer.

Let us know if you have more questions.