Populate listbox from Access

User avatar
adam
SilverLounger
Posts: 2212
Joined: 23 Feb 2010, 12:07

Populate listbox from Access

Post by adam »

I'm trying make the following code to populate my list box from access table.

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
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 72885
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Populate listbox from Access

Post by HansV »

You should move the line

Code: Select all

    rst.Close
down to just above the line

Code: Select all

    cnn.Close
The block With rst ... End With won't work if rst has been closed.
Regards,
Hans

User avatar
adam
SilverLounger
Posts: 2212
Joined: 23 Feb 2010, 12:07

Re: Populate listbox from Access

Post by adam »

Still I'm getting the error operation not allowed when object is opened" highlighting the line
Set .ActiveConnection = Nothing

What may be the reason for this?
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 72885
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Populate listbox from Access

Post by HansV »

Remove that line. It isn't needed.
Regards,
Hans

User avatar
adam
SilverLounger
Posts: 2212
Joined: 23 Feb 2010, 12:07

Re: Populate listbox from Access

Post by adam »

Thanks. It worked fine now.
Best Regards,
Adam

User avatar
adam
SilverLounger
Posts: 2212
Joined: 23 Feb 2010, 12:07

Re: Populate listbox from Access

Post by adam »

Just another rst.close issue I'm having with the following code.
This code works as fine. But it still gives the error message "operation not allowed when object is closed" highlighting rst.close

When I comment it out the error does not come. How could I get over this?

Code: Select all

Private Sub CommandButton4_Click()
    Dim cnn         As ADODB.Connection
    Dim rst         As ADODB.Recordset
    Dim strSQL      As String
    Dim intLoop     As Integer
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Set cnn = New ADODB.Connection
    cnn.Open "Provider=Microsoft.ace.OLEDB.12.0; " & _
             "Data Source=E:\X\Database.accdb;"
    
    Set rst = New ADODB.Recordset
    
    For intLoop = 0 To Me.Me.ListBox1.ListCount - 1
        
        strSQL = "INSERT INTO Orders " _
                & "([CU Code], Code, [Department Name], [Profile Name], [Line Total]) " _
                & "Values('" & Me.Me.ListBox1.Column(0, intLoop) & "', '" _
                            & Me.ListBox1.Column(1, intLoop) & "', '" _
                            & Me.Me.ListBox1.Column(2, intLoop) & "', '" _
                            & Me.Me.ListBox1.Column(3, intLoop) & "','" _
                            & Me.Me.ListBox1s.Column(4, intLoop) & "')"
                            
    rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
    
    Next intLoop
    
    rst.Close
    cnn.Close

    Set rst = Nothing
    Set cnn = Nothing
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
     
End Sub
Best Regards,
Adam

User avatar
rory
5StarLounger
Posts: 751
Joined: 24 Jan 2010, 15:56

Re: Populate listbox from Access

Post by rory »

That code doesn't return any data so you could skip the recordset and use:

Code: Select all

cnn.execute strsql
Regards,
Rory

User avatar
HansV
Administrator
Posts: 72885
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Populate listbox from Access

Post by HansV »

In your previous macro, the SQL was for a Select query that returns data.
In this macro, the SQL is for an append query that adds records to a table; it does not return data, so you cannot use a recordset here.
Use the Execute method of the Connection object instead.

Also, you have Me.Me instead of Me several times. That makes no sense.

Code: Select all

Private Sub CommandButton4_Click()
    Dim cnn         As ADODB.Connection
    Dim strSQL      As String
    Dim intLoop     As Integer
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Set cnn = New ADODB.Connection
    cnn.Open "Provider=Microsoft.ace.OLEDB.12.0; " & _
             "Data Source=E:\X\Database.accdb;"
    
    For intLoop = 0 To Me.ListBox1.ListCount - 1
        strSQL = "INSERT INTO Orders " _
                & "([CU Code], Code, [Department Name], [Profile Name], [Line Total]) " _
                & "Values('" & Me.ListBox1.Column(0, intLoop) & "', '" _
                            & Me.ListBox1.Column(1, intLoop) & "', '" _
                            & Me.ListBox1.Column(2, intLoop) & "', '" _
                            & Me.ListBox1.Column(3, intLoop) & "','" _
                            & Me.ListBox1s.Column(4, intLoop) & "')"
                            
        cnn.Execute CommandText:=strSQL, Options:=adCmdText
    Next intLoop
    
    cnn.Close
    Set cnn = Nothing
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
Regards,
Hans

User avatar
adam
SilverLounger
Posts: 2212
Joined: 23 Feb 2010, 12:07

Re: Populate listbox from Access

Post by adam »

Thanks for the help Hans. Really appreciate it. By the way, "Me.Me" was a typho error while I was trying to modify and upload the code.
Best Regards,
Adam

User avatar
adam
SilverLounger
Posts: 2212
Joined: 23 Feb 2010, 12:07

Re: Populate listbox from Access

Post by adam »

The following code displays searched data row as a single column if the search value is in a single row.
But if the search value is in multiple rows, it displays data as multiple rows in lsitbox. How can this be solved?

Im assuming the line

Code: Select all

.List = Application.Transpose(vaData)
is causing this.

Code: Select all

Dim cnn         As ADODB.Connection
    Dim rst         As ADODB.Recordset
    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 [Code],[Line Total] FROM Orders WHERE [Serial No]=" & Me.TextBox1.Value
    rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
    
    With rst
        k = .Fields.Count
        vaData = .GetRows
    End With
    
    rst.Close
    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
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 72885
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Populate listbox from Access

Post by HansV »

Change

Code: Select all

            .List = Application.Transpose(vaData)
to

Code: Select all

            If UBound(vaData, 2) = 0 Then
                .Column = vaData
            Else
                .List = Application.Transpose(vaData)
            End If
Regards,
Hans

User avatar
adam
SilverLounger
Posts: 2212
Joined: 23 Feb 2010, 12:07

Re: Populate listbox from Access

Post by adam »

Thankyou for the help Hans.

To make it easier for the user to search from different columns, I have modified the code into another code. Everything works fine except I'm unable to count the number of rows the code has searched.

Any help would be appreciated.

Code: Select all

Private Sub CommandButton5_Click()

    Dim cnn         As ADODB.Connection
    Dim rst         As ADODB.Recordset
    Dim strSQL      As String
    Dim i           As Integer
    Dim var
    Dim n           As Long
    
    Dim vaData      As Variant
    Dim k           As Long

    On Error GoTo errHandler:

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    var = Me.txtSearch
    Set cnn = New ADODB.Connection ' Initialise the collection class variable
    cnn.Open "Provider=Microsoft.ace.OLEDB.12.0; " & _
    "Data Source=E:\X\Database.accdb;"

    If CheckBox1 = True Then
        strSQL = "SELECT [Code],[Line Total] FROM Orders WHERE [Serial No] = '" & var & "' OR [Product Name] = '" & var & "'"
    Else
        strSQL = "SELECT [Code],[Line Total] FROM Orders WHERE [Serial No] LIKE '" & var & "%' OR [Product Name] Like '" & var & "%'"
    End If
    
    Set rst = New ADODB.Recordset

    rst.Open strSQL, cnn

    If rst.EOF And rst.BOF Then

        rst.Close
        cnn.Close

        Set rst = Nothing
        Set cnn = Nothing

        Application.ScreenUpdating = True
        Application.EnableEvents = True

        MsgBox "There are no records in the recordset!", vbCritical, "No Records"
        Me.ListBox1.RowSource = ""
        Exit Sub
    End If
    
    With rst
        k = .Fields.Count
        vaData = .GetRows
    End With
    
    rst.Close
    cnn.Close
    
    With Me
        With .ListBox1
            .Clear
            .BoundColumn = k
            If UBound(vaData, 2) = 0 Then
                .Column = vaData
            Else
                .List = Application.Transpose(vaData)
            End If
            .ListIndex = -1
        End With
    End With

    MsgBox k & " records have been found", vbInformation, "Search Successful"

    On Error GoTo 0
    Exit Sub
errHandler:

    Set rst = Nothing
    Set cnn = Nothing
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Import_Data"
End Sub
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 72885
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Populate listbox from Access

Post by HansV »

The number of rows is UBound(vaData, 2) + 1
Regards,
Hans

User avatar
HansV
Administrator
Posts: 72885
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Populate listbox from Access

Post by HansV »

And k is the number of columns (fields), not the number of rows.
Regards,
Hans

User avatar
adam
SilverLounger
Posts: 2212
Joined: 23 Feb 2010, 12:07

Re: Populate listbox from Access

Post by adam »

Thankyou very much Hans. It worked great.
Best Regards,
Adam

User avatar
adam
SilverLounger
Posts: 2212
Joined: 23 Feb 2010, 12:07

Re: Populate listbox from Access

Post by adam »

I have a date field textbox and a customer name text box which I want to get saved to the table with the listbox.

But when I place the date code in the code I'm getting data type mismatch error. I did try using #" & Me.txtDate & "#"for date. but its' not working.

How can I solve this?

Code: Select all

Private Sub CommandButton4_Click()
    Dim cnn         As ADODB.Connection
    Dim strSQL      As String
    Dim intLoop     As Integer
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Set cnn = New ADODB.Connection
    cnn.Open "Provider=Microsoft.ace.OLEDB.12.0; " & _
             "Data Source=E:\X\Database.accdb;"
    
    For intLoop = 0 To Me.ListBox1.ListCount - 1
        strSQL = "INSERT INTO Orders " _
                & "([CU Code], Code, [Department Name], [Profile Name], [Line Total], [Bill Date], [CUstomer Name]) " _
                & "Values('" & Me.ListBox1.Column(0, intLoop) & "', '" _
                            & Me.ListBox1.Column(1, intLoop) & "', '" _
                            & Me.ListBox1.Column(2, intLoop) & "', '" _
                            & Me.ListBox1.Column(3, intLoop) & "','" _
                            & Me.ListBox1s.Column(4, intLoop) & "','" _
                            & Me.txtDate & "','" & Me.txtCustomerName & "')"
                            
        cnn.Execute CommandText:=strSQL, Options:=adCmdText
    Next intLoop
    
    cnn.Close
    Set cnn = Nothing
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 72885
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Populate listbox from Access

Post by HansV »

Try

Code: Select all

        strSQL = "INSERT INTO Orders " _
                & "([CU Code], Code, [Department Name], [Profile Name], [Line Total], [Bill Date], [CUstomer Name]) " _
                & "Values('" & Me.ListBox1.Column(0, intLoop) & "', '" _
                            & Me.ListBox1.Column(1, intLoop) & "', '" _
                            & Me.ListBox1.Column(2, intLoop) & "', '" _
                            & Me.ListBox1.Column(3, intLoop) & "','" _
                            & Me.ListBox1s.Column(4, intLoop) & "',#" _
                            & Format(Me.txtDate, "yyyy-mm-dd") & #','" & Me.txtCustomerName & "')"
Regards,
Hans

User avatar
adam
SilverLounger
Posts: 2212
Joined: 23 Feb 2010, 12:07

Re: Populate listbox from Access

Post by adam »

Im geting a compile error expected expression highlighting the second hashtag when I paste the code?
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 72885
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Populate listbox from Access

Post by HansV »

Sorry, forgot the double quotes.

Code: Select all

        strSQL = "INSERT INTO Orders " _
                & "([CU Code], Code, [Department Name], [Profile Name], [Line Total], [Bill Date], [CUstomer Name]) " _
                & "Values('" & Me.ListBox1.Column(0, intLoop) & "', '" _
                            & Me.ListBox1.Column(1, intLoop) & "', '" _
                            & Me.ListBox1.Column(2, intLoop) & "', '" _
                            & Me.ListBox1.Column(3, intLoop) & "','" _
                            & Me.ListBox1s.Column(4, intLoop) & "',#" _
                            & Format(Me.txtDate, "yyyy-mm-dd") & "#','" & Me.txtCustomerName & "')"
Regards,
Hans

User avatar
adam
SilverLounger
Posts: 2212
Joined: 23 Feb 2010, 12:07

Re: Populate listbox from Access

Post by adam »

Thankyou very much Hans.
Best Regards,
Adam