Perform data entry validation on textbox and combobox

[ezybusy]

Hi everyone,


i am trying to perform data entry validation on my forms for textboxes and comboboxes. The logic and code i used was found on iBasskung's youtube chanel.


CREDIT: Original code Video link: https://www.youtube.com/watch?v=dq3myEN4Vps

So here are my steps:

1 - I placed the following code placed in a module called Mod_IsNullOrEmpty

Option Compare Database
Option Explicit

Public Function IsNullOrEmpty(Tbx As TextBox) As Boolean
If IsNull(Tbx.Value) Or _
    Len(Nz(Tbx.Value, vbNullString)) = 0 Or _
    Len(Tbx.Value & vbNullString) = 0 Then
    IsNullOrEmpty = True
Else
    IsNullOrEmpty = False
End If
End Function

2 - In the BeforeUpdate event of the form onto which i want to perform the validation, i use the following code:

Option Compare Database
Option Explicit
Private str As String
Dim ctrl As Control

Private Sub Form_BeforeUpdate(Cancel As Integer)

str = Empty

For Each ctrl In Me.Controls
    If TypeOf ctrl Is TextBox And ctrl.Tag <> "" Then
        If IsNullOrEmpty(ctrl) Then
            ctrl.BackColor = vbRed
            ctrl.ForeColor = vbWhite
            str = str & "- " & ctrl.Tag & vbNewLine
            Cancel = True
        Else
            ctrl.BackColor = vbWhite
            ctrl.ForeColor = vbBlack
        End If
    End If
Next ctrl

If IsNull(str) Or str = "" Then
    Exit Sub
Else
    MsgBox "The following field(s) should not be empty:" & vbNewLine & _
    String(52, "_") & vbCrLf & vbCrLf & str, vbExclamation, "Err: Rquired Field(s)"
    Exit Sub
End If
End Sub

My Problem: What i am looking to achieve is to make the code work for Comboboxes. I have a couple of combo boxes on most of my forms and would like the validation to work on them.

At the moment everything work as expected, but only for textboxes. So i want to extend the functionality to comboboxes.

Thank you.

[June7]

Include control type Combobox in the If condition with OR operator.

If TypeOf ctrl Is TextBox Or TypeOf ctrl Is ComboBox Then

Is form in Continuous or Datasheet view? Setting these properties will show the same for all records. Use Conditional Formatting if you want the setting to be dynamic per record.

I wouldn't bother with a custom function just to test if control is Null or empty string.

If ctrl & "" = "" Then

[Micron]

Not hard and can be simpler than what you have. But first, are you using the control tag in both cases, and how?
EDIT
are your control labels associated (attached) to these controls, or have you separated them? If you use a naming convention for controls, those names are usually not of much help to a user.

[ezybusy]

Hi June 7, thx for the reply
i tried the following:

For Each ctrl In Me.Controls
    If TypeOf ctrl Is TextBox Or acComboBox And ctrl.Tag <> "" Then
        If IsNullOrEmpty(ctrl) Then

But it gives me an error with the third line highighted in yellow

[ezybusy]

Hi Micron, can you kindly help out with the more simpler approach you are referring to?

[Micron]

I would not put Cancel = True inside of a loop, nor would I alter control colours as you can end up getting stuck with them. Plus, you cannot do it this way for continuous forms, which makes the code only useful for single form view. If you want the null/empty string check in a separate function or sub, it can still be done in one line as shown below. If you plan to do this a lot, consider passing the form to a public function in a standard module. That way you can use the same code for any form. This is air code (from the empty space between my ears) thus this example is untested.

You didn't answer the question re tags or attached controls, so this assumes the tag is not important (since you only seem to check tags that = ""). Also assumes the label is attached. If not, it will error. Thus, I didn't go to the trouble of writing an error handler.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctrl As Control, strList As String

For each ctrl In Me.Controls
  If ctl.ControlType = acTextbox Or ctl.ControlType = acComboBox Then
    If Nz(ctl,"") = "" Then strList = strList & ctl.Controls(0).Caption & vbCrLf '<< caption was incorrectly "name"
  End If
Next

If strList <> "" Then
  strList = "The following fields are required:" & vbCrLf & strList
  Msgbox strList
  Cancel = True
End If

[ezybusy]

Thank you Micron.

Are you using the control tag in both cases, and how?

- Do you mean for Textbox and Combobox? YES i want to use control tag in both cases.

Here is the logic i want to implement:
- Vba code checks if control (textbox or combobox) is null (has no value), if yes then code checks if control tag is not null (textbox or combobox).
- When both conditions are met, then it means the concerned field are required, so stop there and tell the user to input something in these required fields.

it actually sounds clear in my mind, but i am unable to convert it into code and make it work.

Are your control labels associated (attached) to these controls

- YES the labels are associated to the controls.

In the meantime, i will test your proposed code block and post back.

[ezybusy]

The following code works but it checks for all fields.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctrl As Control, strList As String


For each ctrl In Me.Controls
  If ctrl.ControlType = acTextbox Or ctrl.ControlType = acComboBox Then
    If Nz(ctrl,"") = "" Then strList = strList & ctrl.Controls(0).Caption & vbCrLf '<< caption was incorrectly "name"
  End If
Next


If strList <> "" Then
  strList = "The following fields are required:" & vbCrLf & strList
  Msgbox strList
  Cancel = True
End If
end sub

Since i only want to check some fields, and use control tag instead of control label, i modified the above code a bit to have the following which works as expected:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As Control, strList As String


For Each ctl In Me.Controls
  If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
    If Nz(ctl, "") = "" And ctl.Tag <> "" Then
        strList = strList & ctl.Tag & vbCrLf
    End If
  End If
Next


If strList <> "" Then
  strList = "The following fields are required:" & vbCrLf & strList
  MsgBox strList
  Cancel = True
End If
end sub

Thank you for you time Micron. Thank you too June 7.

[Micron]

You're welcome. One day I will have to check if
If ctl.ControlType = (acTextBox OR acComboBox)
works. Probably not.