We help IT Professionals succeed at work.

Access Tabular Form - Row Numbering

advlegals
advlegals asked
on
604 Views
Last Modified: 2013-11-28
I have a tabular form which displays annual data in month blocks.

The form has a sort function on it and I can sort on all months Desc.

I'm trying to add a field onto the form that'll number rows from 1..  Please bear in mind that when the recordset is re-sorted the row numbering needs to remain static, running from 1.. to the bottom of the form.

This, on the face of it, would seem like a simple thing to accomplish. I can return a row number from a click event by using Me. CurrentRecord but I can't assign this to my row counter text box at runtime.
Comment
Watch Question

Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Mike EghtebasDatabase and Application Developer

Commented:
What is the SQL of the forms record source. I have a solution but involves few steps. Post your SQL here and identify the default sort field (when the form first opens).

Mike
Brian PiercePhotographer
CERTIFIED EXPERT
Awarded 2007
Top Expert 2008
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
One gotcha with this:

=DSum( [Field1] , Table1 , [Table1].[id]<=[Forms]![Form1]![id] )

it could be painfully slow with a lot of records and ... if there are any gaps in the auto number, you may not get sequential numbers.

mx
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
further....
you will have to set glbRowCount = 0 prior to running the query (unless you just started Access mdb).

This could be done from within a sub

Sub StartForm()
glbRowCount = 0
docmd.openform "yourform"
end sub

Scott C


Scott C
Mike EghtebasDatabase and Application Developer
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.