Show record number in a text box on a form - Access 2013

[Apple08]

Hi

Please would anyone help that I need to add a text box to show the record number automatically once a record is added or delete. It is a form for agenda items, so once someone add text into a field then the text box will show 1 for item 1, and 2 for item 2 and if record is deleted then the number will be in sequence automatically.

I have tried to add Event Procedure below but it only shows the last record's number to each text box:

(Text box name txtPosition)

Private Sub Form_Current()

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_Current

End Sub

Please could anyone help?

Many thanks!

 

[xenou]

You would need to be using an event for a new record, not a current record (or at least something that captures when a new record is created or when a new record is saved - so Form Save Event is probably the best).

I think I would just use an autonumber ID. So, work done! Otherwise, it's not a trivial problem if more than one user can be using a database at one time, since you need to be sure you don't calculate the same id number two times.

this is a bad idea:

record is deleted then the number will be in sequence automatically

You will need to renumber all the records if you do this, whenever any record is deleted. And your audit trail is ruined. Many databases don't even actually delete records, they just mark them as "cancelled" or "void". Again, not a trivial issue if the database can be multiuser (not even particularly easy for even one user - it's the kind of thing that would need to be done when no one is actively working with any records).

 

[Apple08]

Thanks.