Sep 18 2020 05:29 PM
Sep 18 2020 05:29 PM
I have built a subject form and would like to find a way to highlight a textbox control that is required (it is paired to a required field) IF AND ONLY IF the user fails to enter it. I know that there are validation rules that are built as part of the subject table that will give the user an indication of an error when they fail to enter required data. But, I am looking for a more "colorful" or "visual" indication, so to speak. I have seen people simply give a colorful border to a required field. This is fine, but I am trying to find a way to either highlight or make a colorful border on the required control IF AND ONLY IF an error is thrown.
I am using ACCESS 2016
Sep 19 2020 03:54 AM
You can do this with the following setup and VBA-code. I assume you have some basic VBA knowledge. Otherwise, let me know and I will elaborate on this.
Furthermore, we focus on text and combo boxes while these field have a background color that we can set (highlight).
First, we address the required form fields by using the Tag-property. In the example below I mark the Description-field as required by setting the Tag-property in the Other-tab to the value “required”. The Tag-property can hold any text we want, so we use it here to our convenience.
I also marked the Unit-combo box as required.
Next, we build a VBA-function that checks the required fields of a form. You can place this function in the code section of the concerning form, but it’s more suitable to place it in a module because this makes it possible to call the function from any form you want.
If necessary, add a module (Insert > Module from the VBA-editor) and place this function in it:
Function ValidateRequired(frm As Form, sFormName As String) As Boolean 'Check if the required fields of a form are entered 'Declare Dim ctl As Control, ctlFirst As Control, iCounter As Integer 'Init ValidateRequired = True iCounter = 0 'Loop through the form controls For Each ctl In frm.Controls 'Check for required tag If ctl.Tag = "required" Then 'Is the control empty? If IsNull(ctl) Then 'Only set the background for text and combo boxes Select Case ctl.ControlType Case acTextBox, acComboBox ctl.BackColor = 10092543 iCounter = iCounter + 1 'count the number of required fields that are empty End Select 'Set the outcome of this function to false, so the form update can be cancelled ValidateRequired = False 'Keep the first control that's highlighted for setting focus on this control later on If ctlFirst Is Nothing Then Set ctlFirst = ctl Else '(Re)set the background of a required field that's filled Select Case ctl.ControlType Case acTextBox, acComboBox ctl.BackColor = vbWhite End Select End If End If Next 'Set focus to the first highlighted field, if any, and signal an info message If ValidateRequired = False Then On Error Resume Next ctlFirst.SetFocus On Error GoTo 0 Select Case iCounter Case 1 MsgBox "The highlighted field is required.", vbInformation, sFormName Case Is > 1 MsgBox "The highlighted fields are required.", vbInformation, sFormName End Select End If End Function
This function takes the form(object) as input and loops over all the form controls, including the text and combo boxes. If a control has a Tag-property with the value “required” and a type of text or combo box, the function check if it is filled.
If not, the background of the concerning field is set to a yellow color. If a required field is filled, the background is set to white. Furthermore, the function signals a message when one or more required fields are empty.
You can change the highlight-color and the message as you like. Or even comment out the message if you don’t want it displayed.
Last step is calling the validation-function from the form. In my example I call it from the Click-event of the Save-command button:
Private Sub cmdSave_Click() 'Validate required fields If Not ValidateRequired(Me, "Product") Then Exit Sub 'Save the record If Me.Dirty Then Me.Dirty = False 'Save 'Close form DoCmd.Close acForm, Me.Name End Sub
The ValidateRequired-function takes two parameters: the form object and the name of the form as you want it displayed in the error-message. Here, we refer with the “Me” keyword to the form object. We call the function from an If-statement, so in case of an error, we exit the subroutine without saving and the form stays on the screen.
Now when you open the form in View mode and click Save without entering any data, you will get this screen:
The required fields are highlighted and the focus is set on the first missing field. After filling the required fields and clicking Save again, the record is entered in Access and the form is closed.
Hope this works for you.