Need help with VBA to check for required fields

[vanzie]

Hi everyone

Just need some help quickly with the following code:

Private Sub btnSubmit_Click()
    Dim ctrl As Control
    Dim missingFields As String
    Dim isValid As Boolean
    
    ' Initialize variables
    missingFields = ""
    isValid = True
    
    ' Loop through all the controls on the form
    For Each ctrl In Me.Controls
        ' Check if the control is a TextBox or ComboBox
        If TypeOf ctrl Is TextBox Or TypeOf ctrl Is ComboBox Then
            ' Check if the control is tagged as required and is empty
            If ctrl.Tag = "Required" And Trim(ctrl.Value) = "" Then
                missingFields = missingFields & ctrl.Name & vbNewLine
                isValid = False
            End If
        End If
    Next ctrl
    
    ' If there are missing fields, alert the user
    If Not isValid Then
        MsgBox "The following fields are required and must be filled out:" & vbNewLine & missingFields, vbExclamation, "Missing Fields"
    Else
        ' Proceed with submission logic
        MsgBox "All required fields are filled!", vbInformation, "Success"
    End If
End Sub

The specified controls have "Required" tag but when I leave it empty and click submit, it follows through to the last message box saying all required fields are filled.

 

[CJ_London]

If you haven’t specified required in the tag property, isValid is never set to false. So not sure what your issue is. The implication of an empty tag field is not required

Edit - think you mean the ctrl value is empty. If the control datatype is numeric then it will be null rather than a zls.

Suggest try

Trim(nz(ctrl, ""))=""

 

[vanzie]

If you haven’t specified required in the tag property, isValid is never set to false. So not sure what your issue is. The implication of an empty tag field is not required

Edit - think you mean the ctrl value is empty. If the control datatype is numeric then it will be null rather than a zls.

Suggest try

Trim(nz(ctrl, ""))=""

The tag of the fields I want to trigger is set to "Required". Some of the fields are mixed between text and date fields. I've used your suggested code and the result still produced the last message box of the code instead of telling me the required fields are left empty.

 

[CJ_London]

between text and date fields

be aware dates are numeric fields, what you see is just a format based on your windows settings which can be overridden by your own format property

step through your code and check the values - put a stop on this line

If ctrl.Tag = "Required" And Trim(nz(ctrl,"")) = "" Then

and hover over 'ctrl.Tag' and 'ctrl'. Perhaps use debug.print to print the information

 

[vanzie]

be aware dates are numeric fields, what you see is just a format based on your windows settings which can be overridden by your own format property

step through your code and check the values - put a stop on this line

If ctrl.Tag = "Required" And Trim(nz(ctrl,"")) = "" Then

and hover over 'ctrl.Tag' and 'ctrl'. Perhaps use debug.print to print the information

I'll find a way around but thank you for your input. Much appreciated.

 

[MajP]

The check you want for a control to ensure you catch all possibilities
1. Null
2. Empty string
3. Spaces (rare but possible if importing especially from excel)
is

if trim(ctrl.value & "") = ""

1. Null & "" = ""
2. "" & "" = ""
3. trim (space & "") = ""

 

[MajP]

FYI, the reason your code failed is because Trim(null) = Null not ""

ctrl.Tag = "Required" And Trim(ctrl.Value) = ""

NULL <> "" therefore not true
However
Trim(null & "") = ""

I am actually surprised it returns null not "". VBA often automatically figures out what you are trying to do and will Cast variants to proper subtype.