Required fields in a table, Message to add value

[Khalil Handal]

Hi to all,


I started a database table tblPersonnel with BirthDate field. This field is required.

I created a form frmPersonnel, the error message shown when birth date field is null was: You must enter a value in the 'tblPersonnel.BirthDate' field

How to do it on the form level for the txtBirthDate? How can we deal with the case of four required fields?


Khalil

[Welshgasman]

Remove the validation from the table.
Validate in the form instead. Most people use the tag property of the control to indicate required controls for validation.
Try using google, plenty of examples out there.

[mike60smart]

Hi Khalil

This is the best method as suggested by Welshgasman:-

Validate Controls using Tag


In the Before Update of the Form

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
10    On Error GoTo Form_BeforeUpdate_Error
 
        Dim ctl As Control
        Dim CName As String
       
20      For Each ctl In Me.Controls
30        Select Case ctl.ControlType
          Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
40          If ctl.Tag = "Reqd" Then
50            If Nz(ctl, "") = "" Then
60              CName = ctl.Controls(0).Caption
70              MsgBox "Following field is required: " & vbCrLf & vbCrLf & CName
80              Cancel = True
90              ctl.SetFocus
100             Exit Sub
110           End If
120         End If
130       End Select
140     Next ctl
150     On Error GoTo 0
160     Exit Sub
 
Form_BeforeUpdate_Error:
170       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_BeforeUpdate, line " & Erl & "."
End Sub

[Micron]

This is the best method as suggested by Welshgasman

I'd say pretty good, but IMO, better to loop over all controls and append with line feeds to a string variable and present them all at once rather than perhaps multiple messages. The Select Case block is better than a long IF statement though. I'm going to steal that one!

What should be noted re: that code is that the labels must be attached to the controls, else it will raise an error.

[moke123]

I agree with Micron. I set the borders red and launch 1 messagebox for all. Additionally its a function so it returns true if missing fields and false if its good. Then you can just use Cancel = ValidateForm(Me, "V8").

Public Function ValidateForm(frm As Form, TagCharacter As String) As Boolean
'validated controls must have a label. Ok to use a hidden label if needed.
'Returns True if there is missing fields


    Dim ctl As Control
    Dim flg As Boolean
    Dim strOut As String


    flg = False


    For Each ctl In frm.Controls


        If ctl.Tag = TagCharacter Then
            If Nz(ctl.value, "") = "" Then
                flg = True
                ctl.BorderColor = vbRed
                strOut = strOut & Space(10) & "* " & ctl.Controls.Item(0).Caption & vbNewLine
            Else
                ctl.BorderColor = vbBlack
            End If


        End If
    Next


    If flg = True Then
        MsgBox "The following field(s) must be completed:" & vbNewLine & strOut
    End If


    ValidateForm = flg


End Function

[Minty]

Although you need to set it, I use the datasheet caption property so a label doesn't need to be present;
This also gathers the missing values into a single message box, and relies on a tag property being set. It alters the background colour to yellow.
It also resets the colours on the controls so they don't get left behind if they fail validation the second time.

Function ValidateRecs() As Boolean
    
    Dim sControls As String
    Dim ctl As Control
    Dim ctlColour As Long
    
     
    For Each ctl In Me.Controls
        If ctl.Tag = "Req" Then     'Reset the colours
            If ctl.BackColor = vbYellow Then
                ctlColour = GetHexColor("#C6D9F1") ' light blue
                ctl.BackColor = ctlColour
            End If
            If ctl.Enabled Then
                'Debug.Print ctl.Name, ctl.value, ctl.Properties("DataSheetCaption")
                If IsNull(ctl.value) Then
                    sControls = sControls & ctl.Properties("DataSheetCaption") & vbCrLf
                    ctl.BackColor = vbYellow
                End If
            End If
        End If
    Next ctl
    
    ValidateRecs = True
    bOkClose = True
    If Len(sControls & "") > 0 Then
        Box "The following fields require data before the completion date can be entered;" & vbCrLf & vbCrLf & sControls, vbInformation + vbOKOnly, "Missing Data!", , , 0, , 0, 0
        ValidateRecs = False
        bOkClose = False
    End If
    
End Function

Be aware I use a custom message box hence the slightly different layout for the message box line.