Solved

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

Posted on 2011-02-15
6
452 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

770 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