Cascading Combo Boxes problem linking to a sub form

[Dave Butler]

Hi, I'm pretty much a beginner to access and am trying to develop a customer care database for residents that move into new homes.
I have created 4 tables: Sites, Plot Details, Resident Details and Defect Details.
I have been able to produce a plot detail form that displays all the plot info, one record at a time for that site, based on choosing that site from a combo box. Then below that is a list of existring plots which is within a subform on the main form.
I am now trying to display a specific resident's details in exactly the smae way but by filtering from two combo boxes ( sites and plots) and displaying one record at a time on the form so that their details can be amended if necessary as well as showing a sub form below with all the other residents that are in the system.
I can't seem to get my head around this last bit as the second combo box seems to complicate things immensely.
Can anyone shed any light ?
let me know if the ACCDB file is helpful
Cheers

[Micron]

db might should be helpful as I for one am trying to visualize this and probably don't have it right. You want a single record main form for plots with a single record subform for sites and a multi record subform for residents? If that's it, it sounds odd as there's probably no real relationship between a single site, single plot, but multiple residents.

[Dave Butler]

Hi Micron,

I have attached the file to show you what I have done so far.
The "All Plots Form" works fine for me. It has 2 sub forms that automatically refresh to the relevent set of records once the Site has been chosen.

In a list of heirarchy, the info will stack like this:
There are a number of sites, each one uniquely named with a site code,
Each site can have any number of plots, plot numbers may be repeated on each site (every site will have a "Plot 1" etc.)
Each plot can have any number of residents (usually just one but obviously they move out randomly.)
Each plot can have any number of defects reported at any time.

I would like to have a "Residents Info" form where you identify a specific plot in a specific site (possibly by use of comboboxes) which then displays the info for the current resident for you to add or edit into.

The second sub form below will show any previous residents of that plot in a datasheet view.

There will then need to be a similar setup for a "Plot Defects" form, again based on selecting a specific plot to bring up a sub form record to add or edit into, with a list of previous defects displayed below in a 2nd Subform datashee

Does this make sense ?

[Micron]

This is it about the other form "SiteResiTables"? That one has a non-existent row source specified for Plot. Its row source ought to be something like

SELECT [Plot No], [Address 1] FROM [Plot Info Table] WHERE [Plot Info Table].[Site ID]=[Forms]![SitesResiTablesForm].[Combo18];

along with 2 columns; maybe 0;0.75 (if in inches).

Why put a subform for records on the main form when you could just put those data controls on the main form and use only one subform for associated records?
Site is a number in one table and text in another!
You plan on creating any PK fields in your tables?
You're missing the basics on normalization (only Plot info in tblPlots, only site info in tblSites along with junction tables); a lot of holes where there's no comments...
You will have a lot of repeated data doing what you're doing. Also should read up on naming things (e.g. no spaces)

maybe take a look at these

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...ng-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/...cation-in.html

Important for success:
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html