Access Required Fields Before Exiting Form

Question

I have this BeforeUpdate code to check if certain fields are filled out. These fields are required and must be filled or the record shouldn't save. If other fields have been filled without the ID and Staff field, then the message box prompts pop up (These are required fields, etc.).

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Nz([ID], "") = "" Then
    MsgBox "The ID field is required.", vbExclamation, "Required Field"
    Cancel = True
End If
If Nz([Staff], "") = "" Then
    MsgBox "Staff field is required.", vbExclamation, "Required Field"
    Cancel = True
    Me.[Staff].SetFocus
End Sub

I have a 'Close Form' button as follows:

Private Sub CmdCloseForm_Click()
    DoCmd.Close , ""
End Sub

When this button is clicked, I get the warning that the fields aren't filled, but then the form closes. I want a Yes/No message box asking the user to see if they would still like to close the form or not. I've made a Yes/No messagebox in the BeforeUpdate sub. However it doesn't stop the sub CmdCloseForm.

Is there a way to create a messagebox to confirm if the user wants to exit the form?

For example:

If MsgBox("Would you like to close the form still? Changes won't be saved.", vbYesNo + vbQuestion, "Warning") = vbNo Then
    Exit Sub
End If

If I put the above Msgbox into the CmdCloseForm function, it prompts the user before letting them know that they're missing the ID/Staff fields.

Maybe you should do the data validation in the button click event. — June7

Omer A.Aziz Omer A.Aziz · Accepted Answer · 2020-12-04T20:53:10

I came about this solution by Mr. Craig Dolphin and it worked for me. The way he approached is to have a generic validation function that is saved in a general purpose module (ie not in a form module).

Public Function validateform(myform As Form) As Boolean
'returns true if all required fields have data, or false if not. 
'It will also create a popup message explaining which fields need data

Dim boolresponse As Boolean
Dim strError As Variant
Dim ctl As Control
boolresponse = True
strError = Null
With myform
For Each ctl In .Controls
  With ctl
    If .Tag = "required" Then
       If .Value & "" = "" Then
            boolresponse = False
            strError = (strError + ", ") & .Name
        End If
    End If
  End With
Next ctl
End With
If strError & "" <> "" Then MsgBox "The following information must be entered first: " 
& strError, vbInformation
validateform = boolresponse
End Function

Then, for any fields that are absolutely required, you just set the Tag property of the control to 'required'

You can then, for example, call the function from the onclick event of the 'save' button or 'close' button.

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click


If Me.PrimaryID & "" <> "" Then
If validateform(Me) Then DoCmd.Close
Else
DoCmd.Close
End If

Exit_Command5_Click:
Exit Sub

Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

End Sub

this essentially checks to see if the auto pk field has a value indicating that the record has been created. If it has, then it validates the required fields. If not, it closes without checking. If the record has been created, it validates the form and only closes if all the required fields are filled in. If more data is required, then it pops up a message reminding the user which fields to fill in.

The nice part of this is that you can set conditional requirements using the vba to set the tag property of certain controls to required if another field is updated to a value that you want to trigger an additional requirement. Of course, if you do that then you also need to initialize those tag value in the on_current event for when you switch records. Many thanks to Mr. Craig Dolphin

Access Required Fields Before Exiting Form

2 Answers