Looping until EOF

  • Thread starter Thread starter David
  • Start date Start date
D

David

I create a table every week that looks like this; every
week the contacts get high I need it in desending order
I will create the table first but I need to add the rank
as you will see. I stink at code. Any suggestions!
Thanks..

Firm_ID Name Contacts
AAA Nname 100
BBB Aname 85
CCC Cname 75

Need to add rank that goes until the end of the file:

Rank Firm_ID Name Contacts
1 AAA Nname 100
2 BBB Aname 85
3 CCC Cname 75
 
If you are sending the results to a report, and the report is ordered by the
descending, then you don't nee code.

Simply put a text box on the report

=(1)

Then, just set he text box "running sum" to over all.

That is it..no code at all.

If in fact, you do need to export this data, or for some reason you are NOT
using a report, then if you MUST, you can use code.


The following code does assume you create the rank field before you run it.

Dim rstData As DAO.Recordset
Dim strSql As String
Dim lngRankCount As Long

strSql = "select rankField from tblData order by Contacts desc"

Set rstData = CurrentDb.OpenRecordset(strSql)

lngRankCount = 1
Do While rstData.EOF = False
rstData.Edit
rstData!rankField = lngRankCount
rstData.Update
rstData.MoveNext
lngRankCount = lngRankCount + 1
Loop
rstData.Close
Set rstData = Nothing
 
Back
Top