Example Apps for Businesses, Schools & Developers

Version 1.2           Approx 0.8 MB (zipped)                 First Published 3 Apr 2024


This is the fourth article in my series showing how functionality can be added to continuous forms.

It is also a companion to my earlier articles: Highlight Current Control in Single Forms and Highlight Filtered Columns in Continuous Forms

The currently selected control in a continuous form can also be highlighted using transparency.
As for single forms, first set the back colour for each control to e.g. pale yellow then change the back style to transparent.

PropertySheet
The controls will normally have a transparent background but the yellow back colour will be shown when the control gets focus i.e. when it is selected.

Highlight Selected Control
Doing this requires no code.

You can also highlight a complete column by clicking on its header label.

Highlight Selected Column
NOTE: The header label cannot get focus so the previous control remains highlighted until another control is clicked

To highlight a column, add code to the header label to change the 'related' column back style from Transparent (0) to Normal (1).
For example:

CODE:

Private Sub ClearBackStyle()

'reset back style of all controls to Tranparent
'error 438 for controls with no back style e.g. line
On Error Resume Next

For Each ctl In Me.Detail.Controls
      ctl.BackStyle = 0     'transparent
Next

End Sub

'=================================

Private Sub Forename_Label_Click()

      'reset back style of all controls to Tranparent
      ClearBackStyle

      'set back style of related control to Normal
      Me.Forename.BackStyle = 1     'Normal

End Sub


The above code requires the related control to be named specifically for each label.
A better solution uses the Accessibility function, accHitTest, to identify the related control name from the header label name, caption or position.
This allows a generic function to be used for each control.

Save the following code to a standared module e.g. modHighlight

CODE:

Option Compare Database
Option Explicit

'Uses accessibility code

Private Type POINTAPI
      x As Long
      y As Long
End Type

Private Declare PtrSafe Function GetCursorPos Lib "user32.dll" _
      (ByRef lpPoint As POINTAPI) As Long

'====================================

Sub ClearBackStyle(frm As Form)

'error 438 for controls with no back style e.g. line
On Error Resume Next

Dim ctl As Control

For Each ctl In frm.Detail.Controls
      ctl.BackStyle = 0
Next

End Sub

'====================================

Function HighlightControlFromLabel(frm As Form)

'Colin Riddington 2024-01-09
'This only works if the related field control name can be derived from the label name
'Fails with err 5 on secondary monitor with negative co-ordinates (to left of primary monitor)

On Error GoTo Err_Handler

Dim pt As POINTAPI
Dim accObject As Object
Dim strText As String

      'Get current position of mouse cursor
      GetCursorPos pt

      'Use accHitTest to get name of label control at that position
      Set accObject = frm.AccHitTest(pt.x, pt.y)

      'The following code get related control name
      'This assumes labels named using default Access method e.g. Surname_Label, Gender_Label
      strText = Left(accObject.Name, InStr(accObject.Name, "_") - 1)

      'Modify as necessary
      'e.g. if using e.g. lblSurname, lblGender etc ...
      'strText = Mid(accObject.Name, 4)

      'clear existing back styles
      ClearBackStyle frm

      'set control back style = Normal
      If Not accObject Is Nothing Then frm(strText).BackStyle = 1

Exit_Handler:
      Exit Function

Err_Handler:
      'Fails with err 5 on secondary monitor with negative co-ordinates (to left of primary monitor)
      If Err = 5 Then MsgBox "This code cannot be used on a secondary monitor to the left of a primary monitor", vbCritical, "Code failed"
      Resume Exit_Handler

End Function


Now each header label needs just one line of code. For example:

Private Sub Forename_Label_Click()
      HighlightControlFromLabel Me
End Sub



Alternatively, the function can be run from the On Click event of the property sheet for each header label

Property Sheet Function


Download

Click to download:   HighlightSelectedControlColumn_v1.2     ACCDB file     Approx 0.8 MB (zipped)


Video

A video explaining all the features and code used in this app is now available on YouTube.

You can watch the Highlight Selected Control / Column video on my Isladogs YouTube channel or you can click below:



If you liked the video, please subscribe to my Isladogs on Access channel on YouTube. Thanks.



More Continuous Form Examples

The following articles also include examples of functionality added to continuous forms:

•   Highlight Current Record
•   Highlight Selected Control or Column
•   Highlight Filtered Columns
•   Add Multiselect Filter   (2 pages)
•   Paint Me Transparent       (Selectively Hide Controls)
•   Sort columns
•   Hide duplicate values in a column   (as for reports)
•   Hide & Restore Selected Columns   (2 pages)
•   Freeze Columns   (3 pages)
•   Move and Resize Columns   (2 pages)



Feedback

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 3 Apr 2024



Return to Example Databases Page




Return to Top