Issue with Movenext statement

V

vcsphx

All, I am running into a strange issue that I have never seen before. I have
a recordset that gets it's data from a server and I am looping through the
recordset (using Do...Loop) to do few calculations. The problem is the
looping is working fine until it reaches certain point (in one case,
irrespective of what filter I use, VBA gets frozen at the 41st row when it
gets to the movenext line and in other case, it gets stuck when I started
looking at "December" month data). I am using the DAO recordset and I have
done similar looping before but this is the first time I am running into this
issue. Do you see why and how it can be fixed? Any help is appreciated!

Thanks!
 
V

vcsphx

Here is what I have:

Function reqmoves()
Dim db As Database
Set db = CurrentDb()
db.Execute "delete * from tbl1"
Dim rs As DAO.Recordset
Set rs = Nothing
Set rs = db.OpenRecordset("tbl1", dbOpenDynaset)
Dim WS As DAO.Recordset
Set WS = db.OpenRecordset("select * from table2 where field1 like
'1_area**'", dbOpenDynaset)

Do While WS.EOF = False
Dim rs1 As DAO.Recordset
db.QueryDefs("query1").SQL = "exec passthroughquery1 '" & WS!field1 &
"','Capacity'" //pass through query that gets its data from a server

Set rs1 = db.OpenRecordset("query1", dbOpenDynaset)

Dim i As Integer
Do While rs1.EOF = False
i = i + 1
If i = 40 Then
'This is when the rs1.movenext step gets frozen...it executes util that
step and it freezes there
End If
DoCmd.Echo True, "Processing WSG:" & WS!WS_group_name
rs.AddNew

//stores the data and the calculations in the tbl1

rs.Update
rs1.MoveNext
Loop
WS.MoveNext
Loop
End Function
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top