Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Access 2007 Beginner question

Posted on 2011-03-13
Medium Priority
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
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
  • 6
  • 5
LVL 85
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?
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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 85
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 85
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 85
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

610 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