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!
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!
if you can upload a copy of the db now, i can look at it now and give you a solution.
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!)
ASKER
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
Working-Database.accdb
test this
here are the codes
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
Working-Database.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.