Solved

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

Posted on 2011-02-15
6
470 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

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 500 total points
ID: 34903811
use this one, this is better



Working-Database.accdb
0

Featured Post

Technology Partners: 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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

726 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