Cycling through a Multi Select ListBox with many selected items VBA ACCESS

Posted on 2006-05-29
Last Modified: 2008-01-09
This is the problem I am having... I have a form which has a MULTI-SELECT ( Simple ) listbox and a textbox.

Right now I have this on the 'Click' event. Not sure if this is correct event.

Basically I want to be able to highlight an invoice number or many invoice numbers from the listbox and have the total that is associated with those invoice numbers be placed in the textbox.

The problem I am having is the only the total for the first selected invoice number is being read and nothing after.

How do I cycle through a listbox so that all the selected items are read?? And after all the selected invoice numbers are read I want the grand total of those invoices to be placed in the textbox.

I think have to read the first selected index and then exit out of the loop and then read the next selected index..but I am a bit fuzzy about how to do it.

I have code that reads the selected item ..

      For intIndex = lstbx_status.ListCount - 1 To 0 Step -1
          If lstbx_status.Selected(intIndex) Then
             lnInvoiceNumber = lstbx_status.Column(0, intIndex)
          End If

but it doesn't only reads the first highlighted item from the listbox.

Any help or insight would be much appreciated.
Question by:nomar2
    LVL 16

    Accepted Solution

    First, one should use the AfterUpdate event instead of the OnClick Event
    Second, since you are only interested in the sum of items selected you don't need to loop thru the entire list box.  Instead there is a collection named .ItemsSelected ready made for this purpose.

    Code illustrated below.  I'm assuming you're trying to output the total to a Text Box named "lnInvoiceNumber".

    Hope this helps.


    Private Sub lstbx_status_AfterUpdate()
        Dim varItem As Variant
        Dim curTotal As Currency
        With Me.lstbx_status
            For Each varItem In .ItemsSelected
                curTotal = curTotal + Nz(.Column(0, varItem),0)
            Next varItem
        End With
        Me.lnInvoiceNumber = curTotal
    End Sub

    Author Comment

    Wicked!!! That worked perfect!!!!!
    LVL 16

    Expert Comment

    Glad I could help.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now