Row numbers and alternate colors to a Continuous Form

[Michael R]

Searching the net I've found a simple technique to add row numbers and alternate colors (for the even and the uneven row) to a continuous form.

1st step: Create a textbox, send it to background and select the first color.
.ControlSouce =fRowNum(False)
.Name = RowNum

2nd step: Add the following function to the form module: (for row numbers)

Public Function fRowNum(Reset As Boolean) As Long
Static I As Integer
 
If Reset = True Then
   I = 0
   Exit Function
End If
'Add Row Numbers to Continuous Forms:
I = I + 1
fRowNum = I
 
End Function
 

3rd step: Add the following code to the form OnOpen or OnLoad event, or any other event that has Requery in it, for the row numbers to recalculate correctly:

me.fRowNum False

4th step: Add the following code to form's OnOpen or OnLoad Event or add a condition it via the Conditional Formatting panel: (for alternate colors). Before using the code, create a textbox RowColor with the width of the whole form for alternate colors, send it to background, and make other controls' backcolor transparent.

 
Dim objColor As FormatCondition
 
    Set objColor = Forms![myForm]![RowColor].FormatConditions.Add(acExpression, , "[RowNum] Mod 2 = 0")
 
    With Forms![myForm]![RowColor].FormatConditions(0)
       .BackColor = 15132390
    ' or whatever color you choose for your alternating rowcolor...
    End With
 
    Set objColor = Nothing
 

Now the form appears with row numbers and alternate colors. This is a simple way but there's one 'bug' to it - if a user plays with the mouse on the form's detail or plays with the scroll bar on the first few instances after starting the form, (when the rows are being calculated) the row numbers are comming messed up, and so are the alternate colors because they depend on the row numbers.

Any suggestions on how to improove this?

Cheers, Michael.

Editors Note: Michael worked on it more and then came up with this solution.

Here's a complete soultion for this that works firmly, without any scrolling or refresh problems. Credit to Stephen Lebans for the numbering function code and to the experts-exchange.com board for combining the overall solution:


1st step: Add the following function to an existing or a new project module:

Public Function RowNum(frm As Form) As Variant
On Error GoTo Err_RowNum
    'Purpose:   Numbering the rows on a form.
    'Usage:     Text box with ControlSource of:  =RowNum([Forms]![myForm])
 
    With frm.RecordsetClone
        .Bookmark = frm.Bookmark
        RowNum = .AbsolutePosition + 1
    End With
 
Exit_RowNum:
    Exit Function
 
Err_RowNum:
    If Err.Number <> 3021& Then  'Ignore "No bookmark" at new row.
        Debug.Print "RowNum() error " & Err.Number & " - " & Err.Description
    End If
    RowNum = Null
    Resume Exit_RowNum
End Function 

2nd step: Add a textbox for row numbers to any form of your project with the following propreties:

.Name = RowNum
.ControlSource = =RowNum([Forms]![myForm])

3rd step: For alternate colors add a textbox with the width of the entire form, send it to background, and set others controls background color (and border) to transparent. Use Conditional Formatting dialog with the conditional Expression Is: [RowNum] mod 2 = 0 for the even rows, or add the following code in the projects module for On Load or On Open event:

Dim objColor As FormatCondition
 
Forms![myForm][RowColor].FormatConditions.Delete
 
Set objColor = Forms![myForm]![RowColor].FormatConditions.Add(acExpression, , "[RowNum] Mod 2 = 0")
 
With Forms![myForm]![RowColor].FormatConditions(0)
       .BackColor = vbGrey ' or whatever color you choose for your alternating rowcolor...
End With
Set objColor = Nothing 

[NeoPa]

The whole concept of numbering lines returned from a recordset is quite foreign within an RDBMS like Access. There are often request for it though and sometimes almost solutions are found (I've even written one myself ;)). Unfortunately Access wasn't designed to support this (ordinal numbering of records) as it rather restricts its ability to perform its job when restrained to do it in a particular linear way. Having said that, it will typically work as outlined. This is definitely the weak link in the logic though, and I don't see an easy way around it I'm afraid.
Interesting thread.

[Michael R]

Although Access doesn't implement record numbering in it, insets like row numbers and alternate colors can make certain continuous forms to appear more readable.

Please notice that this manner of conducting the task doesn't use recordset and it is quicker than using recordset.

The method works correctly only if one is gentle enough with the mouse, so to speak, for the row numbers and the colors to come out correctly. Also, scrolling too fast to the not yet calculated/painted parts of the form messes the numbers. I think this is so because of the way Access constructs continuous forms. When the mouse gets on the rows it changes Access order of carrying out form's construction.

To make this work better, maybe it is possible to create a second or a 2 seconds delay on the On Open event that won't let the user to move the mouse on the detail section until the form's rows are constructed? Even if it is, it still leaves the scrolling too fast problem unsolved. So maybe it is possible to make Access to first construct the entire continuous form and then let the user to 'mess' with it?

[NeoPa]

You're right in a sense but then not quite right in another.
I suspect that moving the current record of the recordset (A bound form DOES use one I'm afraid, regardless of whether or not it is accessed in code) to the end may get around your problem somewhat.
I can't promise that it will work 100% reliably for you, but it should improve the situation somewhat at least. You can use Me.RecordSet.MoveFirst followed by Me.RecordSet.MoveLast in your Form_Open() procedure to create the effect.
Please let us know what effect this has.

[nico5038]

I've used a similar technique, but continuous forms have a will of their own.
I used a refresh to get the results more predictable.
In A2007 this alternate colors is handled by Access and thus would be no longer a problem.

Nic;o)

[NeoPa]

Thanks for posting that Michael.
It hadn't occurred to me to use .AbsolutePosition I must admit.
An answer with a solution always rounds off a thread nicely I feel :)