 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / July 2007Subform problem
My main table is called Activity with key field ActivityID. I want to link activities to other activities with a many to many relationship through a table I have called Activity2PostActivity with fields ActivityID and PostActivityID. I want to create a subforms on my activity editor form that allows user to select the post activities/preactivities for each activity to create entries in this new table. I've been struggling with this for sometime and cannot get it to work. Any pointers? It sounds like the easiest way to do this would be to have your main form as you say for the main activity. Then create two tables that are for the pre- and post-activities, respectively. Each post- or pre-activity will have its own ID number. But, have an extra column that will link to the main table as ActivityID. So, Table2 (pre-) will have the following fields: PreID, Autonumber ActivityID, Number (this is linked) (further details) Table3 (post-) will have the same as above. In your relationships, go ahead and link them. You can also ensure that they are cascading so that any information that is changed ripples its way through. But that is up to you. When you create the subforms (and this is what I do) create subforms of table2 and table3. Do not display the IDs. Only the pertinent information. On the main form, use a tabbed control. That will allow you to put the two subforms into the main form and they are both accessible. Information both subforms will be linked to the main form via the ActivityID, thereby allowing a fairly easy connection between the individual tables. Hope this helps a bit. Jason Lopez > My main table is called Activity with key field ActivityID. I want to > link [quoted text clipped - 6 lines] > create entries in this new table. I've been struggling with this for > sometime and cannot get it to work. Any pointers? >My main table is called Activity with key field ActivityID. I want to link >activities to other activities with a many to many relationship through a [quoted text clipped - 3 lines] >create entries in this new table. I've been struggling with this for >sometime and cannot get it to work. Any pointers? The mainform should be based on Activity, and the subform on Activity2PostActivity. The Master/Child Link Field of the subform should be ActivityID, and the subform should have a combo box bound to PostActivityID, itself based on the Activity table. Is this what you've done? What specific problems have you had? How do you intend to distinguish pre- from post-activities? John W. Vinson [MVP] What do you mean by "and the subform should have a combo box bound to PostActivityID, itself based on the Activity table." I have a combobox with control source PostActivityID, what do you mean by based on activity table? And I intend on having just having the one Activity2PostActivity table that will be used for two different forms. One to assign pre-activities and one for post-activities. Both forms will write to the same table and should update when changed. So when I assign Activity2 as a post activity for Activity1, Activity2 should now update with Activity1 as a pre-activity. So the error that I am getting when selecting a post activity in the subform: "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain the duplicate data, romve the index, or redefine the index to permit duplicate entries and try again" So the subform should be getting the ActivityID from the main form and the ActivityID of the activites selected. How do I specify that I want the ActivityID of the selected activity to be entered as the PostActivityID? I think this may be where I'm having problems. > >My main table is called Activity with key field ActivityID. I want to link > >activities to other activities with a many to many relationship through a [quoted text clipped - 13 lines] > > John W. Vinson [MVP] I'm gonna completely lay out the situation and maybe it will help: Activity table: ActivityID Activity2PostActivity table: PreActivityID PostActivityID I have a main for for entering Activity data, and a subform on this main for inputing Activity2PostActivity data. So my subform should take the main forms ActivityID put that in the PreActivityID and a user can select what Activity they want to link and the subform should put that ActivityID in the PostActivityID. (I may make a second form to do the reverse of this, preactivities) Here is my subform's record source: SELECT Activity.ActivityID, Activity2PostActivity.PreActivityID, Activity2PostActivity.PostActivityID FROM Activity INNER JOIN Activity2PostActivity ON (Activity.ActivityID = Activity2PostActivity.PreActivityID); The combo box had record source of PostActivtyID. And the link Master/Child field are both ActivityID. This is the error I get when I select an activity in the subform: "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain the duplicate data, romve the index, or redefine the index to permit duplicate entries and try again" Both PreActivityID and PostActivityID are set to Indexed: Yes (Duplicates OK) in the Activity2PostActivity table. Any help would be greatly appreciated. >Here is my subform's record source: >SELECT Activity.ActivityID, Activity2PostActivity.PreActivityID, >Activity2PostActivity.PostActivityID >FROM Activity INNER JOIN Activity2PostActivity ON (Activity.ActivityID = >Activity2PostActivity.PreActivityID); That's the problem. >The combo box had record source of PostActivtyID. > >And the link Master/Child field are both ActivityID. You're trying to store the mainform's ActivityID into Activity.ActivityID - creating a new record with the same activity ID. The subform's recordsource should be JUST the Activity2PostActivity table. See my other answer in the thread for a bit of a refinement on this. John W. Vinson [MVP] >What do you mean by "and the subform should have a combo box bound to >PostActivityID, itself based on the Activity table." I have a combobox with [quoted text clipped - 4 lines] >update when changed. So when I assign Activity2 as a post activity for >Activity1, Activity2 should now update with Activity1 as a pre-activity. Ok... that makes sense. What's the *ROW SOURCE* of the combo box? A combo gets its data from the Row Source (usually a query) and stores the bound column of the selected record in its Control Source field. >So the error that I am getting when selecting a post activity in the >subform: "The changes you requested to the table were not successful because >they would create duplicate values in the index, primary key, or >relationship. Change the data in the field or fields that contain the >duplicate data, romve the index, or redefine the index to permit duplicate >entries and try again" What's the Primary Key of Activity2PostActivity? It sounds like it's PostActivityID; if so, that's the source of the problem. It should be a joint two-field key comprising ActvityID and PostActivityID, so that a single activity can have one or more pre- or post-activities, and each activity can serve as a pre- or post-activity multiple times. >So the subform should be getting the ActivityID from the main form and the >ActivityID of the activites selected. How do I specify that I want the >ActivityID of the selected activity to be entered as the PostActivityID? I >think this may be where I'm having problems. The ActivityToPostActivity table should (I'd think, I can't see your database) have three fields: ActivityID Long Integer link to Activities.ActivityID AssociatedActivityID Long Integer link to Activities.ActivityID PrePost Integer, with 1 meaning that this is a preactivity, 2 a postactivity The Primary Key of this table should consist of BOTH ActivityID and AssociatedActivityID; ctrl-click both fields in table design view and select the key icon. This will let you store both pre and post activities in the same table. If you want to use two subforms (which is probably good for your users!) then base one subform on a Query with 1 as a criterion on PrePost, and the other on a query using 2. John W. Vinson [MVP] IT WORKS! Well for the most part. The only problem I am having is that it is writing the ActivityCode to the PostActivity field rather than the Activity ID. Here's the row source for the combo box: SELECT Activity.ActivityID, Activity.ActivityName, Activity.ActivityCode, Activity.ServiceAreaID FROM Activity ORDER BY [ServiceAreaID], [ActivityName]; I have the bound column set to 1, but it still pulls the ActivityCode rather than the ActivityID. Suggestions? Oh and thanks for your help so far. > >What do you mean by "and the subform should have a combo box bound to > >PostActivityID, itself based on the Activity table." I have a combobox with [quoted text clipped - 44 lines] > > John W. Vinson [MVP] >IT WORKS! Well for the most part. The only problem I am having is that it is >writing the ActivityCode to the PostActivity field rather than the Activity [quoted text clipped - 8 lines] > >Suggestions? It shouldn't be. Might the ActivityID field in the table be defined as a <yuck!> Lookup field, i.e. storing the ActivityID while *displaying* the ActivityCode? John W. Vinson [MVP] It was, thanks for all your help! > >IT WORKS! Well for the most part. The only problem I am having is that it is > >writing the ActivityCode to the PostActivity field rather than the Activity [quoted text clipped - 14 lines] > > John W. Vinson [MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.