Avatar of Issa S.
Issa S.
 asked on

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?
Microsoft Access

Avatar of undefined
Last Comment
Gustav Brock

8/22/2022 - Mon
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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 ....
Issa S.

ASKER
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
PatHartman

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?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Issa S.

ASKER
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.
SOLUTION
Gustav Brock

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Issa S.

ASKER
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.
Issa S.

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gustav Brock

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

/gustav
PatHartman

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.
Issa S.

ASKER
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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Gustav Brock

There is the native Snipping Tool

/gustav
PatHartman

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 - Former Microsoft Access MVP)

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

https://www.techsmith.com/screen-capture.html
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PatHartman

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.
Issa S.

ASKER
Thanks a Lot Gustav & Pat.

It was very helpful and professional.
Gustav Brock

You are welcome!

/gustav
Your help has saved me hundreds of hours of internet surfing.
fblack61