Question Auto numbering records in a continous form

[josephbupe]

Hi,

If I want to arrange records sequentially in a report I would do the following:

From the Toolbox (Access 1 - 2003) or the Controls group of the Design ribbon (Access 2007 and later), add a text box for displaying the number.

1. Select the text box, and in the Properties Window, set these properties:

Control Source =1 Running Sum Over Group
as suggested by Allenbrowne at http://allenbrowne.com/casu-10.html

But, how can I sequentially arrange records on a continous form?

Best regards.

Joseph

 

[bob fitz]

1. Select the text box, and in the Properties Window, set these properties:

Control Source =1 Running Sum Over Group
as suggested by Allenbrowne at http://allenbrowne.com/casu-10.html

And just below that he says:

Form


Casual users sometimes want to number records in a form as well, e.g. to save the number of a record so as to return there later. Don't do it! Although Access does show "Record xx of yy" in the lower left ofthe form, this number can change for any number of reasons, such as:

• The user clicks the "A-Z" button to change the sort order;
• The user applies a filter;
• A new record is inserted;
• An old record is deleted.

In relational database theory, the records in a table cannot have any physical order, so record numbers represent faulty thinking. In place of record numbers, Access uses the Primary Key of the table, or the Bookmark of a recordset. If you are accustomed from another database and find it difficult to conceive of life without record numbers, check out What, no record numbers?
You still want to refer to the number of a record in a form as currently filtered and sorted? There are ways to do so. In Access 97 or later, use the form's CurrentRecord property, by adding a text box with this expression in the ControlSource property:

=[Form].[CurrentRecord]In Access 2, open your form in Design View in design view and follow these steps:

1. From the Toolbox, add a text box for displaying the number.
2. Select the text box, and in the Properties Window, set its Name to txtPosition. Be sure to leave the Control Source property blank.
3. Select the form, and in the Properties Window set the On Current property to [Event Procedure] .
4. Click the "..." button beside this. Access opens the Code window.
5. Between the lines Sub Form_Current() and End Sub, paste these lines:

On Error GoTo Err_Form_Current Dim rst As Recordset Set rst = Me.RecordsetClone rst.Bookmark = Me.Bookmark Me.txtPosition = rst.AbsolutePosition + 1 Exit_Form_Current: Set rst = Nothing Exit Sub Err_Form_Current: If Err = 3021 Then 'No current record Me.txtPosition = rst.RecordCount + 1 Else MsgBox Error$, 16, "Error in Form_Current()" End If Resume Exit_Form_CurrentThe text box will now show a number matching the one between the NavigationButtons on your form.

 

[gemma-the-husky]

in particular, to sort records in a report, use the sorting and grouping option on the report. it lets you select fields in a particular order, and you can add group headers and footers to enable you show sub-totals etc, and present your report in a particular way.

note that a report uses these criteria for sorting, rather than the sort order of the query.

of course in a report, you can then including running sums in certain columns - but all of this is part of the report generators functionality.

 

[josephbupe]

Hi,

Thank you for your responces. However, I couldn't adapt it; hence, I resorted to using another approach by lebans here: http://www.lebans.com/rownumber.htm

Stay well.

Joseph