Ranking Help

[dnelson24]

Given the code below how would I code this so that if the select query "MGW6Store - Acc Select" has no records my macro would just continue on with out stopping with a run=time error box?

Thanks David



Function MGWacc()

Dim MyDB As Database
Dim MySet As Recordset
Dim I As Integer
Dim R As Integer

Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDB.OpenRecordset("select * from [MGW6Store - Acc Select] order by [Acc%Sales] DESC", DB_OPEN_DYNASET)

MySet.MoveLast
I = MySet.RecordCount
R = 12

If MySet.RecordCount > 0 Then
MySet.MoveFirst
For I = 1 To MySet.RecordCount
MySet.Edit
MySet![Acc Rank] = (R - 2)
MySet.Update
MySet.MoveNext
R = R - 2
Next
End If

End Function

 

[jbpelletier]

Add this "magical" line of code right before the 1st instruction of your sub

ON ERROR RESUME NEXT

jb

 

[VBslammer]

Seems to me you should check the record count before doing anything else:

     'check for records first     
     If MySet.RecordCount > 0 Then

          'then do stuff
          MySet.MoveLast
          I = MySet.RecordCount
          R = 12
          MySet.MoveFirst

          'now iterate
          For I = 1 To MySet.RecordCount
            MySet.Edit
             MySet![Acc Rank] = (R - 2)
             MySet.Update
             MySet.MoveNext
          R = R - 2
          Next
     End If

VBSlammer

Unemployed in Houston, Texas

 

[PHV]

And what about testing MySet.EOF just after the OpenRecordset call ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244