How to show the Record Number on a Sub-Form just like Running Sum In a Report?

Issa S.
Issa S. used Ask the Experts™
on
HI,,,

How to show the Record Number on a Sub-Form just like Running Sum In a Report?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Define 'Record Number' ....
There is no Record Number per se in an Access Table.  The closest thing is the *relative* record number you see in a Datasheet View form (sub form) when you have the Navigation Buttons enabled. But even that record number is subject to change as records are added/deleted, etc.
I supposed you could ... invent some scheme but not sure it's work it ....

Author

Commented:
Hello Joe:

If I say 'raw number' in  the sub-form, is it OK?
What do you call the "record selectors" in the property sheet of the sub-form?
My sub-form is based on a query.

Pls guide me to the right Access terminology as am new to this.

Thanks
Distinguished Expert 2017

Commented:
Tables in relational databases do not have record numbers.  That is an old dBase concept.  Spreadsheets also display record numbers.  But since a table is by definition an unordered set, a record number doesn't make any sense.

What are you trying to do with the record number?  Will it be permanent so that record 1 will always be record 1 regardless of how the subform is currently sorted?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hello Pat,,,

Once I pick the record Number, It will used just as a label for some documents in a separate file.
So there is NO infringing to the  DB relationships, etc.
If the record is not there after leaving the form, nothing will happen.
You can trust me on these concerns (after 40 years in the IT . . .  lol, old enough).  
This is a forced Requirement by the Company Standards and Audit rules.
No one cares about unique Primary Keys and so on or extra one field here or there.
A reference to a Cheque   or  Promissory Note has to be constructed in accordance with a certain scheme and that is it.
Users and management do not care about these technicalities.

Thanks.
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Record Number ... just like Running Sum

You can't do this in the form itself, except if you add a field which you repeatedly updates.
But you can expand the query you use as source for the subform to use the function below:

SELECT 
    tblChild.ID, 
    tblChild.FK, 
    tblChild.SomeFieldToSortOn, 
    tblChild.SomeOtherFieldToSortOn, 
    RowCounter(CStr([ID]),False,CStr([FK])) AS [RowNo]
FROM 
    tblChild
ORDER BY 
    tblChild.FK, 
    tblChild.SomeFieldToSortOn, 
    tblChild.SomeOtherFieldToSortOn;

Open in new window

This is quite speedy due to the use of a static Collection.

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

Open in new window

/gustav
Distinguished Expert 2017
Commented:
Issa,
I understand the requirement for a unique, non-autonumber identifier.  And now that I know that you want to store this number permanently, I can give better advice.  For starters, the number needs to be assigned in the BeforeUpdate event of the form when Me.NewRecord is true.  One possible solution is to keep a table whose only purpose is to assign a sequential number.  To be safe, you need to do the assignment inside a transaction otherwise in a busy multi-user environment gaps can occur.  Start Transaction, Step 1 is to find the last assigned number and update the table to increment it.  Step 2 is to save the record to which is is being assigned.  End Transaction.  The reason for the transaction is twofold.  First you need to lock the table that assigns the numbers until you have actually used the number and saved the record.  And second because you don't want the first update to succeed if the second one fails.

You can do this without the assignment table but in that case, you have the possibility of creating duplicate numbers.  Use DMax() to get the highest number, increment it, save the record.  Depending on system speed and how many other users are adding records, it is possible for two users to get the same number.  The first save succeeds but the second fails (I'm assuming you will have a unique identifier on the generated number).  That causes you to need a loop to keep getting new numbers until a save succeeds.  I think this second logic is more complex and more difficult ti implement correctly than the first solution using a separate table and a transaction.

Author

Commented:
Dear Pat / Gustav,,,

I think now we are synchronized on the same wave.
I will be trying it soon and let you know.

Thanks a lot.

Author

Commented:
Dear Pat / Gustav,,,

Wonderful . . . and . . . .  Working  beautifully.

I change the sorting to be on the Foreign Key (FK), a Date,  and the new  Row No.

The sorting on the Row No. will be for the sequence of presenting the rows in the sub form.    Is this OK?

Many many Thanks.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Well, you should not sort on the calculated row number ... makes little sense.

/gustav
Distinguished Expert 2017

Commented:
The sorting on the Row No. will be for the sequence of presenting the rows in the sub form.    Is this OK?
Yes, that is correct assuming you are using my suggestion of creating the number when the record is created so that it is permanent.  It is not clear to me what you are actually doing since Gus and I gave you completely different solutions.  Gus' solution gives you an on the fly number that could change every time you open the form.  I gave you a permanent solution that stays with the record similar to a check number.

Author

Commented:
Dear PAT,,,

Is there any tool within the Win 10 by which I grab some rectangular area from the screen so I can pass it to you here?
In the old days, I used to use ZapGrab 3rd party. Anything similar.
Thanks.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
There is the native Snipping Tool

/gustav
Distinguished Expert 2017

Commented:
You might have to use Cortina to find the Snipping Tool if it isn't in your tray.  Alternatively you can use the Print Screen and then use Paint to trim it down.  You might also have to search for Paint if you can't find it under accessories.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Snagit ... best ever ... cool in SO many ways

https://www.techsmith.com/screen-capture.html
Distinguished Expert 2017

Commented:
Snagit is excellent and I use it at home but it isn't free.  It isn't expensive as far as useful tools go be it still might matter.  For documentation, I always use Snagit because it has the ability to grab dropped combos or menus and the free tools lack that capability.  For day-to-day stuff at a client site, I use the Snipping Tool built into Windows.

Author

Commented:
Thanks a Lot Gustav & Pat.

It was very helpful and professional.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

/gustav

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial