Un-highlighting Required Fields

[MHamid]

Hello,

I am trying a different approach to handle required fields. I'm testing another code and where it highlights required fields works great. However, I need it to un-highlight after the required field has been filled. Below is the code that handles the required fields. Any suggestions?

Public Function SetupRequiredFields(frm As Form)
On Error GoTo Err_Handler

    Dim rs As DAO.Recordset     'Recordset of the form.
    Dim ctl As Access.Control   'Each control on the form.
    Dim strField As String      'Name of the field a control is bound to.
    
    Set rs = frm.Recordset
    For Each ctl In frm.Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox
            'Ignore unbound, or bound to an expression.
            strField = ctl.ControlSource
            If (strField <> vbNullString) And Not (strField Like "=*") Then
                With rs(strField)
                    If (.Required) Or (.ValidationRule Like "*Is Not Null*") Then
                        ctl.BackColor = mlngcRequiredBackColor
                        Call MarkAttachedLabel(ctl)
                    End If
                End With
            End If
        End Select
    Next
    SetupRequiredFields = True
    
Exit_Handler:
    Set ctl = Nothing
    Set rs = Nothing
    Exit Function
Err_Handler:
    MsgBox "Error " & err.Number & ": " & err.Description, vbExclamation, "SetupRequiredFields()"
    Resume Exit_Handler
End Function

Below is the full code that has been placed in a Module

Option Compare Database
Option Explicit

'The RGB value to use as a control's Back Color when it has focus.
Private Const mlngcFocusBackColor = &HB0FFFF
'The RGB value to use as Back Color if a control is bound to a required field.
Private Const mlngcRequiredBackColor = &HD0D0FF
'These constants are for assigning/reading the Tag property.
Private Const mstrcTagBackColor = "UsualBackColor"
Private Const mstrcTagSeparator = ";"
Private Const mstrcTagAssignmnent = "="

Public Function SetupForm(frm As Form, Optional iSetupWhat As Integer = &H7FFF)
    Const iSetupRequired = 1
    Const iSetupFocusColor = 2
    
    If (iSetupWhat And iSetupRequired) Then Call SetupRequiredFields(frm)
    If (iSetupWhat And iSetupFocusColor) Then Call SetupFocusColor(frm)
End Function

Public Function SetupFocusColor(frm As Form)
On Error GoTo Err_Handler

    Dim ctl As Access.Control   'Each control on the form.
    
    For Each ctl In frm.Controls
        With ctl
            Select Case .ControlType
            Case acTextBox, acComboBox, acListBox
                If (.OnGotFocus = vbNullString) And (.OnLostFocus = vbNullString) Then
                    .OnGotFocus = "=Hilight([" & .Name & "], True)"
                    .OnLostFocus = "=Hilight([" & .Name & "], False)"
                    .Tag = .Tag & IIf(.Tag <> vbNullString, mstrcTagSeparator, Null) & _
                        mstrcTagBackColor & mstrcTagAssignmnent & .BackColor
                End If
            End Select
        End With
    Next
    SetupFocusColor = True
    
Exit_Handler:
    Set ctl = Nothing
    Exit Function
Err_Handler:
    MsgBox "Error " & err.Number & ": " & err.Description, vbExclamation, "SetupFocusColor()"
    Resume Exit_Handler
End Function

Public Function SetupRequiredFields(frm As Form)
On Error GoTo Err_Handler

    Dim rs As DAO.Recordset     'Recordset of the form.
    Dim ctl As Access.Control   'Each control on the form.
    Dim strField As String      'Name of the field a control is bound to.
    
    Set rs = frm.Recordset
    For Each ctl In frm.Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox
            'Ignore unbound, or bound to an expression.
            strField = ctl.ControlSource
            If (strField <> vbNullString) And Not (strField Like "=*") Then
                With rs(strField)
                    If (.Required) Or (.ValidationRule Like "*Is Not Null*") Then
                        ctl.BackColor = mlngcRequiredBackColor
                        Call MarkAttachedLabel(ctl)
                    End If
                End With
            End If
        End Select
    Next
    SetupRequiredFields = True
    
Exit_Handler:
    Set ctl = Nothing
    Set rs = Nothing
    Exit Function
Err_Handler:
    MsgBox "Error " & err.Number & ": " & err.Description, vbExclamation, "SetupRequiredFields()"
    Resume Exit_Handler
End Function

Public Function Hilight(ctl As Access.Control, bOn As Boolean)

    '           bOn = flag: True if receiving focus, False if losing focus.
    Dim strBackColor As String
    
    If bOn Then
        'Assign the 'got focus' color.
        ctl.BackColor = mlngcFocusBackColor
    Else
        'Restore the color from the control's Tag property (white if not found.)
        strBackColor = ReadFromTag(ctl, mstrcTagBackColor)
        If IsNumeric(strBackColor) Then
            ctl.BackColor = Val(strBackColor)
        Else
            ctl.BackColor = vbWhite
        End If
    End If
End Function

Private Function MarkAttachedLabel(ctl As Access.Control)
On Error GoTo Err_Handler
    
    With ctl.Controls(0)
        If Not .Caption Like "*
[*]" Then
            .Caption = .Caption & "*"
            .FontBold = True
            .ForeColor = vbRed
        End If
    End With
Exit_Handler:
    Exit Function
Err_Handler:
    Resume Exit_Handler
End Function

Private Function ReadFromTag(ctl As Control, strName As String) As String

    Dim varArray As Variant
    Dim strValue As String
    Dim i As Long
    
    If ctl.Tag <> vbNullString Then
        varArray = Split(ctl.Tag, mstrcTagSeparator)
        If IsArray(varArray) Then
            For i = LBound(varArray) To UBound(varArray)
                If varArray(i) Like strName & mstrcTagAssignmnent & "*" Then
                    ReadFromTag = Mid(varArray(i), Len(strName) + Len(mstrcTagAssignmnent) + 1&)
                End If
            Next
        End If
    End If
End Function

The code is being called in the Form_Load event.
FYI - this is someone else's code I found online and wanted to try out.

Thank you

 

[ranman256]

In the control AFTERUPDATE event
if Not isnull(txtbox) then txtbox.backcolor = vbWhite

 

[MHamid]

Hello,

So you're suggesting something like this in the AfterUpdate event of each control.

If Not IsNull(Me.CycleMonth) Then Me.CycleMonth.BackColor = vbWhite

I just tried that and it did nothing. Still left it as being required.

 

[xenou]

What events are you handling in order to make the highlighting and unhighlighting work. So far I see code but you aren't showing when you use the code.

Although for what it's worth the above suggestion seems correct - right after the required field is filled in you should be "un-highlighting" it.

 

[MHamid]

I was just about to post the event where the code is being called. The code is being called in the Form_Load event, which is why I think the code above did nothing ... but I could be wrong.

 

[xenou]

Form_Load could highlight required fields for you. It wouldn't unhighlight anything when they are filled in though.

 

[MHamid]

Is there any way I could work around this?

 

[xenou]

So you're suggesting something like this in the AfterUpdate event of each control.

Code:

If Not IsNull(Me.CycleMonth) Then Me.CycleMonth.BackColor = vbWhite

I just tried that and it did nothing. Still left it as being required.

What did you actually do when you tried the above. All I see is a line of code, which doesn't really indicate enough information about the issue. In general, that is on the right track but I can't tell what you actually did with this line of code to use it or test it.

Note that unhighlighting code would probably go in the after_update events of the controls, not the form. Which is what Ranman suggested to you.

 

[MHamid]

Hello,

I tested that one line of coding in the control's after_updated event. As far as I noticed in ranman's post, that was the only line of coding needed.

Private Sub CycleMonth_AfterUpdate()
If Not IsNull(Me.CycleMonth) Then Me.CycleMonth.BackColor = vbWhite
End Sub

Would there possibly be a way to disable the highlight code I have in my original post that is being called in the Form Load event?
Or is there a way that I can use this code in another Form event?

 

[xenou]

What are you updating CycleMonth to - is it a non null value? What value is it?

also you should test if the event is firing as you expect:

Private Sub CycleMonth_AfterUpdate()
[B]msgbox "CycleMonth_AfterUpdate Event Fired!"
[/B]If Not IsNull(Me.CycleMonth) Then Me.CycleMonth.BackColor = vbWhite
End Sub

I'm assuming that the first part works (highlighting the required fields). and that only the second part doesn't work (unhighlighting filled in fields).

Also it goes without saying but comment out the ON ERROR GOTO ERRHandler lines while you are testing. You will get debugging done faster if you fall into break mode on the actual lines that are erroring, exactly when and where they error.