error about referential integrity in a sub form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a strange issue that I can not figure out. I have a sub form that if
I open the sub form alone I can add records to the form. When I try to enter
records in the sub form as a part of the main form I get an error about
referential integrity. Any thoughts on where I should look for why this
issue is happening.

Thanks
Andrew
 
Open the main form in design view.
Right-click the edge of the subform control, and choose Properties.
On the Data tab, what field is named in the LinkChildFields?

That field will inherit a value from the main form's field that is named in
LinkMasterFields. If that value is not appropriate, you will receive the
Referential Integrity message.

For example, if the main form is at a new record and the field has zero as a
default value, the zero will prevent the subform creating the new record.

If that is not the issue, post back with more information on the source for
the main form (table or query?), the source for the subform (table or
query), and the names and data types of the fields named in LinkMasterFields
and LinkChildFields.
 
Allen,

Thanks for your post I think you are on the right track but I am not sure
what I am doing wrong. The Main form is a Family Record and the sub form is
a set of dates that are associated with a child in the family. When I set
the Master and child link fields to the [FamilyId] I get this error when I
clear the [FamilyId] from these fields the error goes away but the sub form
does not change records I assume due to them not being linked. [FamilId] is
just a long int which is an auto number and the primary key in the Family
table [tblFamily]. The sub form is based on a query [qryHelper] that
includes the [FamilyId] from a table of student names joined with a table of
dates associated with students.

[tblFamily]
FamilyId AutoNumber
Notes Memo
FamilyName Text

[qryHelper]
SELECT tblHelper.StudentId, tblHelper.NurseryDate, tblHelper.ProgramDate,
tblStudent.FamilyId, tblStudent.LastName, tblStudent.FirstName
FROM tblStudent INNER JOIN tblHelper ON tblStudent.StudentId =
tblHelper.StudentId
WHERE (((tblStudent.FamilyId)=[Forms]![frmFamilyEntry]![txtId]));

I am passing the [FamilyId] to the query by using a text field on the main
form.

Thanks for any help.

Thanks
Andrew
 
So the subform gets the FamilyID field from qryHelper, which gets it from
your Student table?

Open the student table in design view.
Select the FamilyID field.
In in lower pane, remove the zero from the Default Value property.

Does that solve it?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Andrew said:
Allen,

Thanks for your post I think you are on the right track but I am not sure
what I am doing wrong. The Main form is a Family Record and the sub form
is
a set of dates that are associated with a child in the family. When I set
the Master and child link fields to the [FamilyId] I get this error when I
clear the [FamilyId] from these fields the error goes away but the sub
form
does not change records I assume due to them not being linked. [FamilId]
is
just a long int which is an auto number and the primary key in the Family
table [tblFamily]. The sub form is based on a query [qryHelper] that
includes the [FamilyId] from a table of student names joined with a table
of
dates associated with students.

[tblFamily]
FamilyId AutoNumber
Notes Memo
FamilyName Text

[qryHelper]
SELECT tblHelper.StudentId, tblHelper.NurseryDate, tblHelper.ProgramDate,
tblStudent.FamilyId, tblStudent.LastName, tblStudent.FirstName
FROM tblStudent INNER JOIN tblHelper ON tblStudent.StudentId =
tblHelper.StudentId
WHERE (((tblStudent.FamilyId)=[Forms]![frmFamilyEntry]![txtId]));

I am passing the [FamilyId] to the query by using a text field on the main
form.

Thanks for any help.

Thanks
Andrew



Allen Browne said:
Open the main form in design view.
Right-click the edge of the subform control, and choose Properties.
On the Data tab, what field is named in the LinkChildFields?

That field will inherit a value from the main form's field that is named
in
LinkMasterFields. If that value is not appropriate, you will receive the
Referential Integrity message.

For example, if the main form is at a new record and the field has zero
as a
default value, the zero will prevent the subform creating the new record.

If that is not the issue, post back with more information on the source
for
the main form (table or query?), the source for the subform (table or
query), and the names and data types of the fields named in
LinkMasterFields
and LinkChildFields.
Click to expand...
Click to expand...
 
There was a 0 in the default value but that did not fix the issue. The sub
form has a Combo box on it that I use this query to fill the combo box:

SELECT qryHelper.StudentId, qryHelper.FirstName, qryHelper.LastName
FROM qryHelper;

Could this be throwing it off?


Thanks for your help.

Thanks
Andrew


Allen Browne said:
So the subform gets the FamilyID field from qryHelper, which gets it from
your Student table?

Open the student table in design view.
Select the FamilyID field.
In in lower pane, remove the zero from the Default Value property.

Does that solve it?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Andrew said:
Allen,

Thanks for your post I think you are on the right track but I am not sure
what I am doing wrong. The Main form is a Family Record and the sub form
is
a set of dates that are associated with a child in the family. When I set
the Master and child link fields to the [FamilyId] I get this error when I
clear the [FamilyId] from these fields the error goes away but the sub
form
does not change records I assume due to them not being linked. [FamilId]
is
just a long int which is an auto number and the primary key in the Family
table [tblFamily]. The sub form is based on a query [qryHelper] that
includes the [FamilyId] from a table of student names joined with a table
of
dates associated with students.

[tblFamily]
FamilyId AutoNumber
Notes Memo
FamilyName Text

[qryHelper]
SELECT tblHelper.StudentId, tblHelper.NurseryDate, tblHelper.ProgramDate,
tblStudent.FamilyId, tblStudent.LastName, tblStudent.FirstName
FROM tblStudent INNER JOIN tblHelper ON tblStudent.StudentId =
tblHelper.StudentId
WHERE (((tblStudent.FamilyId)=[Forms]![frmFamilyEntry]![txtId]));

I am passing the [FamilyId] to the query by using a text field on the main
form.

Thanks for any help.

Thanks
Andrew



Allen Browne said:
Open the main form in design view.
Right-click the edge of the subform control, and choose Properties.
On the Data tab, what field is named in the LinkChildFields?

That field will inherit a value from the main form's field that is named
in
LinkMasterFields. If that value is not appropriate, you will receive the
Referential Integrity message.

For example, if the main form is at a new record and the field has zero
as a
default value, the zero will prevent the subform creating the new record.

If that is not the issue, post back with more information on the source
for
the main form (table or query?), the source for the subform (table or
query), and the names and data types of the fields named in
LinkMasterFields
and LinkChildFields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a strange issue that I can not figure out. I have a sub form
that
if
I open the sub form alone I can add records to the form. When I try to
enter
records in the sub form as a part of the main form I get an error about
referential integrity. Any thoughts on where I should look for why
this
issue is happening.

Thanks
Andrew
Click to expand...
Click to expand...
Click to expand...
 
I don't think the RowSource of the combo would be the issue.

You say that removing the MasterLinkFields/ChildLinkFields prevents the
error (even though it does not achieve the desired results, of course), so
it must be linked to something that's inherited as a result? Would be worth
persuing that further.

I would assume that the query that serves as the subform source is
updatable, i.e. you can add new records there without a problem?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Andrew said:
There was a 0 in the default value but that did not fix the issue. The
sub
form has a Combo box on it that I use this query to fill the combo box:

SELECT qryHelper.StudentId, qryHelper.FirstName, qryHelper.LastName
FROM qryHelper;

Could this be throwing it off?


Thanks for your help.

Thanks
Andrew


Allen Browne said:
So the subform gets the FamilyID field from qryHelper, which gets it from
your Student table?

Open the student table in design view.
Select the FamilyID field.
In in lower pane, remove the zero from the Default Value property.

Does that solve it?


Andrew said:
Allen,

Thanks for your post I think you are on the right track but I am not
sure
what I am doing wrong. The Main form is a Family Record and the sub
form
is
a set of dates that are associated with a child in the family. When I
set
the Master and child link fields to the [FamilyId] I get this error
when I
clear the [FamilyId] from these fields the error goes away but the sub
form
does not change records I assume due to them not being linked.
[FamilId]
is
just a long int which is an auto number and the primary key in the
Family
table [tblFamily]. The sub form is based on a query [qryHelper] that
includes the [FamilyId] from a table of student names joined with a
table
of
dates associated with students.

[tblFamily]
FamilyId AutoNumber
Notes Memo
FamilyName Text

[qryHelper]
SELECT tblHelper.StudentId, tblHelper.NurseryDate,
tblHelper.ProgramDate,
tblStudent.FamilyId, tblStudent.LastName, tblStudent.FirstName
FROM tblStudent INNER JOIN tblHelper ON tblStudent.StudentId =
tblHelper.StudentId
WHERE (((tblStudent.FamilyId)=[Forms]![frmFamilyEntry]![txtId]));

I am passing the [FamilyId] to the query by using a text field on the
main
form.

Thanks for any help.

Thanks
Andrew



:

Open the main form in design view.
Right-click the edge of the subform control, and choose Properties.
On the Data tab, what field is named in the LinkChildFields?

That field will inherit a value from the main form's field that is
named
in
LinkMasterFields. If that value is not appropriate, you will receive
the
Referential Integrity message.

For example, if the main form is at a new record and the field has
zero
as a
default value, the zero will prevent the subform creating the new
record.

If that is not the issue, post back with more information on the
source
for
the main form (table or query?), the source for the subform (table or
query), and the names and data types of the fields named in
LinkMasterFields
and LinkChildFields.


I have a strange issue that I can not figure out. I have a sub form
that
if
I open the sub form alone I can add records to the form. When I try
to
enter
records in the sub form as a part of the main form I get an error
about
referential integrity. Any thoughts on where I should look for why
this
issue is happening.

Thanks
Andrew
Click to expand...
Click to expand...
Click to expand...
 
Yep if I just open the sub form out side of the parent form I can add a
record. What is really strange is I can add a record as long as I do not
pick a student for the record and then save that record and come back to add
the student to the student field. I am just stuck on what is causing this.
Is there a way to use events to sync the sub form and the parent form with
out using the Master Child fields?

Thanks
Andrew


Allen Browne said:
I don't think the RowSource of the combo would be the issue.

You say that removing the MasterLinkFields/ChildLinkFields prevents the
error (even though it does not achieve the desired results, of course), so
it must be linked to something that's inherited as a result? Would be worth
persuing that further.

I would assume that the query that serves as the subform source is
updatable, i.e. you can add new records there without a problem?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Andrew said:
There was a 0 in the default value but that did not fix the issue. The
sub
form has a Combo box on it that I use this query to fill the combo box:

SELECT qryHelper.StudentId, qryHelper.FirstName, qryHelper.LastName
FROM qryHelper;

Could this be throwing it off?


Thanks for your help.

Thanks
Andrew


Allen Browne said:
So the subform gets the FamilyID field from qryHelper, which gets it from
your Student table?

Open the student table in design view.
Select the FamilyID field.
In in lower pane, remove the zero from the Default Value property.

Does that solve it?


Allen,

Thanks for your post I think you are on the right track but I am not
sure
what I am doing wrong. The Main form is a Family Record and the sub
form
is
a set of dates that are associated with a child in the family. When I
set
the Master and child link fields to the [FamilyId] I get this error
when I
clear the [FamilyId] from these fields the error goes away but the sub
form
does not change records I assume due to them not being linked.
[FamilId]
is
just a long int which is an auto number and the primary key in the
Family
table [tblFamily]. The sub form is based on a query [qryHelper] that
includes the [FamilyId] from a table of student names joined with a
table
of
dates associated with students.

[tblFamily]
FamilyId AutoNumber
Notes Memo
FamilyName Text

[qryHelper]
SELECT tblHelper.StudentId, tblHelper.NurseryDate,
tblHelper.ProgramDate,
tblStudent.FamilyId, tblStudent.LastName, tblStudent.FirstName
FROM tblStudent INNER JOIN tblHelper ON tblStudent.StudentId =
tblHelper.StudentId
WHERE (((tblStudent.FamilyId)=[Forms]![frmFamilyEntry]![txtId]));

I am passing the [FamilyId] to the query by using a text field on the
main
form.

Thanks for any help.

Thanks
Andrew



:

Open the main form in design view.
Right-click the edge of the subform control, and choose Properties.
On the Data tab, what field is named in the LinkChildFields?

That field will inherit a value from the main form's field that is
named
in
LinkMasterFields. If that value is not appropriate, you will receive
the
Referential Integrity message.

For example, if the main form is at a new record and the field has
zero
as a
default value, the zero will prevent the subform creating the new
record.

If that is not the issue, post back with more information on the
source
for
the main form (table or query?), the source for the subform (table or
query), and the names and data types of the fields named in
LinkMasterFields
and LinkChildFields.


I have a strange issue that I can not figure out. I have a sub form
that
if
I open the sub form alone I can add records to the form. When I try
to
enter
records in the sub form as a part of the main form I get an error
about
referential integrity. Any thoughts on where I should look for why
this
issue is happening.

Thanks
Andrew
Click to expand...
Click to expand...
Click to expand...
 
Not sure what else to suggest, Andrew.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Andrew said:
Yep if I just open the sub form out side of the parent form I can add a
record. What is really strange is I can add a record as long as I do not
pick a student for the record and then save that record and come back to
add
the student to the student field. I am just stuck on what is causing
this.
Is there a way to use events to sync the sub form and the parent form with
out using the Master Child fields?

Thanks
Andrew


Allen Browne said:
I don't think the RowSource of the combo would be the issue.

You say that removing the MasterLinkFields/ChildLinkFields prevents the
error (even though it does not achieve the desired results, of course),
so
it must be linked to something that's inherited as a result? Would be
worth
persuing that further.

I would assume that the query that serves as the subform source is
updatable, i.e. you can add new records there without a problem?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Andrew said:
There was a 0 in the default value but that did not fix the issue. The
sub
form has a Combo box on it that I use this query to fill the combo box:

SELECT qryHelper.StudentId, qryHelper.FirstName, qryHelper.LastName
FROM qryHelper;

Could this be throwing it off?


Thanks for your help.

Thanks
Andrew


:

So the subform gets the FamilyID field from qryHelper, which gets it
from
your Student table?

Open the student table in design view.
Select the FamilyID field.
In in lower pane, remove the zero from the Default Value property.

Does that solve it?


Allen,

Thanks for your post I think you are on the right track but I am not
sure
what I am doing wrong. The Main form is a Family Record and the sub
form
is
a set of dates that are associated with a child in the family. When
I
set
the Master and child link fields to the [FamilyId] I get this error
when I
clear the [FamilyId] from these fields the error goes away but the
sub
form
does not change records I assume due to them not being linked.
[FamilId]
is
just a long int which is an auto number and the primary key in the
Family
table [tblFamily]. The sub form is based on a query [qryHelper]
that
includes the [FamilyId] from a table of student names joined with a
table
of
dates associated with students.

[tblFamily]
FamilyId AutoNumber
Notes Memo
FamilyName Text

[qryHelper]
SELECT tblHelper.StudentId, tblHelper.NurseryDate,
tblHelper.ProgramDate,
tblStudent.FamilyId, tblStudent.LastName, tblStudent.FirstName
FROM tblStudent INNER JOIN tblHelper ON tblStudent.StudentId =
tblHelper.StudentId
WHERE (((tblStudent.FamilyId)=[Forms]![frmFamilyEntry]![txtId]));

I am passing the [FamilyId] to the query by using a text field on
the
main
form.

Thanks for any help.

Thanks
Andrew



:

Open the main form in design view.
Right-click the edge of the subform control, and choose Properties.
On the Data tab, what field is named in the LinkChildFields?

That field will inherit a value from the main form's field that is
named
in
LinkMasterFields. If that value is not appropriate, you will
receive
the
Referential Integrity message.

For example, if the main form is at a new record and the field has
zero
as a
default value, the zero will prevent the subform creating the new
record.

If that is not the issue, post back with more information on the
source
for
the main form (table or query?), the source for the subform (table
or
query), and the names and data types of the fields named in
LinkMasterFields
and LinkChildFields.


I have a strange issue that I can not figure out. I have a sub
form
that
if
I open the sub form alone I can add records to the form. When I
try
to
enter
records in the sub form as a part of the main form I get an error
about
referential integrity. Any thoughts on where I should look for
why
this
issue is happening.

Thanks
Andrew
Click to expand...
Click to expand...
Click to expand...
 
Ok Allen
I greatly appreciate all your help I will keep on looking.

Thanks
Andrew


Allen Browne said:
Not sure what else to suggest, Andrew.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Andrew said:
Yep if I just open the sub form out side of the parent form I can add a
record. What is really strange is I can add a record as long as I do not
pick a student for the record and then save that record and come back to
add
the student to the student field. I am just stuck on what is causing
this.
Is there a way to use events to sync the sub form and the parent form with
out using the Master Child fields?

Thanks
Andrew


Allen Browne said:
I don't think the RowSource of the combo would be the issue.

You say that removing the MasterLinkFields/ChildLinkFields prevents the
error (even though it does not achieve the desired results, of course),
so
it must be linked to something that's inherited as a result? Would be
worth
persuing that further.

I would assume that the query that serves as the subform source is
updatable, i.e. you can add new records there without a problem?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

There was a 0 in the default value but that did not fix the issue. The
sub
form has a Combo box on it that I use this query to fill the combo box:

SELECT qryHelper.StudentId, qryHelper.FirstName, qryHelper.LastName
FROM qryHelper;

Could this be throwing it off?


Thanks for your help.

Thanks
Andrew


:

So the subform gets the FamilyID field from qryHelper, which gets it
from
your Student table?

Open the student table in design view.
Select the FamilyID field.
In in lower pane, remove the zero from the Default Value property.

Does that solve it?


Allen,

Thanks for your post I think you are on the right track but I am not
sure
what I am doing wrong. The Main form is a Family Record and the sub
form
is
a set of dates that are associated with a child in the family. When
I
set
the Master and child link fields to the [FamilyId] I get this error
when I
clear the [FamilyId] from these fields the error goes away but the
sub
form
does not change records I assume due to them not being linked.
[FamilId]
is
just a long int which is an auto number and the primary key in the
Family
table [tblFamily]. The sub form is based on a query [qryHelper]
that
includes the [FamilyId] from a table of student names joined with a
table
of
dates associated with students.

[tblFamily]
FamilyId AutoNumber
Notes Memo
FamilyName Text

[qryHelper]
SELECT tblHelper.StudentId, tblHelper.NurseryDate,
tblHelper.ProgramDate,
tblStudent.FamilyId, tblStudent.LastName, tblStudent.FirstName
FROM tblStudent INNER JOIN tblHelper ON tblStudent.StudentId =
tblHelper.StudentId
WHERE (((tblStudent.FamilyId)=[Forms]![frmFamilyEntry]![txtId]));

I am passing the [FamilyId] to the query by using a text field on
the
main
form.

Thanks for any help.

Thanks
Andrew



:

Open the main form in design view.
Right-click the edge of the subform control, and choose Properties.
On the Data tab, what field is named in the LinkChildFields?

That field will inherit a value from the main form's field that is
named
in
LinkMasterFields. If that value is not appropriate, you will
receive
the
Referential Integrity message.

For example, if the main form is at a new record and the field has
zero
as a
default value, the zero will prevent the subform creating the new
record.

If that is not the issue, post back with more information on the
source
for
the main form (table or query?), the source for the subform (table
or
query), and the names and data types of the fields named in
LinkMasterFields
and LinkChildFields.


I have a strange issue that I can not figure out. I have a sub
form
that
if
I open the sub form alone I can add records to the form. When I
try
to
enter
records in the sub form as a part of the main form I get an error
about
referential integrity. Any thoughts on where I should look for
why
this
issue is happening.

Thanks
Andrew
Click to expand...
Click to expand...
Click to expand...
 
Back
Top