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
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 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?
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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

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.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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.

726 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