Link to home
Get AccessLog in
Avatar of James0903
James0903

asked on

Select value in drop-down, runs query, returns results

Hello.  I used your service years ago and loved it.  I have returned to use again.  Need your help on this one please.  It has been years since I worked in Access 2007, so I am very rusty, so please bear with me.  I work in a law firm.  We have 200+ lawyers.  Each lawyer can be assigned multiple cases at the same time.  In my Access main table my data is stored.  I created a form with a drop-down where I plan to have the user select their name and then want to have the cases associated with their name appear on the screen.  I was able to program Access to have the lawyer name appear only once in the drop-down since many records in the main table can be associated with him/her.  How do I:

1.  Upon selecting of a lawyer's name, how do I get the only the cases associated with that lawyer to display on the screen?

2,  Each lawyer is assigned a location:  NYC (New York), WAS (Washington), PHL (Philly), MIA (Miami).  One lawyer can be associated with multiple cities.  How can I program Access that when he selects his name, the cases for him appear on the screen?

3.  How I can program Access where, if in question #1 I select a lawyer's name and question #2 I select a city, I can receive records associated with that lawyer?

Thank you for your help!  
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

if you can upload  a copy of the db now, i can look at it now and give you a solution.
Avatar of James0903
James0903

ASKER

Capricorn1, give me a couple of minutes and I will do so...need to make sure I remove any confidential information (don't need any legal issues!)
Here you go...had to remove a lot of data.  Left enough to give you an idea of what I am trying to accomplish.  The database is in very early stages.  I am taking baby steps to make sure I program correctly from beginning.  Thank you.
Working-Database.accdb
test this

here are the codes
Sub ApplyFilter()
Dim sFilter As String
sFilter = ""
If Me.Combo0 & "" <> "" Then sFilter = "[Last name]=" & Chr(34) & Me.Combo0 & Chr(34)
If Me.Combo2 & "" <> "" Then sFilter = sFilter & " And [city]=" & Chr(34) & Me.Combo2 & Chr(34)

If sFilter & "" <> "" Then
    Me.subFrmMain.Form.Filter = sFilter
    Me.subFrmMain.Form.FilterOn = True
    Else
    Me.subFrmMain.Form.FilterOn = False
End If

End Sub
Private Sub Combo0_AfterUpdate()
ApplyFilter
End Sub

Private Sub Combo2_AfterUpdate()
ApplyFilter
End Sub

Private Sub Command5_Click()
Me.Combo0 = ""
Me.Combo2 = ""
ApplyFilter
End Sub

Open in new window

Working-Database.accdb
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access