[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 550
  • Last Modified:

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

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!
  • 2
  • 2
1 Solution
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
enviro-guy2010Author Commented:
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!
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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 :)
enviro-guy2010Author Commented:
Thanks for your help!

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now