home

Highlight Rows on Continuous Forms

9/4/01 3:19:51 PM

Based on information from this article.

A very commonly asked question, I am told, is how to highlight continuous rows on a form. This is a very nifty feature to have working for you, because (a) continuous forms are very commonly utilized to show all the records in a given table in "spreadsheet" layout and (b) using typical programming logic, attempting to highlight one row typically results in all the rows being highlighted.

Properly executed, setting this up will result in very professional-looking datasheet browsing form. A typical example is shown here:

browse records screenshot

There is an article at the MVPS.Org web site which describes how to do this. Before I mention the link to it, though, 2 key footnotes needs to be mentioned:

  • The CtrlBack text box mentioned in the explanations needs to be formatted "Send to Back." With the form under "design" view and the "CtrlBack" text box selected, from the pull-down menus execute "Format....Send to Back." This is a VERY important thing to remember, because otherwise the highlight will completely obliterate the entire row & make it unreadable. The instructions don't mention this, I found out the hard way.

  • This is for Access 97. Access 2000 has a "conditional formatting" feature, and it's possible that it can be substituted for these procedures. I'm not sure.

    For complete instructions, look below or go to the article.

    This procedure will highlight the current row in a continuous form. Please see the notes at the end to learn what this procedure DOES NOT do.

    This sample is based on the "Products" table from the Northwinds Database.Changes will be noted so you may use this code in your own form.

    1. Create a new form based on the "Products" table.
    2. Add all fields from the products table to the form.
    3. Create the following Controls to the form

      Name: CtlBack
      Control Source: =IIf([SelTop]=[ctlCurrentLine],"ÛÛÛÛÛÛÛÛÛÛÛÛ",Null)

    "Û" is character 0219, the easiest way to enter this is to copy and paste from here. Format the font of this control as Terminal.

    Place this control on your form so that it is sized to cover the entire area where you would like the background to be. Experiment with the number of "Û" characters as well as the font height to get complete coverage. Set the background to transparent. Set the foreground to whatever color you want your highlight color to be. Make sure the section background color is different from the highlight color.

    Next, for all the controls that will have the background highlight, select them all, change the background color to the highlight color, then change the background color to transparent. (Yes, this step is necessary).

    The following two controls can be placed anywhere, and be hidden. You may want to leave them visible to help in seeing how this works, then hide them when done.

    Name: ctlCurrentLine
    Control Source:=GetLineNumber()

    Name: ctlCurrentRecordControl
    Source: unbound

    Add the following code behind the form:

    '****************** Code Start ******************* '
    Function GetLineNumber()
    'The function "GetLineNumber" is modified from the Microsoft Knowledge Base
    ' (Q120913), the only difference here is that the following items have been hard
    'coded:F, KeyName, KeyValue. This was done to add a slight performance
    'increase. Change KeyName and KeyValue to reflect the key in your table.

    Dim RS As Recordset
    Dim CountLines
    Dim F As Form
    Dim KeyName As String
    Dim KeyValue

    Set F = Form
    KeyName = "productid"
    KeyValue = [ProductID] 

             On Error GoTo Err_GetLineNumber
         Set RS = F.RecordsetClone
         ' Find the current record.
         Select Case RS.Fields(KeyName).Type
            ' Find using numeric data type key value.
            Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
            DB_DOUBLE, DB_BYTE
               RS.FindFirst "[" & KeyName & "] = " & KeyValue
            ' Find using date data type key value.
            Case DB_DATE
               RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
            ' Find using text data type key value.
            Case DB_TEXT
               RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
            Case Else
            MsgBox "ERROR: Invalid key field data type!"
               Exit Function
               End Select
         ' Loop backward, counting the lines.
         Do Until RS.BOF
            CountLines = CountLines + 1
            RS.MovePrevious
            Loop
Bye_GetLineNumber:               ' Return the result.
         GetLineNumber = CountLines
         Exit Function
Err_GetLineNumber:
      CountLines = 0
      Resume Bye_GetLineNumber

    End Function

    Private Sub Form_Click()

    Me!ctlCurrentRecord = Me.SelTop
    End Sub

    Private Sub Form_Current()

    Me!ctlCurrentRecord = Me.SelTop
    End Sub
    '****************** Code End *******************