Linking tables/forms together to get filtered infomation only pertaing to a specific record

Question

I am trying to create a database to do surveillance site walks. I have created multiple tables for each part of the site walk broken down like this:

Opportunity/Customer Info/Opportunity Number

              Site information (Customers are mostly school district that will have multiple sites tied to one     Customer/Opporunity

                          Camera Info for site (Multiple cameras will be tied to one site. So multiple cameras per site. Multiple Sites per 
                          customer)

So my list of Tables are:

Opportunity/Customer
Sites
Cameras

So basically when I create a new Opportunity and then open the Sites Form/Table, anything I create is tied to the opportunity I just created and the only records listed are just the sites for the opportunity I have opened. Then once those sites are filtered to the specific opportunity, I want the cameras under each site filtered to just that site. This is my first attempt so bear with me.

Thanks!

Answer 1

Hi. Not sure I follow what you're asking but setting up a form for a one-to-many relationship is better by using a form/subform setup. If you need two subforms, that's also possible. Just my 2 cents...

Answer 2

So basically when I create a new Opportunity and then open the Sites Form/Table, anything I create is tied to the opportunity I just created and the only records listed are just the sites for the opportunity I have opened. Then once those sites are filtered to the specific opportunity, I want the cameras under each site filtered to just that site. This is my first attempt so bear with me.

It sounds to me like you are talking about correlated subforms.  You'll find an example as CorrelatedSubs.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file uses data from Northwind as an example.  If you select the correlated subforms option the form which opens is based on Customers (analogous to your Opportunity/Customer).  The subform to the left is based on Orders (analogous to your Sites) and the subform on the right is based on OrderDetails (analogous to your Cameras).  When you navigate to a customer record the Orders for that customer are listed by order date in the subform on the left.  The subform on the right shows the order details for that order.  As you navigate to different orders in the left hand subform the right hand subform is requeried to show the details for that order.

The right hand subform is correlated with the left hand subform by being based on a query which references the key of the latter as a parameter.  The right hand subform is requeried in the Current event procedure of the left hand subform, which also disables the right hand subform if it is at a new record until a new row has been inserted.

Code in the right hand subform's BeforeInsert event procedure assigns the value of the OrderID from the left hand subform's current record to the OrderID foreign key column in the right hand subform's recordset.

The same demo illustrates the use of nested subforms to represent the same data.

---

Ken Sheridan, Stafford, England