Cursor is not valid error

[Regdod]

This is the process of my code

sql1 = select employeeID from employees where dept = 34
Set recordset1 = workspace.OpenRecordset(Sql1,dbOpendynamic)

recordset1.Movefirst

Do while not recordset.BOF and Not recordset.EOF
call processInfo
recordset1.MoveNext ====>>>this is where I get =========================>>>error saying cursor not valid

Loop



Public Function ProcessInfo
sql2 = select * from infotable where employeeId = 1234
recordset2 = workspace.openrecordset(sql2,dbopendynamic)

recordset2.MoveFirst
Do while not bof and not eof
calculate totals
recordset2.MoveNext
Loop
sql3 = insert into temporary table values(totals)
execute
End Function

 

[nealvenables]

Try...

sql1 = "select employeeID from employees where dept = 34"

Set recordset1 = workspace.OpenRecordset(Sql1,dbOpendynamic)

if recordset1.eof
msgbox "No data was returned"
exit sub
end if

Do until recordset1.eof
call processInfo
recordset1.MoveNext
Loop

 

[Regdod]

one thing I forgot to mention is that when I step through the code it processs 3 records, the first 2 dont require the insert into temp table, so it go through both sets of loops twice. On the third record it requires and insert and then when it returns to the first module it gives me the cursor not valid error.

what do i need to do to keep it moving through all records of recordset1 when many recordset2 records will be inserted into the temp table
thanks for your help

 

[nealvenables]

Can you post the code in full? Will make it easier for me to visualise what processes are running...

 

[Regdod]

Public Function ProcessReportInfo(StrEmpNum, StrLast, StrFirst)
Dim StrDeptAbs As String
Dim StrDeptTot As String
Dim RstDeptVac As Recordset
Dim RstDeptAbs As Recordset
Dim RstDeptTot As Recordset
Dim StrDeptNum As String
Dim StrDeptDate As String
Dim DeptCount As Integer
Dim VacCount As Integer
Dim AbsCount As Integer
Dim DeptLeftover As String
Dim DeptEarned As String
Dim DeptAccrued As String
Dim DeptTotal As String
Dim MyDb As Database
Dim AbsCode As String
Dim TotTotal As String
Dim DeptEmpName As String
Dim RstInsertDept As Recordset
Dim StrInsertDept As String
Dim QdfInsert As QueryDef
Dim WrkspcInsert As Workspace




StrDeptDate = #1/1/2003#
Set MyDb = CurrentDb

StrDeptTot = " Select Name, Leftover, Earned2003, AccruedHours, TotalHours From TotVacation where ID = " & StrEmpNum & ""
Set RstDeptTot = MyDb.OpenRecordset(StrDeptTot, dbOpenSnapshot)

If Not RstDeptTot.BOF And Not RstDeptTot.EOF Then
DeptEmpName = RstDeptTot!Name
DeptLeftover = RstDeptTot!Leftover
DeptEarned = RstDeptTot!Earned2003
DeptAccrued = RstDeptTot!AccruedHours
DeptTotal = RstDeptTot!TotalHours

StrDeptAbs = "SELECT Absence.EmpNum, Absence.AbsDate, Absence.AbsCode, Absence.WorkShift, Absence.AbsHours " & _
"FROM ABSENCE WHERE (((Absence.EmpNum)= " & StrEmpNum & &quot AND(((Absence.AbsDate)>'" & StrDeptDate & "') AND ((((Absence.AbsCode)=87))" & _
"OR (((Absence.AbsCode)=88)) OR (((Absence.AbsCode)=89)) OR (((Absence.AbsCode)=90)) OR (((Absence.AbsCode)=91)))))" & _
"Order by AbsDate Desc"

' MsgBox " Sql Looks like " & StrDeptAbs & " This", vbOKOnly


Set RstDeptAbs = gconAbs.OpenRecordset(StrDeptAbs, dbOpenDynamic)

If Not RstDeptAbs.BOF And Not RstDeptAbs.EOF Then

DeptCount = 1
Do While DeptCount <= RstDeptAbs.RecordCount

If Not IsNull(RstDeptAbs!AbsHours) Then


AbsCode = RstDeptAbs!AbsCode

Select Case AbsCode

Case 87
DeptLeftover = DeptLeftover - RstDeptAbs!AbsHours
Case 88
DeptEarned = DeptEarned - RstDeptAbs!AbsHours
TotDept03Used = TotDept03Used + RstDeptAbs!AbsHours
Case 89
DeptEarned = DeptEarned - RstDeptAbs!AbsHours
TotDept03Cashed = TotDept03Cashed + RstDeptAbs!AbsHours
Case 90
DeptAccrued = DeptAccrued - RstDeptAbs!AbsHours
TotDeptAccUsed = TotDeptAccUsed + RstDeptAbs!AbsHours
Case 91
DeptAccrued = DeptAccrued - RstDeptAbs!AbsHours
TotDeptAccCashed = TotDeptAccCashed + RstDeptAbs!AbsHours


End Select


DeptTotal = DeptTotal - RstDeptAbs!AbsHours



Else
End If
DeptCount = DeptCount + 1
RstDeptAbs.MoveNext

Loop

StrInsertDept = &quot;Insert Into tblTempDeptVac (&quot; & StrEmpNum & &quot; ,&quot; & StrLast & &quot;,&quot; & StrFirst & &quot;, &quot; & _
&quot; &quot; & DeptLeftover & &quot;,&quot; & DeptEarned & &quot;,&quot; & TotDept03Used & &quot;,&quot; & TotDept03Cashed & &quot;, &quot; & _
&quot; &quot; & DeptAccrued & &quot;,&quot; & TotDeptAccUsed & &quot;,&quot; & TotDeptAccCashed & &quot;,&quot; & DeptTotal & &quot&quot;


If Not RunSql(StrInsertDept) Then
MsgBox &quot;Error Has Occured entering info into Temp Table&quot;, vbOKOnly
Else
Call ClearAllTotals
End If

Else
' StrInsertDept = &quot;Insert into Vacation Values ( '1','Reno','3','4','5')&quot;
' StrInsertDept = &quot;Insert into tblTempDeptVac Values ('1234','smith','mary','1','2','3','4','5','6','7','8')&quot;

StrInsertDept = &quot; Insert into tblTempDeptVac Values (' &quot; & StrEmpNum & &quot; ',' &quot; & StrLast & &quot; ',' &quot; & StrFirst & &quot; ',&quot; & _
&quot; ' &quot; & DeptLeftover & &quot; ',' &quot; & DeptEarned & &quot; ','0','0', ' &quot; & DeptAccrued & &quot; ','0','0',' &quot; & DeptTotal & &quot; ' )&quot;
End If




If Not RunSql(StrInsertDept) Then
MsgBox &quot; Looks like &quot; & StrInsertDept & &quot; this&quot;, vbOKOnly
MsgBox &quot;Error Has Occured. Temp Table 002&quot;, vbOKOnly
Else
Call ClearAllTotals
End If
End If


End Function


Public Function GetEmployeeFromDeptNum()


Dim StrDeptVac As String
Dim RstDeptVac As Recordset
Dim StrDeptNum As String
Dim StrEmpNum As String
Dim StrLast As String
Dim StrFirst As String




Dim VacCount As Integer


StrDeptNum = GetDeptNumber
If Not DbConnect(False) Then ' force a reconnect
MsgBox &quot;Connection Lost, Please Contact IT&quot;, vbOKOnly
Else

StrDeptVac = &quot;Select EmpNum, LastName, FirstName From Employees where Dept = &quot; & StrDeptNum & &quot;&quot;

Set RstDeptVac = gconAbs.OpenRecordset(StrDeptVac, dbOpenDynamic)


If RstDeptVac.BOF And RstDeptVac.EOF Then
MsgBox &quot; Cannot find employees for this Department &quot; & StrDeptNum & &quot; Try Again&quot;, vbOKOnly

Else
RstDeptVac.MoveFirst
If Not RstDeptVac.EOF And Not RstDeptVac.BOF Then
Do While Not RstDeptVac.EOF And Not RstDeptVac.BOF

VacCount = 1
StrEmpNum = RstDeptVac!EmpNum
StrLast = RstDeptVac!LastName
StrFirst = RstDeptVac!FirstName

Call ProcessReportInfo(StrEmpNum, StrLast, StrFirst)

RstDeptVac.MoveNext
VacCount = VacCount + 1
Loop
Else

End If
End If
End If

End Function

 

[nealvenables]

OK, bit of a stab in the dark this, but &quot;Cursor Not Valid&quot; normally indicates that a server-side process has updated the underlying data and closed your recordset cursor.

I can't see where this might be happening, but the problem may go away if you change your recordsets from dbOpenDynamic to dbOpenSnapshot as these are less sensitive to changes on the server side.

Alternatively, &quot;cursor not valid&quot; is an ODBC specific error - you may make the problem go away by switching to ADO. At the very least this would give you a different set of error codes that might help further illuminate what's going on

 

[Regdod]

Originally I did have dbOpenSnapshot

Thanks for your help