during create not creating records in linked file

[cindyareed]

I have a form that contains my student information. That form has 2 tabs, one has the Parent subform attached to it and the other tab has the Grandparent subform.

I can create the Student record but the Relationship table record is not being created when a parent already exists.

I am using query's on my forms/subforms. (instead of the tables themselves).

When I create the Student record and then try tie it to an existing parent record or create one if it does not exist, I get the error:
"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 duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

In this case the parent already exists and I thought that by having a look up on a field (that is unique) that I could get the entire record to display in the subform and that this could create the linkage to the relationship table.

I have the following tables:

STUDENT
StuID (unique key)
StuFstNm
StuLstNm
+ other fields

PARENT
ParID (unique Key)
ParFstNm
ParLstNm
+ other fields

RELATIONSHIP
RelStuID (unique Key)
RelParID (unique Key)
RelType


On my parent subform I want to default "PA" into the RelTyp field and on the Grandparent Subform I want to default "GP" into that field when creating records.

So...How do I bring up an existing record on the parent/grandparent subforms so that the linkage can be created? All works fine if the parent and student are both new records.

Thanks for any help
Cindy

 

[cindyareed]

Pat,

I did look at the web site info you gave me that and I thought I had my tables set up that way? Don't I?

I have one parent record, one student record and the Relationship table has the many, linking the parent to their children.

I thought I understood this. Do I still have it wrong?

Cindy

[This message has been edited by cindyareed (edited 04-16-2002).]

 

[cindyareed]

Thanks Pat, Will send you and E-mail.

Cindy

 

[cindyareed]

Pat,

Thanks for the Many to Many sample. I have looked the tables, queries and forms to compare the sample to what I have. I think mine is very similar to the sample and my form is doing basically what yours is doing.

Is it possible to create a new record in the form and one in the subform at the same time with out having to get out of the form and go to a different form. I want to be able to Create a new Child and a new Parent at the same time by entering the child information in the form and then go right to the subform and add a parent. I can create a child and then attach an existing parent right now but I think I am missing something to allow creation of both or is this a limitation of Access?

Thanks Cindy