MS Access 2010 - How do I set-up a form that limits choices based on relationships?

Posted on 2012-08-16
Last Modified: 2012-08-28
I am creating a simple Access database.  I have a table for Sessions and I am trying to create a form to enter new sessions.  I need to be able to first select the Client from the Client table, then I need to be able to select a Campaign from the Campaign table, but only the campaigns related to the chosen client should appear.  Next, I need to assign a Client Rep to the session, but I only want to be given the choices of the client reps that are related to the clien that was first chosen.  Then I need to be able to fill in the rest of the data for the Session table.  I can get the form to limit the campaigns by the customer, but it keeps giving me all of the client reps to choose from instead of just the ones that are assigned to the specified client.  I attached a Word document that shows the relationships.  Can someone give me some advice, please?  Thank you!
Question by:enviro-guy2010
    LVL 84
    You mention Customer, but I don't see a Customer table in your diagram. Do you mean "client" when you say "Customer"?

    What's the difference between a ClientRep and a Client? I assume that a Client is the "Company", and a ClientRep is an employee of that Company.So you would perhaps first choose a Client, and then a ClientRep, it would seem.

    Assuming my assumptions are correct, it would seem that either (a) you should not be storing the ClientRepID in the Session table or (b) you've got the relationship between Campaign and Session backwards.

    Does a Campaign "belong" to a Session, or is it the other way around?

    In other words, if a Campain belongs to a Client, then it would seem that you would select Campaign - Session - Client - ClientRep, and not Session - ClientRep - Campaign - ClientRep - Client.

    But I may have misunderstood your database needs and purpose. If you could give us more information about the real-life use of your database, perhaps we could help further.
    How are you filtering

    Author Comment

    LSM, you are correct that I was referring to Clients when I said Customer.  Sorry about the confusion...  You are correct that ClientRep's are employees of Clients.  Each Client Rep is assigned to one Client.

    Each campaign consists of multiple sessions and each session is for one ClientRep, but only the ClientRep's that are assigned to the Client who purchased the Campaign are eligible to be assigned to a session.  I am storing the ClientRep information in the Session because I will need to pull queries for various sessions that are assigned to the same ClientRep.  I have to assign the Sessions to a Campaign so I can do summary queries by Campaign.

    For setting up new Sessions, I want to begin by selecting the Client, then I would select one of the campaigns that belongs to the client, then I would create a new session for that campaign.  In doing so, I need to tell it which ClientRep the session is for.  My problem is that is continues to allow me to select any ClientRep and not just ones who belong to the Client for which the Campaign and Session belong.

    I hope that makes more sense.  Thanks for your help!
    LVL 84

    Accepted Solution

    Thanks for the explanation. It would seem, then, that your tables are setup correctly, and your relationships are correct.

    You'll have to give us more information on how you are selecting the Clients and the ClientRep - for example, are you using a Form, and do you have a combo on that form where you would select a Client? If so, it should be a simple matter to filter the ClientRep combo to show only those records associated with the selected Client:

    Me.YourClientRepCombo.RowSource = "SELECT * FROM Client_Rep WHERE ClientID=" & Me.YourClientCombo.Column(0)

    You'd do this in the AfterUpdate event of ClientID.

    If you're trying to do this directly in table Datasheet view, or in a query, then all bets are off :)

    Author Closing Comment

    Thanks for your help!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Article by: Leon
    Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
    No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    759 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

    9 Experts available now in Live!

    Get 1:1 Help Now