I Cannot Delete a Record with a Subrecord

G

Guest

Can anyone please help me? I am a greeny Database developer. I created a
delete button using the button wizard but when I click on it I get the
following message "The Record cannot be deleted because table ‘tblCLines’
includes related records.cannot delete a record." I have eight other sub
forms and they all delete the reodrds fine. But this fsubCLines does not
delete the record. Any ideas why?

I have a table called tblBatchHeader and and other table called tblCLines.
The their is a frmBatchHeader and a subform in a tab control called
fsubCLines. The sub form has three fields. lngzPatientNumber (int) is
manually typed, chrFieldCodes (txt) is a combo box that is populated using
the following SQL statement:

SELECT tblFieldCodes.chrFieldCodes AS Code,
tblFieldCodes.chrFieldCodesDescription AS Description FROM tblFieldCodes;

and chrNewData (txt) that is populated with values from another table and is
triggered by whaterever value it slected in the chrFieldCodes it used the
following SQL statement:

SELECT tblNewData.chrNewData AS [Data Value], tblNewData.chrNewDataFormat AS
[Data Format], tblNewData.chrNewDataDescription AS Description,
tblNewData.chrFieldCodes FROM tblNewData WHERE
(((tblNewData.chrFieldCodes)=Forms!frmBatchHeader!ChildfsubCLines.Form!cboFieldCodes));
 
J

J. Goddard

Mary said:
Can anyone please help me? I am a greeny Database developer. I created a
delete button using the button wizard but when I click on it I get the
following message "The Record cannot be deleted because table ‘tblCLines’
includes related records.cannot delete a record." I have eight other sub
forms and they all delete the reodrds fine. But this fsubCLines does not
delete the record. Any ideas why?
Click to expand...
MS Access is enforcing referential integrity set up when relationships
are defined. What you are trying to do in deleting a record fsubclines
is delete a record from the "one" side of a one-to-many relationship,
which Access won't let you do. The deletions in the sub-forms are
either from the "many" side of a relationship, or they are not in a
defined relationship at all.

For example, take the often cited example of customers and orders. A
customer can have many orders (one-to-many). If you delete a customer
record (the "one" side), then all the related order records (the "Many"
side) have no customer who placed the order (i.e. they would have a
customerID that didn't point to any record in the customer table).

You have two choices - first is to delete all the records from the
"many" side, and then delete the record from the "one" side or

Use the cascade delete option of the relationship. If you don't know
what that is, don't use it! Mistakes made when cascade delete is in
effect can be disastrous.

John

I have a table called tblBatchHeader and and other table called tblCLines.
The their is a frmBatchHeader and a subform in a tab control called
fsubCLines. The sub form has three fields. lngzPatientNumber (int) is
manually typed, chrFieldCodes (txt) is a combo box that is populated using
the following SQL statement:

SELECT tblFieldCodes.chrFieldCodes AS Code,
tblFieldCodes.chrFieldCodesDescription AS Description FROM tblFieldCodes;

and chrNewData (txt) that is populated with values from another table and is
triggered by whaterever value it slected in the chrFieldCodes it used the
following SQL statement:

SELECT tblNewData.chrNewData AS [Data Value], tblNewData.chrNewDataFormat AS
[Data Format], tblNewData.chrNewDataDescription AS Description,
tblNewData.chrFieldCodes FROM tblNewData WHERE
(((tblNewData.chrFieldCodes)=Forms!frmBatchHeader!ChildfsubCLines.Form!cboFieldCodes));
Click to expand...
 
Top