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

HI,,,

How to show the Record Number on a Sub-Form just like Running Sum In a Report?
Issa S.Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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 ....
0
Issa S.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
0
PatHartmanCommented:
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?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Issa S.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.
0
Gustav BrockCIOCommented:
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
1
PatHartmanCommented:
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.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Issa S.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.
0
Issa S.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.
0
Gustav BrockCIOCommented:
Well, you should not sort on the calculated row number ... makes little sense.

/gustav
0
PatHartmanCommented:
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.
0
Issa S.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.
0
Gustav BrockCIOCommented:
There is the native Snipping Tool

/gustav
0
PatHartmanCommented:
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.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Snagit ... best ever ... cool in SO many ways

https://www.techsmith.com/screen-capture.html
0
PatHartmanCommented:
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.
0
Issa S.Author Commented:
Thanks a Lot Gustav & Pat.

It was very helpful and professional.
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.