• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 352
  • Last Modified:

VBA Checkbox Field Validation

I have a form with 3 text boxes fields, 1 combo box field and 8 check boxes fields. The text boxes, the combo box and 6 check boxes have a tag name "Req". After the last checkbox, the user press a button to save the record and close the form. At this point, I want to validate if all fields with the "Req" tag are filled or checked - if not, will prompt a message that there's a field that's missing.

I have this code running, but it only works for the text boxes and the combo box. What I'm missing?
Private Sub abtnCommand1_Click()
Dim ctl As Control
 
 
For Each ctl In Me.Controls
    If ctl.Tag = "Req" And IsNull(ctl) Then
    MsgBox "Required fields missing. Fill or check missing fields", vbCritical, "Missing fields"
    ctl.BackColor = vbYellow
    Else
    
    End If
    
    Next
    
    Exit Sub
 
End Sub

Open in new window

0
richieac77
Asked:
richieac77
  • 2
1 Solution
 
omgangIT ManagerCommented:
Checkbox controls are either True or False, Yes or No, On or Off, -1 or 0.  You need to test for one of those values.

OM Gang
0
 
omgangIT ManagerCommented:
OM Gang
Private Sub abtnCommand1_Click()
 
Dim ctl As Control
 
 
For Each ctl In Me.Controls
    If ctl.Tag = "Req" Then
        If ctl.ControlType = acCheckBox Then
            If IsNull(ctl.Value) Or ctl.Value = 0 Then
                MsgBox "Required fields missing. Fill or check missing fields", vbCritical, "Missing fields"
                ctl.SetFocus
                Exit For
            End If
        Else
            If IsNull(ctl) Then
                MsgBox "Required fields missing. Fill or check missing fields", vbCritical, "Missing fields"
                ctl.BackColor = vbYellow
                Exit For
            End If
        End If
    End If
    
Next
 
Exit Sub
        
 
 
End Sub

Open in new window

0
 
richieac77Author Commented:
Thanks it worked like charm.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now