[RESOLVED] Access Report Question

[Besoup]

Hello,
I have an access report that I would like the first field to display the count of the record being dispayed starting at one. Is there a formula I can add to a field to calculate this?

Thanks

[RobDog888]

You can create a unbound textbox on your detail section. Create a module level integer variable. Then in the Report's Detail_Format procedure you can increment your variable in a formula like IntCount = intCount + 1.

[Besoup]

Would I put the formula in the text box?

[RobDog888]

No, you put it in the class module behind the report.

[Besoup]

Ok I created a module level variable

Public iIntCount As Integer

Then I added the following behind the report:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
iIntCount = iIntCount + 1
txtRank = iIntCount
End Sub

When I run the report the number it starts counting from starts @ 2388...

Do you know what would cause that?

[RobDog888]

On the report Report_Open event initialize the iIntCount variable to 0. See if that helps. Is that value the number of records by any chance?

[Besoup]

On the report Report_Open event initialize the iIntCount variable to 0. See if that helps. Is that value the number of records by any chance?

Yeah I tried that and get the same thing.... yes it is the number of the records, would it be better to add an autonumber field to the query? I am not sure how I would do that, if you have an example...

[RobDog888]

An autonumber field would work if your not sorting or limiting the records in your recordsource in any way. For ex. if you had 2000 records then your id will be from 1 - 2000 sequential. But if you use a filter or where clause in your sql for your recordsource then you will get id numbers that are not sequential.

To add a autonumber field to your access table, in design view of the table add a new field and calll it idCount or ??? and select the Autonumber item from the type combo dropdown. Bind that field to your textbox txtRank.

[Besoup]

Yeah the report had to be sorted and it is coming from a query... was wondering if you could add a autonumber field to the query as it was run, but probably not.... will have to figure out why I am getting such a high number to start on my module level variable...

[RobDog888]

Ok then if your doing those things to it then you can add a dynamic field on the fly but I forget how the formula may go.

Let me see if I have any projects that used this in them.

[Besoup]

Yeah if you could find anything I would appreciate it, Thanks man.

[Besoup]

Found a way to do it in the report... Had to create an unbound text boxt then go to the properts. Under Data you have to enter =1 in the Control Source property and change Running Sum to Over All. Works perfectly!

Thanks for the help!