I am trying to loop through all textbox/combobox and change their border colour to red if it is empty. I have tried a previous solution to use a "Handle Focus" function, but the border colour is only changed to red when I click on the textbox/combobox.

1. How to change the border colour without the need to click on it?
2. Secondly, how do I get the label name to display in msgbox instead of the name of the control?

Previous example: https://www.access-programmers.co.uk/forums/threads/change-border-color-of-textboxes-when-they-have-focus.246363/

Public Function validateCase() As Boolean
Dim ctrl As Control

For Each ctrl In Me.Controls
    If IsNull(ctrl) Then
        If TypeOf ctrl Is TextBox Or TypeOf ctrl Is ComboBox Then
            MsgBox (ctrl.Name & " is Empty")
            ctrl.OnGotFocus = "=HandleFocus([" & ctrl.Name & "], True)"
        End If
    End If
Next ctrl    
End Function

Public Function HandleFocus(ByRef ctrl As Control, ByVal blnFocus As Boolean)
If blnFocus = True Then
    ctrl.BorderColor = RGB(255, 0, 0)
Else
    ctrl.BorderColor = RGB(0, 0, 0)
End If
End Function

CodePudding user response：

ctrl.LabelName.caption

Change LabelName to name of your label

CodePudding user response：

Short remark to your self answered question about the border: There is absolutely no reason to set the focus before setting the border color. You misunderstood the purpose of the question you referred to: The idea there was to change the border color when a control gets the focus.
Note that you should ask only one question at a time on Stackoverflow. If you need to know two things, ask two separate questions.

To find the label of a text box (or a similar control), use the controls-property of the control. The label is stored as controls(0). The following 2 functions fetches the label control for a control resp. it's caption:

Function getLabelCaption(ctrl As Control) As String
    Dim ctrlLabel As label
    Set ctrlLabel = getLabel(ctrl)
    If Not ctrlLabel Is Nothing Then getLabelCaption = ctrlLabel.Caption
End Function

Function getLabel(ctrl As Control) As label
    On Error Resume Next
    If TypeName(ctrl.Controls(0)) = "Label" Then
        Set getLabel = ctrl.Controls(0)
    End If
    On Error GoTo 0
End Function

In your code, you could use something like

Dim caption as String
caption = getLabelCaption(ctrl)
MsgBox Iif(caption = "", ctrl.name, caption) & " is Empty")

CodePudding user response：

I have a partial answer.

I have solved the first question by adding a line ctrl.SetFocus right after the ctrl.OnGotFocus.

I don't have a solution for the second question.