Run time error 9 subscript out of range

[SJB67]

Hi all,

OK - I know I am probably being thick, but I can't see my error.

I have written a very simple sub to try to identify where I am going wrong but no joy.

I have attached a screen print showing the relevant table, module, error message and immediate window. I can not figure out what is causing the error.

Public Sub Test()
 
    Dim i As Integer
    
    Dim dbs As Database
    Dim rs As DAO.Recordset
   
    Set dbs = CurrentDb
    Set rs = db.OpenRecordset("tblSalesPayMethods")
    
    For i = 0 To rs.RecordCount - 1
    
    dubug.Print rs.Fields("Method")
    
   
   Next i
    
    rs.Close
    
    Set rs = Nothing
    Set dbs = Nothing
    
        
End Sub

It is probably something really silly ( and I will be very embarrassed), but can anyone help?

Thanks

 

[JHB]

It is probably something really silly ( and I will be very embarrassed), but can anyone help?

Thanks

Yeah - you have declared dbs as a database and have set it, but when you set the recordset you don't use dbs but db.

    Set rs = [COLOR=Red][B]db[/B][/COLOR].OpenRecordset("tblSalesPayMethods")

 

[SJB67]

that was a silly mistake - still not working but hey ho I'll keep looking.....

 

[TimW]

What about:
Debug.Print rs!Method

?

T

 

[CJ_London]

you don't advance through the recordset try

 Dim dbs As Database
Dim rs As DAO.Recordset

 Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("tblSalesPayMethods")
    
while not rs.eof    
    dubug.Print rs!Method    
    rs.movenext
wend

 

[sneuberg]

Not that it has anything to do with the error, but I don't think it's a good idea to use RecordCount before setting it by a movelast. Suggest something like.

Dim SalesPayMethodRecordCount as Long
If Not rs.EOF and Not rs.BOF then
rs.MoveLast
SalesPayMethodRecordCount = rs.RecordCount
rs.MoveFirst
Else
SalesPayMethodRecordCount = 0
'May want to do something else like exit sub
End If
For i = 0 To SalesPayMethodRecordCount - 1
.....

 

[JHB]

that was a silly mistake - still not working but hey ho I'll keep looking.....

In exact which line do you get the error?
Maybe there are no records, so look at CJ_London suggestion, which take advance of that.

 

[SJB67]

Hi All,

Thanks for all the responses, I got there in the end. For the use of others this is how:

I had to copy the code in to a new separate module. The immediate window wouldn't work properly in module behind a form.

I had to do the move first & last routine for the record count to calculate properly, then go back to first to make the move next work correctly.

Got there in the end though