UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Continuous Forms: Highlight Current Row    
Continuous Forms: Highlight Current Row

Demo for highlighting current record in continuous form

Highlighting the currently selected row of a Continuous Form is a frequent request which simply involves a little VBA and some conditional formatting.


The method:

1. Add an unbound textbox to your continuous form (leave it’s Control Source Property blank). It can be placed in any section of the form – header/footer/detail. Name it txtID as it will be used to hold the primary key/ID of the selected record. Set it’s visible property to “No”, so that it is hidden.


2. Add code the form’s Current Event to write the primary key to the unbound textbox. In the posted sample, a simple company/employees form, the Current Event code is:

Private Sub Form_Current()

       Me.txtID = Me.EmployeeID

End Sub

In the above code, txtID is the name of the unbound textbox we created in step 1, and EmployeeID is the name of the Primary Key field in the underlying table (you do not need a textbox for this field reference).

3. Arrange your form controls. For the best appearance, arrange the controls in your Continuous Form’s detail section so that they are all lined up, with borders touching (no space between them).

4. Select all of the controls in the detail section, and click the Format Tab on the ribbon, then Conditional Formatting. Select Expression IS from the dropdown list, and enter the following expression, and select a fill color for the background:

[txtID] = [EmployeeID]


This checks the value held in the unbound textbox against the Primary Key, and will apply the formatting to records on the form where the two values match.

That’s it!

The theory:

Unbound controls contain the same data in all records in a form. The reason for this is that even though controls appear in multiple records, they are still only single, individual controls. This is most apparent in a continuous form, where all records are displayed together. If you place an unbound control on a continuous form, and edit its value you will notice that all of the records in the form display the value just entered.

This can be s source of frustration for developers getting accustomed to continuous forms, but it is put to advantage in highlighting the current record.

In the sample, the current event code that writes the EmployeeID to the unbound txtID fires when the form opens, and each time the user moves to a different record. When that code fires, the EmployeeID of the currently selected record (and ONLY the current record) will match the value held in txtID. This in turn is what triggers the conditional formatting, based on the expression [txtID] = [EmployeeID], which will only highlight the current record. (You can further see this in action by making txtID visible in the sample database.)

But what about the New Record line?

With conditional formatting based only on [txtID] = [EmployeeID], if the user clicks the new record line, it will be unformatted. The reason for this is that EmployeeID and txtID will be NULL – which unhandled does not play well in comparisons, since it is actually an absence of value. So taking it one step further, you can highlight the new record line when the user selects it by testing for nulls in both txtID and EmployeeID. The sample does this with the following expression: [EmployeeID] Is Null And [txtID] Is Null


Edit Discussion
Custom Search

Thank you for your support!
This page has been accessed 18,606 times.  This page was last modified 14:39, 24 May 2016 by BruceM. Contributions by mbizup and Jack Leach  Disclaimers