Runtime Error 91; Object variable or with variable.

[adam]

Hi anyone,

I'm getting runtime error 91 by Highlighting the line. How could I overcome this?

Code: Select all

rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText

Following is the full code. Any help would be kindly appreciated.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cnn         As ADODB.Connection
    Dim rst         As ADODB.Recordset
    Dim ce          As Range
    Dim strSQL      As String
    Dim bFound      As Boolean
    Dim strConn     As String
    Dim rCell       As Range
    
    If Not Intersect(Range("J5"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Set cnn = New ADODB.Connection
        cnn.Open "Provider=Microsoft.ace.OLEDB.12.0; " & _
                 "Data Source=C:\NEW\Sample.accdb;"
        Set rst = New ADODB.Recordset
        Range("A16:G542,I16:I542,D7,H5,H7,H9,H11,H13,J7,J9,J11,J13,D5,U5,T3,T7,T9").ClearContents
        Range("C16:C632").EntireRow.Hidden = True
        If Range("J5").Value <> "" Then
            strSQL = "SELECT [Accession No] FROM tblRDetails WHERE [Accession No]=" & Range("J5").Value
            rst.Open Source:=strSQL, ActiveConnection:=cnn, Options:=adCmdText
            bFound = Not rst.EOF
            rst.Close
            
            If bFound Then
                MsgBox "This record already exists. ", vbExclamation
                Range("J5").ClearContents
            End If
        End If
    End If
    
    If Range("J5").Value <> "" Then
        strSQL = "SELECT tblSCategories.[Profile Name],[Test Name] FROM tblODetails INNER JOIN tblSCategories On tblODetails.Code=" & _
                 "tblSCategories.Code WHERE tblODetails.[Serial No]=" & Range("J5").Value
        rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
        Range("C16").CopyFromRecordset rst
        rst.Close
    End If
    
    If Range("J5").Value <> "" Then
        strSQL = "SELECT * FROM tblMDetails WHERE [Serial No]=" & Range("J5").Value
        rst.Open Source:=strSQL, ActiveConnection:=cnn, Options:=adCmdText
        Range("D5").Value = rst![Customer ID]
        Range("H5").Value = rst![Bill No]
        rst.Close
    End If
    
    If Range("J7").Value <> "" Then
        
        Set cnn = New ADODB.Connection
        strConn = "provider=sqloledb.1;data source=MYSERVER;" & _
                  "user id=adam;password=adam123;initial catalog = ADM_B2_8"
        cnn.Open ConnectionString:=strConn
        
        Set rst = New ADODB.Recordset
        strSQL = "SELECT * FROM tInfo WHERE [tIdx]=" & Range("J7").Value
        rst.Open Source:=strSQL, ActiveConnection:=cnn, Options:=adCmdText
        Range("H7").Value = rst![ID]
        Range("D7").Value = rst![tName]
        rst.Close
    End If
    
    If Range("F3").Value <> "" And Range("D13").Value <> "" Then
        Range("D16:D632").Offset(0, 5).ClearContents
        
        Set cnn = New ADODB.Connection
        cnn.Open "Provider=Microsoft.ace.OLEDB.12.0; " & _
                 "Data Source=C:\NEW\Sample.accdb;"
        
        Set rst = New ADODB.Recordset
            For Each ce In Range("D16:D632")
                If ce.Value <> "" Then
                    strSQL = "SELECT [Price Range] FROM RRange WHERE [Product Name]='" & _
                            ce.Value & "' AND [Min Age]<=" & Range("F3").Value & _
                            " AND [Max Age]>=" & Range("F3").Value & " AND Gender='" & _
                            Range("D13").Value & "'"
                    rst.Open Source:=strSQL, ActiveConnection:=cnn, _
                             CursorType:=adOpenStatic, Options:=adCmdText
                    If Not rst.EOF Then
                        ce.Offset(0, 5).Value = rst.Fields("Price Range")
                End If
                rst.Close
            End If
        Next ce
    End If
    
    For Each rCell In Range("C16:C632")
        If rCell <> "" Then rCell.EntireRow.Hidden = False
    Next rCell
    
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

[HansV]

I am afraid I don't see an immediate reason for this error, I would have to see the workbook ad Access database.

[adam]

This error comes if I type a text or value in any cell of the worksheet. It does not occur when the code gets data from access database.

Let's say for example If I write any value in any cell of the sheet; before running the code I get the Object variable error highlighting the last
cnn.Close line.

But if I write any value in any cell of the sheet; after running the code I get the Object variable error highlighting the line
rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText

[LisaGreen]

Adam,

Try commenting out Application.ScreenUpdating = False.

Lisa

[LisaGreen]

Annnd ... maybe putting the set cnn under the Dims.

Lisa

[adam]

Thankyou very much for the help Lisa. Works fine now.