Solved

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

Posted on 2011-02-15
6
441 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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
ID: 34903684
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 34903811
use this one, this is better



Working-Database.accdb
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Link Excel Spreadsheet to Access, not using Transferspreadsheet 5 29
Run Time Error 3075 15 45
Query design issue 2 22
Extract string portion 2 14
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now