Auto Numbering Records 1

[jbento]

All,
I really hope someone can help with this, because this would really make my life easier.

I have a table with 3 fields, called: state, rank, title. There are more fields, but I will just give these 3 as an example. I have 5 records as follows: I will list the field title at the top and then the next lines will be the actual records

state rank title
open 1 This is title 1
open 2 This is title 2
open 3 This is title 3
open 4 This is title 4
open 5 This is title 5

The problem I have is that when i run a report I want my records to automatically number in sequence if I move one of the records to a different state.

For instance if I move record 3 to a closed state, the records will look like this on the report:

state rank title
open 1 This is title 1
open 2 This is title 2
open 4 This is title 4
open 5 This is title 5

As you see the number is out of sequence, because it has rank 1, 2, 4, and 5. I would then have to manually go in and change the rank field number for record 4 and 5 to read 3 and 4, so it will be in sequence.

I would like to know if there is a way to do this automatically in the report, so I don't have to manually change the numbers if I move a record out of the open state?

Can anyone help me with this?

 

[scriverb]

If I understand this correctly try this:

In the report Grouping and Sorting select State as the first item and select Yes for the Group Header. Then select Rank as the next item. Sort them both ascending.

Now create a Report level variable called vRankCounter as Integer.

Open the properties of the State Group Header and select On Format event procedure. Enter the following:

vRankCounter = 0

Open the properties of the Detail Section. Enter the following:

vRankCounter = vRankCounter + 1
me![RankCounter] = vRankCounter

In the Detail Section create a control and set the name of the control to RankCounter.

There must also be a control called rank which is your original control. This is needed for sorting purposes only. It can be made invisible after testing. You see the control Rank will be sorted into your proper order but not be displayed. The variable vRankCounter will be reset to 0 at the beginning of each grouping and incremented with each record. This incremented variable starting at one will be displayed rather than the original record ranking field. This will allow for the rank field to be used for sorting but not displaying the variable vRankCounter will be displayed and have no gaps.

Let me know how this works for you. Bob Scriver

 

[jbento]

I will definitely let you know tomorrow when I try this at work. Thanks so much for responding.

 

[jbento]

scriverb,
In the properties of the Detail Section, where do I place the following:

Open the properties of the Detail Section. Enter the following:

vRankCounter = vRankCounter + 1
me![RankCounter] = vRankCounter

Does that go in the OnFormat, OnOpen, etc. event?

 

[Laois]

This is easy. Just add a text box to your detail section. Set it's control source to =1 and set the Running Sum property to Over Group. Just make sure the records are sorted by Rank. That's it, your done.

Dermot

 

[jbento]

Laois,
That worked BEAUTIFULLY, and it was so simple. Thank you so much, and I also thank GOD for TEK-TIPS. This forum helps a lot of people, and I always try to do my best in helping others on here, with what I know.

Again, I thank you!!!!!

 

[scriverb]

laois, Great idea. Didn't think of that one. Will store that in my bag of little tricks for the future.

Thanks Bob Scriver