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?
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

805 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