Adding the Row number on access form

Unfortunately, this is a difficult feature to add in a form, but relatively easy to add to a report.  If you have a date/time field, or an ID field in your query that you are sorting by, then you could add a column with a DCOUNT( ) function in it.  Although I generally advise against using domain functions in queries, (they tend to be slow) you could do something like:

SELECT DCount("*", "yourQueryName", "ID <= " & q,ID) as RowNum, q.*
FROM yourQuery as q

It this query is a large recordset, it would run quicker if you saved the results of the query to a temp table.

Dale

This is both easy and fast using a collection - as in this function:

Public Function RowCounter( _
  ByVal strKey As String, _
  ByVal booReset As Boolean, _
  Optional ByVal strGroupKey As String) _
  As Long
  
' Builds consecutive RowIDs in select, append or create query
' with the possibility of automatic reset.
' Optionally a grouping key can be passed to reset the row count
' for every group key.
'
' Usage (typical select query):
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
'
' Usage (with group key):
'   SELECT RowCounter(CStr([ID]),False,CStr[GroupID])) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
'
' The Where statement resets the counter when the query is run
' and is needed for browsing a select query.
'
' Usage (typical append query, manual reset):
' 1. Reset counter manually:
'   Call RowCounter(vbNullString, False)
' 2. Run query:
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable;
'
' Usage (typical append query, automatic reset):
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter("",True)=0);
'
' 2002-04-13. Cactus Data ApS. CPH
' 2002-09-09. Str() sometimes fails. Replaced with CStr().
' 2005-10-21. Str(col.Count + 1) reduced to col.Count + 1.
' 2008-02-27. Optional group parameter added.
' 2010-08-04. Corrected that group key missed first row in group.

  Static col      As New Collection
  Static strGroup As String
  
  On Error GoTo Err_RowCounter
  
  If booReset = True Then
    Set col = Nothing
  ElseIf strGroup <> strGroupKey Then
    Set col = Nothing
    strGroup = strGroupKey
    col.Add 1, strKey
  Else
    col.Add col.Count + 1, strKey
  End If
  
  RowCounter = col(strKey)
  
Exit_RowCounter:
  Exit Function
  
Err_RowCounter:
  Select Case Err
    Case 457
      ' Key is present.
      Resume Next
    Case Else
      ' Some other error.
      Resume Exit_RowCounter
  End Select

End Function

Select allOpen in new window

Study the in-line comments for typical usage.

Keep in mind that any individual row number might change from one day to another depending on how the data is sorted and whether or not anything was added.

I assume there is a physical address for access records, but that is not published to developers.
Is this requirement needed because you do not have a unique key?
And if there is a unique key, what is the special need for row_number in this application?

You assume incorrectly.  Relational databases do not maintain record numbers.  That doesn't make any sense in a relational database when a table is an unordered set.  A table should have a primary key.  It may in addition have one or more unique indexes.  Record order is determined by the Order By clause of a query.   Access tends to fool people into thinking that Access doesn't move records around but it does.  If you update a record and make it longer than it was before, Jet/ACE replaces it with a pointer and puts the updated record in the first available free space.

Software, represents the logical face to users, but all done physically.

My question is,

Adding the Row number on access form

What row number are you talking about?
id fld1
1 1 entered first
2 2 entered next
3 3 entered next

Do you mean 1 1 represents row number 1?

and for next entry 1 3 to be row number 4?

if we delete 2 2, do you want 1 3 to stay row number 4, or to be row number 3. In the second case what about record 3 3?

Please, list few records as I did above, and show possible scenarios when adding and deleting records.

Thanks for referencing the solution you went with.  This will work for you as long as as you are aware that the row number for item x might be different the next time you open the form.  Row numbers assigned this way reflect the sort order (or not) of the underlying query or any change in that sort order applied manually to the form.