Run Time error - 2147467259 (80004005)

[murthyspd]

Hi
I am facing a problem with following code

Private Sub cmdMoveNext_Click()
rsChangeRequest.MoveNext
If rsChangeRequest.EOF Then
rsChangeRequest.MoveLast
End If
end sub

It gives a run time error -2147467259 (80004005) on rsChangeRequest.MoveNext

The whole process works like this
1. rsChangeRequest has a set of records using SQL - SELECT * FROM tbl_change_req_list LEFT JOIN tblAllClarity_Master on tbl_change_req_list.ClarityID = tblAllClarity_Master.Clarity WHERE Reviewed IS NULL ORDER BY CR_Number", using the ADODB connection.

2. After updating another table from rsChangeRequest, the record is deleted using docmd.runsql , the rsChangeRequest recordset is closed and opened again with the same SQL using ADODB.
3. When I click on "Next" button, the above set of vba is executed but I get the run time error.
The error description is : Object invalid or no longer set

I have not set the recordset to Nothing anywhere. Any idea what is causing this ? Are using ADODB and docmd.runsql creating any issue ?

 

[Yonnel]

Hi!

It seems that your program doesn't recognize the recorset

How is it defined? As global?


-----
Lionel Garnier
http://www.gylsolutions.fr - IT consulting, VBA, Access trainings, Excel trainings, PowerPoint trainings

 

[murthyspd]

It's global to the module.

I have a question though. If I close rsChangeRequest recordset, do I need to Set it again like this :
Set rsChangeRequest = New ADODB.Recordset

Regards
Murthy

 

[Yonnel]

Difficult to answer without the complete code but:
Maybe you should replace the code by

Private Sub cmdMoveNext_Click()
 If Not rsChangeRequest.EOF Then
  rsChangeRequest.MoveLast
 Else
  rsChangeRequest.MoveNext
 End If
End Sub

=> The rsChangeRequest.MoveNext will only be executed when not at the end of the recordset.

If you then have an issue with the rsChangeRequest.MoveLast, it is most probably that your recordset in empty...
In that case, please post the part of the code where you re-open the recordset!

-----
Lionel Garnier
http://www.gylsolutions.fr - IT consulting, VBA, Access trainings, Excel trainings, PowerPoint trainings

 

[murthyspd]

Here's the code :

Option Explicit
Dim rsChangeRequest As ADODB.Recordset
Dim strSQL As String

----------------------------------------------------------------------------------------
Private Sub Form_Load()

Dim strSQL As String
Dim rs As ADODB.Recordset

Set rsSharePoint = New ADODB.Recordset

Call Main
ReviewCRQ "No CRQs to review"

End Sub
-------------------------------------------------------------------------------------------
Private Sub ReviewCRQ(msg As String)


Dim strSQL As String


strSQL = "SELECT * FROM tbl_change_req_list " & _
"LEFT JOIN tblAllClarity_Master on tbl_change_req_list.ClarityID = tblAllClarity_Master.Clarity " & _
"WHERE Reviewed IS NULL ORDER BY CR_Number"

Set rsChangeRequest = New ADODB.Recordset


If rsChangeRequest.State = 1 Then rsChangeRequest.Close



rsChangeRequest.Open strSQL, conn, adOpenKeyset, adLockOptimistic


If rsChangeRequest.RecordCount > 0 Then
rsChangeRequest.MoveFirst
DisplayPositionOfRecord rsChangeRequest.AbsolutePosition, rsChangeRequest.RecordCount
DisplayChangeRequestDetails
Else
MsgBox msg, vbInformation, "Change Requests"
End If

End Sub
-----------------------------------------------------------------------------------------------

Private Sub cmdSave_Click()


Dim rsSharePoint As ADODB.Recordset


rsSharePoint.Open "SELECT * FROM TblChange_Requests", conn, adOpenKeyset, adLockOptimistic


With rsSharePoint

.AddNew

txtCRNumber.SetFocus
![CR Number] = txtCRNumber.Text

txtSummary.SetFocus
!Summary = txtSummary.Text
end with

DeleteRecordFromtbl_change_req_list

ReviewCRQ "No more CRQs to review" <<<<The code is above
-------------------------------------------------------------------------------------------------------
Private Sub DeleteRecordFromtbl_change_req_list()


Dim rsDELETE As ADODB.Recordset


Set rsDELETE = New ADODB.Recordset

DoCmd.RunSQL "DELETE FROM tbl_change_req_list WHERE CR_Number = '" & txtCRNumber.Value & "'"

End Sub
---------------------------------------------------------------------------------------------------
Private Sub cmdMoveNext_Click()


rsChangeRequest.MoveNext
If rsChangeRequest.EOF Then
rsChangeRequest.MoveLast
End If

DisplayPositionOfRecord rsChangeRequest.AbsolutePosition, rsChangeRequest.RecordCount

DisplayChangeRequestDetails

End Sub

 

[Yonnel]

I think that the problem comes from the fact that you declare several times recordsets without closing them.
In addition and some of them are not used (maybe because I see only a part of the code).

What I would suggest is to:

1. Declare the recordsets at the top of your module (so all recordsets are global)
2. Initialize the new recordset in the form load (to do it only once)
3. Close and empty memory on form close.
4. Remove the variant declarations that are duplicated or not used! (example: strSQL defined as global, defined in form load but not used and defined in the other functions..)

This would give something like this:

Option Explicit

Dim rsChangeRequest As ADODB.Recordset
Dim rsSharePoint As ADODB.Recordset
Dim strSQL As String

----------------------------------------------------------------------------------------
Private Sub Form_Load()

Set rsChangeRequest = New ADODB.Recordset
Set rsSharePoint = New ADODB.Recordset

Call Main
ReviewCRQ "No CRQs to review"

End Sub
-------------------------------------------------------------------------------------------
Private Sub ReviewCRQ(msg As String)

strSQL = "SELECT * FROM tbl_change_req_list " & _
  "LEFT JOIN tblAllClarity_Master on tbl_change_req_list.ClarityID = tblAllClarity_Master.Clarity " & _
  "WHERE Reviewed IS NULL ORDER BY CR_Number"

If rsChangeRequest.State = 1 Then rsChangeRequest.Close
rsChangeRequest.Open strSQL, conn, adOpenKeyset, adLockOptimistic


If rsChangeRequest.RecordCount > 0 Then
  rsChangeRequest.MoveFirst
  DisplayPositionOfRecord rsChangeRequest.AbsolutePosition, rsChangeRequest.RecordCount
  DisplayChangeRequestDetails
Else
  MsgBox msg, vbInformation, "Change Requests"
End If

End Sub
-----------------------------------------------------------------------------------------------

Private Sub cmdSave_Click()

If rsSharePoint.State = 1 Then rsSharePoint.Close
rsSharePoint.Open "SELECT * FROM TblChange_Requests", conn, adOpenKeyset, adLockOptimistic

With rsSharePoint
  .AddNew
  txtCRNumber.SetFocus '(I am not sure that this is needed)
  ![CR Number] = txtCRNumber.Text
  txtSummary.SetFocus '(I am not sure that this is needed)
  !Summary = txtSummary.Text
End With

DeleteRecordFromtbl_change_req_list

ReviewCRQ "No more CRQs to review" '<<<<The code is above

End Sub
-------------------------------------------------------------------------------------------------------
Private Sub DeleteRecordFromtbl_change_req_list()

'Dim rsDELETE As ADODB.Recordset 'Not used!
'Set rsDELETE = New ADODB.Recordset 'Not used!

DoCmd.RunSQL "DELETE FROM tbl_change_req_list WHERE CR_Number = '" & txtCRNumber.Value & "'"

End Sub
---------------------------------------------------------------------------------------------------
Private Sub cmdMoveNext_Click()


rsChangeRequest.MoveNext
If rsChangeRequest.EOF Then
  rsChangeRequest.MoveLast
End If

DisplayPositionOfRecord rsChangeRequest.AbsolutePosition, rsChangeRequest.RecordCount

DisplayChangeRequestDetails

End Sub

 

[steveboydon]

Surely is it not easier to test for

if rst.absoluteposition = rst.recordcount - 1 then

Basically you have forgotten to add the minius one at the end. Basically checking before you fall off the end of the last record.

Does that sound ok

Steve


blah blah