Access 2007 Beginner question

I'm working on an Access 2007 database.  Code is attached.

Brief description of DB -
Tables-
Communities
Properties
Plans
CommunityPlans (Plans available per Community)
Contracts

Problem-
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.



 test.accdb
bwanderAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
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).
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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?
0
 
bwanderAuthor Commented:
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
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
champfrom1984Commented:
As you have a unique property name for every community, why dont you use a textbox?
0
 
bwanderAuthor Commented:
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.  
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
bwanderAuthor Commented:
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?


0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.

0
 
bwanderAuthor Commented:
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.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
bwanderAuthor Commented:
I have attached a screenshot.


Capture.PNG
0
 
bwanderAuthor Commented:
I figured out you have to put the code in an Event Procedure.  After I did that it worked.

thanks for all the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.