Operation is not allowed when the object is closed

[alicejwz]

I received the message above after executing a stored procedure from my ado code. The error appeared in the recordcount property of the If statement. Here is how my sub routine looks like:

Private Sub w_o__history_Click()

On Error GoTo Err_w_o_history_Click

Dim ret_val As Integer
Dim check_wo_history As New ADODB.Command
Dim rs_check_wo_history As New Recordset
Dim work_ord_num As String, work_ord_line_num As String

Call load_const

With check_wo_history
.ActiveConnection = CurrentProject.Connection
.CommandText = "spCheck_wo_history"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
''''.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, mfg_ord_num)
.Parameters.Append .CreateParameter("@work_ord_num", adChar, adParamInput, work_ord_num_length, Me!shipping_sched_list_subform.Form!work_ord_num.Value)
.Parameters.Append .CreateParameter("@work_ord_line_num", adChar, adParamInput, 3, Me!shipping_sched_list_subform.Form!work_ord_line_num.Value)
Set rs_check_wo_history = .Execute
End With

''''ERROR OCCURRED ON THE NEXT LINE OF CODE
If rs_check_wo_history.RecordCount > 0 Then
DoCmd.OpenForm "shipment_hist_list"
Else
MsgBox "There are no shipments found for Work Order Number: " & work_ord_num & " ", vbExclamation
end if
end sub

Thanks in advance.

 

[Mile-O]

Do you not get an Undefined Label error for having this line:

On Error GoTo Err_w_o_history_Click


but no error handling to go with it?




I don't use ADO so I can't advise on that but, although it's okay (though not advised) to give names to things with underscores separating words, it certainly isn't advised to be putting symbols (i.e. @) into names.


Also, at this point:

    If rs_check_wo_history.RecordCount > 0 Then 
        DoCmd.OpenForm "shipment_hist_list" 
    Else 
        MsgBox "There are no shipments found for Work Order Number: " & work_ord_num & " ", vbExclamation 
    End if

you have dimensioned work_ord_num as a String but your code will through up an error as you are trying to use the variable as if it were a numerical value (integer, long, etc.).

    If rs_check_wo_history.RecordCount > 0 Then 
        DoCmd.OpenForm "shipment_hist_list" 
    Else 
        MsgBox "There are no shipments found for Work Order Number: """ & work_ord_num & """", vbExclamation 
    End if

is more advisable.

 

[Mile-O]

Another thing that would be helpful is to say what the error was: not just that it was an error but to describe what Access says about it - that way those who potentially can help can understand the full extent of the problem and don't have to guess.

 

[alicejwz]

Sorry!The label is defined near the end of the subroutin, I meant to copy the code that might caused the error. You are probably right about work_ord_num dimensioned as a string and use the variable as a numerical value. But the error("Operation is not allowed when the object is closed" ) occurred before that in the If statement.
If rs_check_wo_history.RecordCount > 0 Then
DoCmd.OpenForm "shipment_hist_list"
Else
MsgBox "There are no shipments found for Work Order Number: " & work_ord_num & " ", vbExclamation
End if

I believe this is what my code doing- Created a recordset and populated the recordset. But the problem lies in obtaining a record count of the recordset. What object is Access referring that needs to be opened. The connection object? If there is no connection there wouldn't be return value after executing the stored procedure. The recordset? I also tested and set the recordset to open and still get error"Operation is not allowed when the object is closed"
Hope you can help.
Thanks!