Filtering two listbox with each others Entries

Posted on 2009-12-16
Last Modified: 2013-11-28
I have two List boxes: listbox0 is Customer name; listbox2 is Plant name. Right now I have it setup where a user selects "clicks" a customer name in listbox0 and in listbox2 the Plant names that are associated with that Customer name will show. Listbox2 is blank until a Customer is chosen in Listbox0.

My problem is that sometimes the user doesn't know the Customer name and only knows the Plant name. So I need to have all Customer names shown in listbox0 and all Plant names shown in Listbox2 until one is selected in either Listbox, where then the other would show the utility name or the list of available plant names.

Right now, for Listbox0 (Customer Names) I have the row source as "SELECT [customer names] FROM [qryprojectnumbers];      [qryprojectnumbers] contains project numbers and Customer name, Plant name...etc. associated with it.
 For Listbox2, I have the row source as SELECT [Plantname] from [qryprojectnumbers] WHERE qryprojectnumber.customer = [list0] or [list0] is null
but when nothing is selected in Listbox0, Listbox2 is still empty.
How should I set the query to make all plant names show unless Listbox0 is not null?? and vice versa?

Thanks for the help
Question by:wtrimble
    LVL 39

    Accepted Solution

    I would use this row source for Listbox2:
    SELECT [Plantname] from [qryprojectnumbers]

    Then in the after update event of Listbox0 put:
    Private Sub Listbox0_AfterUpdate ()
    Listbox2.RowSource = "SELECT [Plantname] from [qryprojectnumbers] WHERE qryprojectnumber.customer = [list0]"
    End Sub

    Author Comment

    I tried your code and it said " Error: Object required" highlighting the listbox2.rowsource row. Is there something else that needs to be put in?? Also there will be more than one plant name per customer, will that matter with your method?
    LVL 13

    Assisted Solution


    You can try building dynamic SQL. The statement being built depends upon list0.value. If it is 0, build 1 = 1. Else build customer name = list0. It should be easy to implement.

    LVL 39

    Expert Comment

    Listbox2.RowSource = "SELECT [Plantname] from [qryprojectnumbers] WHERE qryprojectnumber.customer = [list0]"

    should be one line. Is that the problem?

    Private Sub Listbox0_AfterUpdate ()
    Listbox2.RowSource = "SELECT [Plantname] from [qryprojectnumbers] WHERE qryprojectnumber.customer = [list0]"
    End Sub

    Open in new window


    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    746 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

    15 Experts available now in Live!

    Get 1:1 Help Now