However it's giving me the error "operation not allowed when object is closed" highlighting the line rst. Close. When I remove the line rst.close I'm getting the error "operation not allowed when object is closed".
How could I overcome this?
Code: Select all
Private Sub CommandButton3_Click()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stConn As String
Dim strSQL As String
Dim vaData As Variant
Dim k As Long
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.ace.OLEDB.12.0; " & _
"Data Source=E:\X\Database.accdb;"
Set rst = New ADODB.Recordset
strSQL = "SELECT [Line Total] FROM Orders WHERE [Serial No]=" & Me.TexBox1.Value
rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
rst.Close
With rst
Set .ActiveConnection = Nothing
k = .Fields.Count
vaData = .GetRows
End With
cnn.Close
With Me
With .ListBox1
.Clear
.BoundColumn = k
.List = Application.Transpose(vaData)
.ListIndex = -1
End With
End With
Set rst = Nothing
Set cnn = Nothing
End Sub