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

Access 2007 Beginner question

Posted on 2011-03-13
Last Modified: 2013-11-29
I'm working on an Access 2007 database.  Code is attached.

Brief description of DB -
CommunityPlans (Plans available per Community)

On the Edit Contracts form I need to pick a Property and have the Community displayed automatically and then I need to pick a plan based on the plans available for that community.

I can't figure out how to do this, or what it's even called, hence the ambiguous title.  I'm also not sure if Community should be a field in the Properties table, or should I have another table like CommunityProperties?

Any help would be appreciated.

Question by:bwander
  • 6
  • 5
LVL 84
ID: 35123220
First: What is the purpose of your database? What is a Community, what's a Property, etc etc - and how do they all relate together in the real world?

Author Comment

ID: 35123291
Purpose is to create a selections sheet for a homebuilder.

Community is a housing development.  Communities have many properties and certain plans that are available.  

Property is a specific address

Plan is a floorplan

Contract is a selections table that links customers to a property and plan

Expert Comment

ID: 35126009
As you have a unique property name for every community, why dont you use a textbox?
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

LVL 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 35127133
A Property is related to a Community, but other than that relationship, a Community has nothing to do with a Contract. It seems the user would select a Property and a Plan in order to "complete" a Contract, at least at the simplest level.

If that's the case, and if you just want to show the Community, then you can use the Property combo. Build a query that "joins" the Property and Community tables, and use that as the basis for your combo:

SELECT PropertyID, PropertyName, Community FROM Properties JOIN Communities ON Properties.CommunityID = Communities.CommunityID

The exact syntax will be different, but the above should give you an idea of what to do.

Set the Combos properties as such: ColumnCount = 3, ColumnWidths=0;1;0. This "hides" the 1st and 3rd columns, while displaying the 2nd.

Add a Textbox named "tbCommunity" to your form. In the AfterUpdate event of your combo, add this:

Me.tbCommunity = Me.YourCombo.Column(2)

Column(2) is the 3rd column, since the Columns collection of a combo is zero-based.

You'll also need to do this in the Form's Current event (same code).

Author Comment

ID: 35129086
looks like you understand what I'm try to do, but I'm not totally clear on your instructions.

The property combo box on my form does a lookup on the properties table, are you saying replace that with the join query?

I understand the column hiding, the textbox and event instructions.

Also, I should probably clue you in to my next two requirements - I was trying to keep it simple.

Deriving the community based on the property selected is really needed because I need to present a list box for the plan selection, which is limited based on the community.  So will your method be able to drive this second query as well?  **Based on my reading I understand Access 07 has a multiple value datatype, but I went with a join table for this instead (the CommunityPlans table) - would you say I've done this correctly?

Finally, I need to present a list box for the available elevations based on the plan selected.  Can you tell if my table structure will accommodate this easily?

btw, thanks for responding.  
LVL 84
ID: 35129674
First: I would STRONGLY advise you to remove ALL table-level lookups (i.e. those fields where you've defined a lookup at the table design level). That's not to say you shouldn't have table-BASED lookups (you should), but you should not define them at the table level. Table-level lookups tend to obfuscate what is really going on, and while it makes adding controls to your forms a bit easier, there are a LOT of drawbacks, especially as your database become more complex.

<Deriving the community based on the property selected is really needed because I need to present a list box for the plan selection, which is limited based on the community.  So will your method be able to drive this second query as well?  **Based on my reading I understand Access 07 has a multiple value datatype, but I went with a join table for this instead (the CommunityPlans table) - would you say I've done this correctly?>

You can always get the Community associated with a Property, since that value would be stored in the Property table, so I'm not exactly clear on what you mean. It almost sounds to me as if you need a "cascading combo" sort of setup, wheren the user would select a Community, then a Property, and then a Plan.

I'd strongly advise AGAINST the MVF unless you have a compelling need for it (and I've not yet seen one). The Join table would be a better choice, IMO.

I have no idea what an "elevation" is, so cannot comment on that.

Author Comment

ID: 35130172
You have said a lot of things that make sense and I appreciate the time.

Can you clarify your instructions on the query?  Or if possible modify the db attached to my first post to show me what you are saying?

LVL 84
ID: 35130817
The basic syntax I suggested earlier is a standard JOIN query. Here's the exact SQL to do it:

SELECT Properties.PropertyID, Properties.PropertyName, Communities.CommunityID, Communities.CommunityName
FROM Communities INNER JOIN Properties ON Communities.CommunityID = Properties.Community;

Open a new Query, switch to SQL View, and paste that into your query.


Author Comment

ID: 35134805
query works as written.  The AfterUpdate Event is giving me an error - it doesn't recognize the object ME.

Am I supposed to replace ME. with something else?

Also, you said I need the same code in the Form's current event.  Can you explain why I need that?

Thanks for being patient with me.
LVL 84
ID: 35136567
What code do you have in the AfterUpdate event? ME refers to the active object - so if you're running this in the Form, it would refer to the Form.

Author Comment

ID: 35138195
I have attached a screenshot.


Author Comment

ID: 35144118
I figured out you have to put the code in an Event Procedure.  After I did that it worked.

thanks for all the help.

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

839 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