me again
My database contains information about various dwellings or properties and the works required to maintain certain aspects of them.
Most of the information in the database is linked to an address table (call it tbl1) with a unique property reference number (UPRN).
Some tables contain general information about a job and a related table - information in greater detail. For example one table (call it tbl2) might include the date of a survey, who the surveyor was, estimated costs etc and a related table might contain greater detail of the works required (call it tbl3) e.g itemised aspects of the work e.g. staircase, windows, roof, door 1, door 2 etc.
One record in the Works table might therefore match up with several items in the WorksDetail table - all of which would be related to one property address.
I want to display general details about a property on a main form eg address, contact name, tel nr etc and general details from tbl2 about any Works_Order's raised against a property on a subform. The subform would also contain greater detail from tbl3 of any Works_Order raised in a 'subform on the subform' in datasheet view.
If I have more than one WorksOrder raised against an address which obviously happens over time, I would like to be able to show these in a List box on the subform so that by clicking one I would display all the information about it on the subform.
I have got this far but no further. The problem I have is that all the WorksOrders from tbl2 ever raised are shown in the List box, not just those relating to the address or property on the main form. How can I limit them just to the property concerned and how can I get it to refresh itself when the record on the main form is changed from one address to another?
I wonder if there is anyone out there who could help me - please?
I have Access 2007 but am more used to 2003 and still can't get my head around ADO having learnt DAO (or is it vica versa?).
My database contains information about various dwellings or properties and the works required to maintain certain aspects of them.
Most of the information in the database is linked to an address table (call it tbl1) with a unique property reference number (UPRN).
Some tables contain general information about a job and a related table - information in greater detail. For example one table (call it tbl2) might include the date of a survey, who the surveyor was, estimated costs etc and a related table might contain greater detail of the works required (call it tbl3) e.g itemised aspects of the work e.g. staircase, windows, roof, door 1, door 2 etc.
One record in the Works table might therefore match up with several items in the WorksDetail table - all of which would be related to one property address.
I want to display general details about a property on a main form eg address, contact name, tel nr etc and general details from tbl2 about any Works_Order's raised against a property on a subform. The subform would also contain greater detail from tbl3 of any Works_Order raised in a 'subform on the subform' in datasheet view.
If I have more than one WorksOrder raised against an address which obviously happens over time, I would like to be able to show these in a List box on the subform so that by clicking one I would display all the information about it on the subform.
I have got this far but no further. The problem I have is that all the WorksOrders from tbl2 ever raised are shown in the List box, not just those relating to the address or property on the main form. How can I limit them just to the property concerned and how can I get it to refresh itself when the record on the main form is changed from one address to another?
I wonder if there is anyone out there who could help me - please?
I have Access 2007 but am more used to 2003 and still can't get my head around ADO having learnt DAO (or is it vica versa?).
