[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 568
  • Last Modified:

Access Tabular Form - Row Numbering

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.
0
advlegals
Asked:
advlegals
  • 2
  • 2
  • 2
  • +1
4 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"on the face of it, would seem like a simple thing to accomplish. "

Yep ... simple in concept, but not so easy to implement on a Form.  Easy on a Report because you have the Running Sum property on a text box - which you can set to =1 ... and thus will increment.  Unforunately, on a Form ... that property does not exist.  So, you have to jury rig something.  This comes up often here, with various solutions or partial solutions ... not sure I've actually seen a full solution - without some sort of gotcha.  You might search the EE site and see what turns up.

The basic problem here is you are trying to generate 'data' that's not really there.  Ideally, you could just put

=[Forms].[YourFormName].[CurrentRecord]

in a text box on the continuous form.  But, since it's a calculated control and not Bound to a field ... you get the same number in each text box - which varies depending on which row you click in.

mx
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
0
 
KCTSCommented:
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)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
0
 
clarkscottCommented:
1. Create a global variable in a module
global glbRowCount as long

2. Create a function in a modue
function IncrementRowCount() as long
glbRowCount  = glbRowCount + 1
end function

3.  Use a query for the recordsource of your form and include a new column.

RowCount: =IncrementRowCount()

As the query opens, the function will be called for each new row - incrementing RowCount each time.

Scott C
0
 
clarkscottCommented:
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
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now