Filtering two listbox with each others Entries

Posted on 2009-12-16
Medium Priority
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
  • 2
LVL 39

Accepted Solution

thenelson earned 1600 total points
ID: 26064418
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

ID: 26064574
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

sameer2010 earned 400 total points
ID: 26064598

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

ID: 26064695
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

850 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