Subform problem

[jhess85]

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?

 

[Jason Lopez]

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

 

[John W. Vinson]

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?

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]

 

[jhess85]

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.

 

[jhess85]

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.

 

[John W. Vinson]

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.

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]

 

[John W. Vinson]

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]

 

[jhess85]

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.

 

[John W. Vinson]

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?

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]

 

[jhess85]

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
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?

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]