How to do this query?

  • Thread starter Thread starter fl
  • Start date Start date
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
 
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
 
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.
 
Back
Top