Link Items in Same Table
in [Getting Started]
|
Prev: Access 2000 Slowdown
Next: access 2007 combo boxes
From: KenSheridan via AccessMonster.com on 18 Mar 2010 09:09 Phil: Inserting a row into the LinkedContracts table is quite easily achieved by means of a subform based on this table. This subform would be embedded in a form based on the Contracts table with the LinkMasterFields property set to Ac-id and the LinkChildFields property set to AccountID. The subform would contain two controls, a combo box bound to AccountID and a text box bound to Association. The combo box would be set up as follows: RowSource: SELECT [Ac-id], [Ct-code] FROM [Contracts] ORDER BY [Ct-code]; BoundColum: 1 ColumnCount: 2 ColumnWidths: 0cm;8cm If your units of measurement are imperial rather than metric Access will automatically convert the last one. The important thing is that the first dimension is zero to hide the first column and that the second is at least as wide as the combo box. However, its important to understand that the relationship between the two contracts linked in this way is directional, so the subform will only show those contacts which have been linked to the current contact in the parent form, not any contacts to which the current contact in the parent form has been linked. Similarly if you create a report based on a query which joins Contacts to LinkedContracts on AccountID and LinkedContracts to another instance of Contracts on AssociatedAccountID the report will only show the links in the one direction. You can get it to show links in both directions by creating another query which joins Contacts to LinkedContracts on AssociatedAccountID and LinkedContracts to another instance of Contracts on AccountID, and then basing the report on a union query of these two queries. This is fine if you are only interested in immediate links between contracts, but it gets more complicated if you want to return all links down the line, e.g. if Contract A links to Contract B which in turn links to Contracts D and E. This is not too difficult if the number of steps is finite, but otherwise it's akin to the classic 'bill of materials' problem, which requires a more elaborate solution. You'll fund a demo of ways of handling this in the file attached to my post at: http://community.netscape.com/n/pfx/forum.aspx?tsn=1&nav=messages&webtag=ws-msdevapps&tid=23133 Ken Sheridan Stafford, England TheScullster wrote: >> Sorry, >> I lost this thread. >[quoted text clipped - 14 lines] >> The Hilltop Institute >> University of Maryland Baltimore County > >Thanks John > >I think I follow your instructions here. >One element that is not clear is the means of creating the record entries in >the new table. >Yes this probably sounds like the dumbest of basic questions, but so far my >understanding has been limited to data entry into a single table via a form. >What we are trying to achieve here is to create an entry in our new table >linking to a record in an existing table. >How is that best achieved? >I pondered a "create link" button with some code behind to capture the >appropriate contract record from the form you describe and then present the >user with a text box to add the memo. >How this is done is almost a mystery. > >Thanks > >Phil -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201003/1
From: TheScullster on 18 Mar 2010 10:32
"KenSheridan via AccessMonster.com" wrote > > Inserting a row into the LinkedContracts table is quite easily achieved by > means of a subform based on this table. This subform would be embedded in > a > form based on the Contracts table with the LinkMasterFields property set > to > Ac-id and the LinkChildFields property set to AccountID. The subform > would > contain two controls, a combo box bound to AccountID and a text box bound > to > Association. > > The combo box would be set up as follows: > > RowSource: SELECT [Ac-id], [Ct-code] FROM [Contracts] ORDER BY > [Ct-code]; > > BoundColum: 1 > ColumnCount: 2 > ColumnWidths: 0cm;8cm > > If your units of measurement are imperial rather than metric Access will > automatically convert the last one. The important thing is that the first > dimension is zero to hide the first column and that the second is at least > as > wide as the combo box. > > However, its important to understand that the relationship between the two > contracts linked in this way is directional, so the subform will only show > those contacts which have been linked to the current contact in the parent > form, not any contacts to which the current contact in the parent form has > been linked. Similarly if you create a report based on a query which > joins > Contacts to LinkedContracts on AccountID and LinkedContracts to another > instance of Contracts on AssociatedAccountID the report will only show the > links in the one direction. You can get it to show links in both > directions > by creating another query which joins Contacts to LinkedContracts on > AssociatedAccountID and LinkedContracts to another instance of Contracts > on > AccountID, and then basing the report on a union query of these two > queries. > > This is fine if you are only interested in immediate links between > contracts, > but it gets more complicated if you want to return all links down the > line, > e.g. if Contract A links to Contract B which in turn links to Contracts D > and > E. This is not too difficult if the number of steps is finite, but > otherwise > it's akin to the classic 'bill of materials' problem, which requires a > more > elaborate solution. You'll fund a demo of ways of handling this in the > file > attached to my post at: > > http://community.netscape.com/n/pfx/forum.aspx?tsn=1&nav=messages&webtag=ws-msdevapps&tid=23133 > > Wow - thanks for such a comprehensive response Ken. Also for pointing out the vagueries/intrecacies of the undertaking. I believe that nested links will not be required, but bi-directional links will. So my final solution will be based on your penultimate paragraph. As I have been a while away from all this, I will start from the beginning of your response and try to create the different options that you have outlined. Thanks again - your assistance is greatly appreciated Phil |