fHighlightRequiredControls()...

While I use fValidateData() to stop Users from going to the another record without filling out the required controls. it occurred to me would be nice to let them know what those required controls are beforehand.  Allen Browne has one but it highlights based on Required being set at the Table level.  With this Function you can highlight Controls at the Form level.  In this example I use yellow to identify which controls are required, however, you can change to any color you like by modifying the Function below.
The difficult I do immediately, the impossible takes a little bit longer.
Public Function fHighlightRequiredControls()
On Error GoTo Err_Handler
'From https://www.access-diva.com/vba23.html
 
    Dim ctl As Control
    Dim frm As Form
 
    Set frm = Screen.ActiveForm
 
    For Each ctl In frm.Controls
        If ctl.Tag <> "" Then
          If ctl.Enabled Then
            If InStr(1, ctl.Tag, "require") Then
              If Nz(ctl, "") = "" Then
 
                    Select Case ctl.ControlType
                        Case acTextBox
                            ctl.BackColor = RGB(254, 242, 154)  'Yellow
                        Case acComboBox
                            ctl.BackColor = RGB(254, 242, 154)  'Yellow
                        Case acListBox
                            ctl.BackColor = RGB(254, 242, 154)  'Yellow
                    End Select
 
              End If
            End If
          End If
        End If
    Next
 
    fHighlightRequiredControls = True
 
Exit_Handler:
    Set ctl = Nothing
    Exit Function
 
Err_Handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "fHighlightRequiredControls()"
    Resume Exit_Handler
End Function
VBA
Tips (Main)
Home
Creating a Multi-Value field using Alphabet
Copy Fields Down from above Record
Loop thru records and OutPutTo seperate .RTF or .PDF
Modified Spell Check
Code Snippets
Lock\Unlock Bound Controls
Loop while renumbering two columns
Create a Table with Dynamic Field Names
Snippets for Exporting to Excel
Log Field Changes
Log Record Deletions
Check for Duplicate Values
ValidateData()
ClearClipboard()
Selecting an Excel Worksheet from Access
Send eMail to Multiple Recipients
Cancel Save in a Bound Form
Automatically Send eMail Notifications
fFindBookmark()
Looping Records to Send eMail
fxlFindReplace()
fMouseOverCurrent()
Check if Table Exists
fAmortization()
Insert (or Remove) Blank Line
You can download the sample here or copy the Function below into a Module and use by placing...
DoCmd.SelectObject acForm, Me.Name
Call fHighlightRequiredControls
DoEvents
...in the On_Current of your Form.  Then just put *require*, no asterisks, on the Tag line of the Controls you want to required.
To let your End User set the required controls click here.
This site uses cookies to collect data on usage. By continuing to browse this site you consent to this policy. Find out more here.