Cannot add records, join key of table not in recordset

[Ayiramala]

Hi,


I know this can be a much discussed question, but no matter how I try, I just couldn't get it right.

Please take a look at the attached layout. When I try to enter new student records I get the error 'Cannot add records, join key of table tblAdmision not in recordset'. The problem is with two filds, namely, YearID and DivisionID. I have placed Comboboxes everywhere, for Religion, Caste, Language, Year and Division. All the others work, except these two. I just can't select them from the Combo.
What could I be doing wrong? Any help is greatly appreciated. Thanks.

[ItsMe]

I do not use the Relationships Window so I do not know for sure. However, considering the error message, it seems you create a constraint under the Relationship you created. Did you set referential integrity rules to your relationships when you defined them? Take a look at the properties of the relationship between the two tables by double clicking it. What sort of constraints did you create?

[Ayiramala]

In our school, for various purposes, we have to specify the relationship the student has with his 'Parent', such as whether father, mother or guardian. Hence the relationship table.

About constraints, I only selected 'Enforce referential integrity', with the two cascade options left unchecked.

[ItsMe]

Maybe you can go to design view of your tblAdmission table and adjust the properties of StudentID to "Allow Nulls".

Or
Add a relative record to tblAdmission before committing the new student to tblStudents

Or
Remove the relationship between the two tables from the Relationships Window

[Ayiramala]

I removed the relationship but that didn't help.

Add a relative record to tblAdmission before committing the new student to tblStudents

I'm afraid I don't know how to do that.

[ItsMe]

Go ahead and replace your relationship then. I may be misunderstanding what table you are trying to add a new record to. Perhaps you can explain which table you are trying to add a record to.

When you say ...

When I try to enter new student records...

What are you doing, exactly? Are you using a form and typing data into a specific control, or clicking a button, or ...? What is the recordsource of your form?

[Ayiramala]

Sorry that I did not explain properly. This is what I'm trying to do:

When a new student is enrolled I want to enter his admission details. This will include: StudentName, ParentName, BirthDate, AdmissionNo, PhoneNo, RegNo (all details in tblStudents), then YearName, DivisionName, ReligionName, CasteName, GenderName as well. I am using a form for this purpose, which is based on a query.

Please ask if any further clarification is required.

[Ayiramala]

This is the query:

SELECT tblStudents.StudentID, tblYear.YearName, tblDivision.DivisionName, tblGender.GenderName, tblStudents.StudentName, tblStudents.AdmissionNo, tblLanguage.LanguageName, tblStudents.BirthDate, tblReligion.Religion, tblCaste.CasteName, tblStudents.ParentName, tblRelationship.RelationshipName, tblStudents.PhoneNo, tblStudents.GenderID, tblStudents.CasteID, tblStudents.LanguageID, tblAdmission.DivisionID, tblStudents.ReligionID, tblStudents.RelationshipID, tblAdmission.YearID
FROM (tblGender INNER JOIN (tblReligion INNER JOIN (tblRelationship INNER JOIN (tblLanguage INNER JOIN (tblCaste INNER JOIN tblStudents ON tblCaste.CasteID = tblStudents.CasteID) ON tblLanguage.LanguageID = tblStudents.LanguageID) ON tblRelationship.RelationshipID = tblStudents.RelationshipID) ON tblReligion.ReligionID = tblStudents.ReligionID) ON tblGender.GenderID = tblStudents.GenderID) INNER JOIN (tblYear INNER JOIN (tblDivision INNER JOIN tblAdmission ON tblDivision.[DivisionID] = tblAdmission.[DivisionID]) ON tblYear.[YearID] = tblAdmission.[YearID]) ON tblStudents.StudentID = tblAdmission.StudentID
WHERE (((tblAdmission.YearID)=[Forms]![frmNavi]![Combo65]))
ORDER BY tblDivision.DivisionName, tblGender.GenderName DESC , tblStudents.StudentName;

[ItsMe]

I would say your issue is that you are creating a dependency on tblAdmission before it is possible to do so.

When you create a new record for a student, start by creating a query that only uses one table, tblStudents. Use the Query Designer to create, save, and name your new query. Bind a NEW form to this Query Object by including the name of the query in the Form's RecordSource.

Change the Data Entry property of your form to Data Entry = Yes. Add a few bound controls to your form while in design view. Test out your new form and then move to the next step. I would then start to add combos for tblCaste, tblRelationship, tblLanguage, and tblReligion. Test everything, one step at a time.

From there, you will need to add some stuff to your tblAdmission. You might be able to continue on this new form you created. Maybe continue with a subform. I would probably create another form. A button on the first form could create open the second form and add the Student ID to it. The second form would be created the same way I described creating the first.

[Ayiramala]

When you create a new record for a student, start by creating a query that only uses one table, tblStudents. Use the Query Designer to create, save, and name your new query. Bind a NEW form to this Query Object by including the name of the query in the Form's RecordSource.

Change the Data Entry property of your form to Data Entry = Yes. Add a few bound controls to your form while in design view. Test out your new form and then move to the next step. I would then start to add combos for tblCaste, tblRelationship, tblLanguage, and tblReligion. Test everything, one step at a time.

From there, you will need to add some stuff to your tblAdmission. You might be able to continue on this new form you created. Maybe continue with a subform.

I already tried that, but couldn't continue with the subform.
I created the main form with tblStudents, tblReligion, tblCaste, etc. and it worked without a glitch. But only that much. When I tried to add a subform based on tblAdmission the subform wouldn't work. I then thought of a separate form, but the problem is, tblAdmission has only numbers. The only link between tblStudents and tblAdmission is studentID. The first student I have may have the ID 967. How do I find the student in the second form?

[Ayiramala]

ItsMe,
The form-subform setup worked! I must have made an error the first time I tried. Now when I did it again it worked perfectly. Now there are no problems.
You spent a lot of time and effort on this. Thank you very much. Much appreciated.

[ItsMe]

Glad you were able to get it to work out. As I mentioned, I would probably used a second form. Because you would need additional comboboxes to manage things like Division and Year, a second form seems to make sense.

There are various ways you can assign values to controls in a second form or to a field in its recordsource. This way, you can open a second form to a new record and assign foreign key values to fields. You can always start a new thread and ask questions if you decide to try this at a later date.