Verifying Required Fields (1 Viewer)

[sparlaman]

Scenario: I am using the code below to verify that particular fields in my form are completed. This works fine but I want more. I have 2.5 questions about this.

If IsNull(Field1) Or IsNull(Field2) Or IsNull(Field3) Or IsNull(Field4) Then
MsgBox "All required fields not completed.", vbInformation, "Required Information"
Else
DoCmd.Close

Questions:
Q1: I would like to list which fields are incomplete. How do I do that (the simplest way possible)? I have reviewed many of the posts here and found 2 diff solutions one which makes sense but doesn’t seem to work and the other is so foreign to me that I can’t modify it if needed. More details regarding the 2 posted solutions below.

Q2: One of my fields is a multi select list box, which apparently won’t work with the “IsNull” parameter. How do I verify that at least one of the items in the list box is selected?

Q2.5: How can I accomplish all of this checking and messaging in one string of code?

All help is greatly appreciated. Thanks in advance.
Shel

Posted Solutions and Why I can’t seem to use them:
Solution1: (This is very straight forward I understand what it’s supposed to do but I get the following error; Run-time error ‘438’: Object doesn’t support this property or method)

Dim ctl as Control
Dim strMissedControls as String

For Each ctl in Me.Controls
If ctl.Value="" Then
strMissedControls=strMissedControls & vbCrLf & ctl.Name
End If
Next ctl

If strMissedControls <> "" Then
strMissedControls= "You have not completed the following fields:" & strMissedControls
Cancel=True
End If


Solution2: (Difficult to understand, wouldn’t know how to modify it if needed, and I don’t know how to apply it)
Public Function fnValidateForm(frmA As Form) As Boolean
Dim ctl As Control
fnValidateForm = True
For Each ctl In frmA.Controls
'value in the control is required
If InStr(1, ctl.Tag, "Required") > 0 Then
' no value entered or value is null
' or zero for numeric fields
If (IsNull(ctl.Value)) Or (Len(ctl.Value) = 0) Then
ctl.SetFocus
MsgBox "Value required"
fnValidateForm = False
Exit For
End If
If InStr(1, ctl.Tag, "NumberRequired") > 0 Then
If ctl.Value = 0 Then
ctl.SetFocus
MsgBox "Value required"
fnValidateForm = False
Exit For
End If
End If
End If
Next
End Function
To use set the tag line for each control you require to AlphaRequired or NumberRequired.
HTH

 

[Travis]

1. Place this code in a Public Module
2. Add the word "Required" (or "NumberRequired" for number fields) to each control on your form that you want to have validated.
3. From an event (Button Click, OnSave etc) call the fnValidateForm by passing the form object. Example fnValidateForm(frmMyForm)

Public Function fnValidateForm(frmA As Form) As Boolean
'This works on each control in a form that has
'its TAG property contains the word "Required".

Dim ctl As Control

'Default to Successful Validataion
fnValidateForm = True

For Each ctl In frmA.Controls
    'value in the control is required
    If InStr(1, ctl.Tag, "Required") = 0 Then
            Select Case ctl.ControlType
                Case acTextBox
                    'Checks TextBox controls
                    
                    ' no value entered or value is null
                    ' or zero for numeric fields
                    If InStr(1, ctl.Tag, "NumberRequired") = 0 Then
                        'Numeric Check
                        If ctl.Value = 0 Then
                            ctl.SetFocus
                            MsgBox "Value required"
                            fnValidateForm = False
                            Exit For
                        End If
                    Else
                        'Alpha Check
                        If (IsNull(ctl.Value)) Or (Len(ctl.Value) = 0) Then
                            ctl.SetFocus
                            MsgBox "Value required"
                            fnValidateForm = False
                            Exit For
                        End If
                    End If
                Case acCheckBox
                    'Checks CheckBox controls
                    If (IsNull(ctl.Value)) Then
                        ctl.SetFocus
                        MsgBox "Value required"
                        fnValidateForm = False
                        Exit For
                    End If
                Case acListBox
                    'Checks for selections on ListBox controls
                    If ctl.ItemsSelected.Count = 0 Then
                        ctl.SetFocus
                        MsgBox "Value required"
                        fnValidateForm = False
                        Exit For
                    End If
                Case Else
                    'Not one of the three controls
            End Select
    End If
Next
End Function

 

[ghudson]

Travis, that is one of the best options to use for validations but I do not think that sparlaman understands what to do or how to use it. His question 2) has a lesser attempt at the same thing and he questions what to do with it.