472,958 Members | 2,191 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 472,958 developers and data experts.

Row numbers and alternate colors to a Continuous Form

176 100+
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 experts-exchange.com 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
5 27340
32,547 Expert Mod 16PB
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
Michael R
176 100+
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
32,547 Expert Mod 16PB
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
3,080 Expert 2GB
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
32,547 Expert Mod 16PB
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

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Prakash Wadhwani | last post by:
Is there any EASY way to highlight a full row in a continuous form so that as i navigate up & down the table/continuous form using the arrow keys, the entire line (all fields) get highlighted ? ...
by: B | last post by:
I know there are several ways to speed up combo boxes and form loading. Most of the solutions leave rowsource of the combo box blank and set the rowsource to a saved query or an SQL with a where...
by: Damian | last post by:
Hi. Is there a way to programatically populate a continuous form? I have an array of descriptions that I want to display in the continuous form and I have a textbox on the form called...
by: Kathy | last post by:
What is the standard technique for handling the fields in the following scenario on a continuous form? Multiple Divisions. Each Division has multiple Buildings. Each Building has a Supervisor. ...
by: PeterW | last post by:
Hi everybody, I know this issue has already been posted by others and I viewed all the threads dealing with it, but I still couldn't find the whole answer to my problem. So here we go: I have...
by: pradheepayyanar | last post by:
Dear All i have dynamic generation of <table> rt now i have done the <TR> with alternate colors which is a dynamic table. my requirement is that for every cumulative <tr> i need different color....
by: OllyJ | last post by:
Hi guys, probably a basic question but have been struggling for some time so thought i'd see if you could help. I have a main schedule table which contains the fields... Date; Machine; Arm/Head;...
by: Steve | last post by:
I have a continuous form showing Product Code and Product Name. Product Code is five digits and is sequential. I have a textbox in the form header. What is the code to scroll the continuous form so...
by: AdamOnAccess | last post by:
This is something I've always had trouble with, and it comes up fairly frequently... I have a continous form with a single text field and next to this text field, I have a caption that maintains...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.