Results 1 to 4 of 4
  1. #1
    Join Date
    May 2002
    Posts
    395

    Unanswered: re: Operation is not allowed when the object is closed

    I received this error “Operation is not allowed when the object is closed.”
    This error is breaking at resume exit_lookup_qmrp_info


    I’m using AC2K and it pulls data from a server. It seems to me the error occurred due to the server is down and the code is trying to close rs_qmrp.close that does not exist which would generate the error above. But there are values in rs_qmrp, then the connection is successful and a recordset is created. Then why would it giving the error??? Can anyone see the problem.

    Function lookup_qmrp_info(work_ord_num As String, work_ord_line_num ..) As Boolean
    On Error GoTo Err_lookup_qmrp_info

    Dim qmrp As New ADODB.Connection, rs_qmrp As New ADODB.Recordset
    Dim strsql As String, line_num As String, mspexists As Boolean
    Dim rs_qmrp_mfg_ord1 As New ADODB.Recordset, rs_qmrp_mfg_ord2 As New ADODB.Recordset
    Dim rs_qmrp_work_ord As New ADODB.Recordset, connOK As Integer

    lookup_qmrp_info = True

    qmrp.Provider = "MSDASQL": .."
    qmrp.Open

    strsql = "Select ITEM_NUMBER " 'return apple_part_num
    strsql = strsql & "from OPN_ORD_LIN_ITM where ORDER_NUMBER = '" & work_ord_num & "' and LINE_NUMBER = '" & work_ord_line_num & "';"
    rs_qmrp_work_ord.Open strsql, qmrp

    If Not (IsNull(rs_qmrp_work_ord(0))) And Not rs_qmrp_work_ord.EOF Then
    apple_part_num = Trim(rs_qmrp_work_ord(0))
    Else
    lookup_qmrp_info = False
    Exit Function
    End If
    rs_qmrp_work_ord.close

    strsql = "Select CUSTOMER_NUMBER, DATE_ORDERED, …. from "
    strsql = strsql & "OPN_ORD_HDR inner join OPN_ORD_LIN_ITM on "
    strsql = strsql & "(OPN_ORD_HDR.ORDER_NUMBER = OPN_ORD_LIN_ITM.ORDER_NUMBER) "
    strsql = strsql & "where OPN_ORD_HDR.ORDER_NUMBER = '" & work_ord_num & "' and LINE_NUMBER = '" & work_ord_line_num & "' and ITEM_NUMBER = '" & apple_part_num & "';"

    rs_qmrp.Open strsql, qmrp
    If Not rs_qmrp.EOF Then
    cust_num = Trim(rs_qmrp(0))
    date_ordered = Trim(rs_qmrp(1))
    cust_ord_due = Trim(rs_qmrp(2))
    cust_name = Trim(rs_qmrp(3))
    cust_po = Trim(rs_qmrp(4))
    cust_ord_qty = Trim(rs_qmrp(7))
    Else
    lookup_qmrp_info = False
    Exit Function
    End If

    Call get_qmrp_qtys(qmrp, apple_part_num, qty_on_hand, qty_committed, apple_catalog_num)
    Call parse_catalog_num(apple_catalog_num, material, vendor)

    mspexists = get_qmrp_msp_info(qmrp, work_ord_num, apple_part_num, mfg_ord_num, mfg_qty, mfg_start_date, qty_completed)


    exit_lookup_qmrp_info:
    rs_qmrp.close

    If rs_qmrp_mfg_ord1.State = adStateOpen Then
    rs_qmrp_mfg_ord1.close
    End If

    If rs_qmrp_mfg_ord2.State = adStateOpen Then
    rs_qmrp_mfg_ord2.close
    End If

    Set qmrp = Nothing
    Exit Function

    Err_lookup_qmrp_info:
    MsgBox err.Description
    ‘’’Error occurred here
    à Resume exit_lookup_qmrp_info


    End Function

  2. #2
    Join Date
    Jan 2005
    Posts
    7
    I'm guessing the initial error that forces the error trap happens at this line...

    Code:
    rs_qmrp.Open strsql, qmrp
    If Not rs_qmrp.EOF Then
    Testing for EOF if the object is not open will kick out this error. Just code it defensively, like you have in your other handlers...

    Code:
    rs_qmrp.Open strsql, qmrp
    
    If rs_qmrp.State = adStateOpen Then
        If Not rs_qmrp.EOF Then
    Add this additional test to your exit handler as well, should sort it!

  3. #3
    Join Date
    May 2002
    Posts
    395

    re: Operation is not allowed when the object is closed

    Hello Dylan,

    Thanks for your reply and suggestions.
    If the rs_qmrp is not created wouldn't it be the same as rs_qmrp at EOF?
    { If rs_qmrp.State = adStateOpen Then
    If Not rs_qmrp.EOF Then }
    Thanks!

  4. #4
    Join Date
    Jan 2005
    Posts
    7
    Quote Originally Posted by alicejwz
    If the rs_qmrp is not created wouldn't it be the same as rs_qmrp at EOF?!
    No, they're different - you can do a very simple test to verify this.

    Example 1: This will generate your error at oADO.EOF - The recordset is not connected, so it can't test to see if it's at the end of file...

    Code:
    Private Sub Form_Load()
    
        Dim oADO As ADODB.Recordset
        
        Set oADO = New ADODB.Recordset
        
        If oADO.EOF Then
    
        End If
    
    End Sub
    In this code, it'll skip the EOF test because adStateOpen will return false.

    Code:
    Private Sub Form_Load()
    
        Dim oADO As ADODB.Recordset
        
        Set oADO = New ADODB.Recordset
        
        If oADO.State = adStateOpen Then
            If oADO.EOF Then
    
            End If
        End If
    
    End Sub
    Only use EOF when you know you're object is created & connected....!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •