Adding a Rank column to a report

[OtheymAverian]

Using Access '02, Windows XP Pro SP2

I have a Report that Contains data that is sorted by name and have been asked to add a field that would output a rank based on a numerical (currency) field.

Example Report structure as it is now (dashes added for spacing purposes)

Name-----------Score
----------------------------
ABC------------------10
BCD------------------25
CDE--------------------5

Example Report Structure needed

Rank------Name--------------Score
------------------------------------------
---2---------ABC-------------------10
---1---------BCD-------------------25
---3---------CDE-------------------5

I just started my adventure into Access a few weeks ago and have no SQL knowledge. I have attempted to make a query to pull data, resort, and number, but I have been unable to get my head around some of the comcepts. I do not know if it is possible to add a field into a query that would auto count a rank (based on the query being sorted by the Score field)

[NeoPa]

Do you currently have code that executes your action query(s)?
Or do you run them manually?

[OtheymAverian]

I was running them manually until earlier today. Today I made a form that had buttons that initiate the reports.

The query in question is ran when a specific report is opened. (On Open event)

-Oth

[NeoPa]

If you post the code for your OnOpen event I'll show you where to put the extra lines.
Don't forget to use [code] tags.

[NeoPa]

Actually, post the code for the button that opens the report as well as the OnOpen event procedure of the report and we'll have a look at what's going on exactly.

[OtheymAverian]

Are you talking VBA code? I am using the On Open field in the properties box when viewing the report in design mode (selecting report from the drop down box). As far as I can see there is not VBA code for the report.

I think this is what you want for the button :

Private Sub SalesNetMTDReport_Click()
On Error GoTo Err_SalesNetMTDReport_Click
 
    Dim stDocName As String
 
    stDocName = "Sales Net MTD"
    DoCmd.OpenReport stDocName, acPreview
 
Exit_SalesNetMTDReport_Click:
    Exit Sub
 
Err_SalesNetMTDReport_Click:
    MsgBox Err.Description
    Resume Exit_SalesNetMTDReport_Click
 
End Sub

[OtheymAverian]

I put the 2 lines of code in before and after the report was called and it ran without posting the warnings. I think I got it.

-Oth

[NeoPa]

Thanks for the VBA routine posted. That is pretty standard so no problems there.
I don't really understand what you're saying about the OnOpen event procedure of the report so let's try this instead :

1. From the main database window select Reports.
2. Select the report called [Sales Net MTD].
3. Open it for Design.
4. Make sure the Properties pane is open (Alt-Enter).
5. Go down to the 'On Open' property.
6. Select [Event Procedure].
7. Click on the elipsis button (...).
8. When the VBA code window opens just Copy (Ctrl-C) and Paste (Ctrl-V) the whole lot into a post here.

[NeoPa]

I put the 2 lines of code in before and after the report was called and it ran without posting the warnings. I think I got it.

-Oth

That may work sometimes but is not the correct place.
Post me the other code and I'll show you the proper place :)

[OtheymAverian]

OH!! I wrote a tiny little macro that called the query and then called that macro in the 'On Open'. If i view the code for the report there is none. I assume it is because I am calling a macro and not a query directly.

-Oth

[NeoPa]

In that case you have two options :

1. Leave the code as you already have it.
2. Explore in the macro options for the command, for a setting which disables those messages

I would recommend using just VBA code in a database. Mixing the two means that you need to understand both. There's nothing I'm aware of that can be done in a macro but not in code.