Validate a form and subforms controls

Question

Hello, I have a form [frmShiftDay] and on this form there are 3 controls [txtShiftDate], [ cboShift] and [cboSupervisorName] then on its subform I have the controls [txtMachineID] and [cboEmployeeName]. When a record is created on the form [frmShiftDay] I need to have all the mentioned controls filled in before moving to then next record on the subform.  What code and where do I need to place it that would stop the user with a message and set focus to the control that’s needing the data.

Thanks!

Answer 1

Sorry for the delayed reply; I was busy.

I would not do it the way that is suggested at that link, because I think it better not to let the user into the subform at all if the required info on the parent form is not filled out.  This problem boils down to two simple requirements:

1. Don't let a record be saved on main form or subform if required fields are not filled out.
2. Don't let the user into a subform at all unless the parent form has been filled out.

If you take care of (1), then (2) is a matter of using the Enter event of the subform control (to see if the form that contains that control is on a new record.  If it is, then warn the user and set the focus back to the appropriate control.  You don't have to check the individual controls, because requirement (1) will prevent the record from being saved, and automatically keep the focus from going to the subform if the parent form has only been partially filled out.

I have downloaded your database, and find the following modifications handle the situation.  First, put the following utility function into a standard module:

'------ start of code ------

Function fncRequiredFieldsMissing(frm As Form) As Boolean

    ' Check form (passed as <frm> for controls marked as  "Required"

    ' in their Tag properties.  If any required control is empty, return

    ' True, display an error message listing all the "missing" fields,

    ' and set the focus to the first missing control (where "first" is

    ' based on the controls' tab order).  In the list of missing controls,

    ' use each control's caption if possible, else use the name of the

    ' control after stripping off the expected object-type prefix (if

    ' present).

    '

    ' Copyright (c) 2013, Dirk Goldgar.  You may use this code freely in your

    '       applications, provided that the copyright notice remains intact.

    On Error Resume Next

    Dim ctl As Access.Control

    Dim strErrCtlName As String

    Dim strErrorMessage As String

    Dim strMsgName As String

    Dim lngErrCtlTabIndex As Long

    Dim blnNoValue As Boolean

    lngErrCtlTabIndex = 99999999  'more than max #controls

    For Each ctl In frm.Controls

        With ctl

            Select Case .ControlType

                Case acTextBox, acComboBox, acListBox, acCheckBox

                    If .Tag = "Required" Then

                        blnNoValue = False

                        If IsNull(.Value) Then

                            blnNoValue = True

                        Else

                            If .ControlType = acTextBox Then

                                If Len(.Value) = 0 Then

                                    blnNoValue = True

                                End If

                            End If

                        End If

                        If blnNoValue Then

                            strMsgName = vbNullString

                            If .Controls.Count = 1 Then

                                strMsgName = .Controls(0).Caption

                                If Right$(strMsgName, 1) = ":" Then

                                    strMsgName = Trim$(Left$(strMsgName, Len(strMsgName) - 1))

                                End If

                            End If

                            If Len(strMsgName) = 0 Then

                                strMsgName = .Name

                                Select Case Left$(strMsgName, 3)

                                    Case "txt", "cbo", "lst", "chk"

                                        strMsgName = Mid(strMsgName, 4)

                                End Select

                            End If

                            strErrorMessage = strErrorMessage & vbCr & _

                                        "   " & strMsgName

                            If .TabIndex < lngErrCtlTabIndex Then

                                strErrCtlName = .Name

                                lngErrCtlTabIndex = .TabIndex

                            End If

                        End If

                    End If

                Case Else

                    ' Ignore this control

            End Select

        End With

    Next ctl

    If Len(strErrorMessage) > 0 Then

        MsgBox "The following fields are required:" & vbCr & _

                strErrorMessage, _

                vbInformation, "Required Fields Are Missing"

        frm.Controls(strErrCtlName).SetFocus

        fncRequiredFieldsMissing = True

    Else

        fncRequiredFieldsMissing = False

    End If

End Function

'------ end of code ------

Next, on each form and subform, select the controls that are required, and set their .Tag properties (on the Other tab of the property sheet) to "Required" (without the quotes).

Next, create this BeforeUpdate event procedure in *both* frmShiftDay and frmShiftMachinesSubform:

'------ start of code ------

Private Sub Form_BeforeUpdate(Cancel As Integer)

    Cancel = fncRequiredFieldsMissing(Me)

End Sub

'------ end of code ------

Next, create this event procedure for the Enter event of the subform control. frmShiftMachinesSubform, on frmShiftDay:

'------ start of code ------

Private Sub frmShiftMachinesSubform_Enter()

    If Me.NewRecord Then

        MsgBox _

            "You must fill in the shift information before entering machine details.", _

            vbInformation, _

            "Shift Info Required"

        Me.txtShiftDate.SetFocus

    End If

End Sub

'------ end of code ------

Next, create this event procedure for the Enter event of the subform control. frmMachineOutputSubform, on frmShiftMachinesSubform:

'------ start of code ------

Private Sub frmMachineOutputSubform_Enter()

    If Me.NewRecord Then

        MsgBox _

            "You must fill in the machine information before entering product details.", _

            vbExclamation, _

            "Machine Info Required"

        Me.txtMachineID.SetFocus

    End If

End Sub

'------ end of code ------

Also, add this to the Current event procedure of frmShiftDay:

'------ start of code ------

    If Me.NewRecord Then

        Me.cboShift.SetFocus

    End If

'------ end of code ------

And add this to the Current event procedure of frmShiftMachinesSubform:

'------ start of code ------

    If Me.NewRecord Then

        Me.txtMachineID.SetFocus

    End If

'------ end of code ------

Finally, assuming this works for you -- as it works for me -- remove the code you have in the BeforeInsert events of the subforms;  you don't need it any more.

Answer 2

Dirk, I did get the below vba I found **HERE** to work on my subform but how can I name the control on my main form like I did in the main form Before Update function? Thanks

Private Sub Form_BeforeInsert(Cancel As Integer)

If Me.Parent.NewRecord Then

MsgBox "You must input information into the main form before inputting information here. Please try again.", vbInformation

Cancel = True

' Remove the following line if you DON'T want to switch focus.

Forms!frmShiftDay.cboSupervisorName.SetFocus

End If

End Sub

Answer 3

Sorry, I don't follow you.  Using the code I suggested, what happens that isn't supposed to happen, and under what exact circumstances?

The code you just posted is a more elaborate version of what I was suggesting, to validate each form in its BeforeUpdate event.  What about it?

Answer 4

Thank you for your responce. I have tried your code and it des work but if the record is already null or blank then it just closes the form and if i enter something and delete it then it will come up with the message. I have the below code I found and have used that works the same but it to brings up that message **"You cant save this record at this time"**how do I get rid of that? also if its a matter of copy and paste I would like to try an incorporate your code. Thanks!

'=============================TEST START

Private Sub Form_BeforeUpdate(Cancel As Integer)

Cancel = MyVerify

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

Cancel = MyVerify

End Sub

Private Function MyVerify() As Boolean

   Dim colFields As New Collection

   MyVerify = False

   colFields.Add "txtShiftDate|Shifts Date"

   colFields.Add "cboShift|Shift"

   colFields.Add "cboSupervisorName|Supervisor"

   MyVerify = vfields(colFields)

End Function

Private Function vfields(colFields As Collection) As Boolean

   Dim strErrorText As String

   Dim strControl As String

   Dim I As Integer

   vfields = False

   For I = 1 To colFields.Count

      strControl = Split(colFields(I), "|")(0)

      strErrorText = Split(colFields(I), "|")(1)

      If IsNull(Me(strControl)) = True Then

         MsgBox strErrorText & " is required", vbExclamation, "Required Info"

         Me(strControl).SetFocus

         vfields = True

         Exit Function

      End If

   Next I

End Function

'=============================TEST END

Answer 5

Hello, I have a form [frmShiftDay] and on this form there are 3 controls [txtShiftDate], [ cboShift] and [cboSupervisorName] then on its subform I have the controls [txtMachineID] and [cboEmployeeName]. When a record is created on the form [frmShiftDay] I need to have all the mentioned controls filled in before moving to then next record on the subform.  What code and where do I need to place it that would stop the user with a message and set focus to the control that’s needing the data.

 

This is either a complicated problem or a simple one, depending on exactly what you mean.  Access won't let the focus move to the subform until the main form's record has been saved, so you can enforce the completion of all main-form controls by using the form's BeforeUpdate event to ensure that none of those controls is Null, cancelling the event if any control is Null.  For example:

'------ start of code for main form (frmShiftDay) ------

Private Sub Form_BeforeUpdate(Cancel As Integer)

    If IsNull(Me.txtShiftDate) _

    Or IsNull(Me.cboShift) _

    Or IsNull(Me.cboSupervisorName) _

    Then

        Cancel = True

        MsgBox "Required fields are not filled in."

    End If

End Sub

'------ end of code for main form (frmShiftDay) ------

Note that the above is pretty simplistic.  In a more fleshed-out version, I would tell the user exactly which controls were empty, and set the focus to the first of them.

With that code in place, the user won't be able to go to the subform until the main form record is complete. 

Now, if you also need to keep any subform records from being created with txtMachineID and cboEmployeeName being filled in, then you can do something very similar in the form object being displayed by the subform control:

'------ start of code for subform ------

Private Sub Form_BeforeUpdate(Cancel As Integer)

    If IsNull(Me.txtMachineID) _

    Or IsNull(Me.cboEmployeeName) _

    Then

        Cancel = True

        MsgBox "Required fields are not filled in."

    End If

End Sub

'------ end of code for subform ------

The complicated part is if you want the user to be unable to create a main-form record without creating a subform record.  Essentially, you can't do that, because there's nothing to force the user to fill out a subform record.  What you can do is, in the main form's Current event, you can have code that looks to see if there is a record on that form (besides the current one) that doesn't have any "child" records in the subform's table, and if so, display a message and move back to that record.  Essentially, that will keep the user from moving to another main record without filling out a subform record.

The code for this is more elaborate, and I won't bother posting it unless you tell me you need it.