Form Data Validation

If you ever need to make sure the user entered valid information on the form before committing the data into the table, then it is considered "best practice" to perform data validation routines using the Form's BeforeUpdate event. In addition, it is common practice to use the Tag property of form controls to indicate which data needs to be validated.

For example, let us say you have a form for a loan application and a complete section must be filled out for a co-borrower information when the customer is applying for a joint loan. Using the Tag property approach, you can enter "co-borrower" in the Tag property of each Textbox representing co-borrower information and then use the following code in the Form's BeforeUpdate event:

If Me.LoanType = "joint" Then
   For Each ctl In Me.Controls
      If ctl.Tag = "co-borrower" Then
         If IsNull(ctl.Value) Then
            Cancel = True
            MsgBox "Please complete co-borrower information.", vbInformation, "Required"
            Exit For
         End If
      End If
   Next
End If

As already mentioned, this approach is common practice and usually applies to probably 99% of situations for data validations. However, someone approached me recently and said this approach of using the Tag property and looping through all the form controls made his form ran very slowly. It turns out his form was very busy with multiple subforms and a lot of unbound controls updated using code to reflect specific information about the current record from other tables in the database.

So, in this case, we want to avoid looping through all form controls, which is a requirement when using the Tag property approach. To do so, we'll need to know the names of the controls to evaluate, so we can simply validate them. One of the main advantages of using the Tag property is it is very easy to add or remove controls to validate without needing to modify the code. If we hard code the names of the controls to validate, we'll need to modify the code each time our requirements change.

As an alternative, we need to somehow store the names of the controls to validate and then go through each of them to check the form's data. There are several options to accomplish this task. For example, we can use a hidden unbound Textbox to store a comma-delimited value of control names to validate, We can then use the Split() function to store the control names in an array for validation.

The approach I will demonstrate will use a hidden unbound Listbox to store the control names. I like using a Listbox because it is very easy to loop through each item in a Listbox when performing our data validation. For this approach, I recommend using a Value List for a Row Source, although using a table will work just fine as well. Assuming the Bound Column of the Listbox is set to the column with the names of the control, here's a modified version of the above code using a Listbox rather than the Tag property:

If Me.LoanType = "joint" Then
   For lngItem = 0 To Me.ListboxName.ListCount - 1
      If IsNull(Me.Controls(Me.ListboxName.ItemData(lngItem))) Then
         Cancel = True
         MsgBox "Please complete co-borrower information.", vbInformation, "Required"
         Exit For
      End If
   Next
End If

As you can see, this alternative approach accomplishes the same task as using the Tag property without affecting the speed of a very busy form. Adding or removing controls to validate is also as simple as updating the Listbox without needing to alter the code. This approach can even be expanded by storing all control names in a table along with the name of the form or forms to which the control validation applies. Doing so will allow the same code to validate multiple forms.