By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,040 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Row numbers and alternate colors to a Continuous Form

P: 176
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)
Expand|Select|Wrap|Line Numbers
  1. Public Function fRowNum(Reset As Boolean) As Long
  2. Static I As Integer
  4. If Reset = True Then
  5.    I = 0
  6.    Exit Function
  7. End If
  8. 'Add Row Numbers to Continuous Forms:
  9. I = I + 1
  10. fRowNum = I
  12. 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:
Expand|Select|Wrap|Line Numbers
  1. 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.

Expand|Select|Wrap|Line Numbers
  2. Dim objColor As FormatCondition
  4.     Set objColor = Forms![myForm]![RowColor].FormatConditions.Add(acExpression, , "[RowNum] Mod 2 = 0")
  6.     With Forms![myForm]![RowColor].FormatConditions(0)
  7.        .BackColor = 15132390
  8.     ' or whatever color you choose for your alternating rowcolor...
  9.     End With
  11.     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 board for combining the overall solution:

1st step: Add the following function to an existing or a new project module:
Expand|Select|Wrap|Line Numbers
  1. Public Function RowNum(frm As Form) As Variant
  2. On Error GoTo Err_RowNum
  3.     'Purpose:   Numbering the rows on a form.
  4.     'Usage:     Text box with ControlSource of:  =RowNum([Forms]![myForm])
  6.     With frm.RecordsetClone
  7.         .Bookmark = frm.Bookmark
  8.         RowNum = .AbsolutePosition + 1
  9.     End With
  11. Exit_RowNum:
  12.     Exit Function
  14. Err_RowNum:
  15.     If Err.Number <> 3021& Then  'Ignore "No bookmark" at new row.
  16.         Debug.Print "RowNum() error " & Err.Number & " - " & Err.Description
  17.     End If
  18.     RowNum = Null
  19.     Resume Exit_RowNum
  20. 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:
Expand|Select|Wrap|Line Numbers
  1. Dim objColor As FormatCondition
  3. Forms![myForm][RowColor].FormatConditions.Delete
  5. Set objColor = Forms![myForm]![RowColor].FormatConditions.Add(acExpression, , "[RowNum] Mod 2 = 0")
  7. With Forms![myForm]![RowColor].FormatConditions(0)
  8.        .BackColor = vbGrey ' or whatever color you choose for your alternating rowcolor...
  9. End With
  10. Set objColor = Nothing 
Jan 29 '07 #1
Share this Article
Share on Google+

Expert Mod 15k+
P: 31,347
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.
Jan 30 '07 #2

P: 176
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?
Jan 30 '07 #3

Expert Mod 15k+
P: 31,347
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.
Jan 30 '07 #4

Expert 2.5K+
P: 3,072
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.

Jan 30 '07 #5

Expert Mod 15k+
P: 31,347
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 :)
Jan 31 '07 #6