?
Solved

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

Posted on 2011-02-15
6
Medium Priority
?
493 Views
Last Modified: 2013-11-27
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!  
0
Comment
Question by:James0903
  • 4
  • 2
6 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34903465
if you can upload  a copy of the db now, i can look at it now and give you a solution.
0
 

Author Comment

by:James0903
ID: 34903485
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!)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34903684
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:James0903
ID: 34903689
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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34903772
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
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 34903811
use this one, this is better



Working-Database.accdb
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

569 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