CODEWITHSUNDEEP
vbams-access
xpandamonium
xpandamonium

Reputation: 13

Access Required Fields Before Exiting Form

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.

Upvotes: 1

Views: 5337

Answers (2)

Omer A.Aziz
Omer A.Aziz

Reputation: 21

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

Upvotes: 2

Kostas K.
Kostas K.

Reputation: 8518

Since you want to show consecutive pop-ups, cancel the auto-update altogether and validate on Close.

If validation is successful save the record, if not notify the user.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Cancel = True  'cancel auto-update
End Sub

'Validate and either Save or Close
Private Sub CmdCloseForm_Click()
    If Me.Dirty Then
        If IsFormValidated Then
            DoCmd.RunCommand acCmdSaveRecord
        Else
            If MsgBox("Would you like to close the form still? Changes won't be saved.", vbYesNo + vbQuestion, "Warning") = vbNo Then Exit Sub
        End If
    End If
    DoCmd.Close acForm, Me.Name, acSavePrompt
End Sub

'Validation
Private Function IsFormValidated() As Boolean

    IsFormValidated = True  'assume all is in order

    If Nz([ID], "") = "" Then
        MsgBox "The ID field is required.", vbExclamation, "Required Field"
        IsFormValidated = False
    End If

    If Nz([Staff], "") = "" Then
        MsgBox "Staff field is required.", vbExclamation, "Required Field"
        Me.[Staff].SetFocus
        IsFormValidated = False
    End If
End Function

Upvotes: 0

Related Questions