How to do this query?

F

fl

I have the following table:

Name Service Area
------ --------------
User-A Area-1
User-A Area-2
User-A Area-3
User-B Area-3
User-C Area-3
User-D Area-2
User-D Area-4
User-F Area-1
User-F Area-2
User-F Area-3
User-G Area-2
User-G Area-4
.... ...

When I select any Name (from a list box which is the result for Name
from a query), I would like to also get Name(s) who serve the same Area
(and output these names in another list box). Example, when I select
User-A, I will output User-F and whoever that have the same Service Area
which is Area-1, Area-2 and Area-3.

Thank you for your help.

Faye
 
D

Dale Fye

Don't remember if you should use the AfterUpdate or Click event of the first
listbox, but in one of those two, you need to requery listbox #2, something
like.

Private Sub list_User_Svc_Areas_Click()

me.list_Other_Users.requery

end sub

Then, the query for the second list box should look something like:

SELECT Name, Service_Area FROM yourTable WHERE ServiceArea =
me.List_User_Svc_Areas.Column(1)

this assumes that you only have two columns in your listbox (the column
property is zero based).

HTH
Dale
 
J

John Spencer (MVP)

The rowsource for the second list box would use a subquery and would probably be
something like:

SELECT [Name]
FROM TheTable
WHERE [Service Area] IN
(SELECT Tmp.[Service Area]
FROM TheTable as Tmp
WHERE Tmp.[Name] = [Forms]![YourFormName]![FirstListboxName])

You would need to requery the second list box in the update event of the first listbox.
 
Top