If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
A delete button on a main form doesn't work
I have a form based on a table of names and addresses with an autonumber
Primary Key. It contains a subform based on another table of appointments. Adding new records works perfectly. I need to be able to delete appointments from the sub form but when I try to operate the button I get the following message. 'The record cannot be dleted or changed because table "tblappoiontments"include related records.' Help? I have a deadline of Monday coming and cannot find a cogent answer either on Microsoft on line help or indeed in this forums previously addressed issues. |
#2
|
|||
|
|||
Sounds as though you've got referential integrity set up. Take a look at the
Relationships and see what there is related to your appointment table. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Iain Mundell" Iain wrote in message ... I have a form based on a table of names and addresses with an autonumber Primary Key. It contains a subform based on another table of appointments. Adding new records works perfectly. I need to be able to delete appointments from the sub form but when I try to operate the button I get the following message. 'The record cannot be dleted or changed because table "tblappoiontments"include related records.' Help? I have a deadline of Monday coming and cannot find a cogent answer either on Microsoft on line help or indeed in this forums previously addressed issues. |
#3
|
|||
|
|||
Hi Doug.
I have three tables. Table Patient, Table Staff and Table Appointments All in relationship via their P keys and foreign keys Help "Iain Mundell" wrote: I have a form based on a table of names and addresses with an autonumber Primary Key. It contains a subform based on another table of appointments. Adding new records works perfectly. I need to be able to delete appointments from the sub form but when I try to operate the button I get the following message. 'The record cannot be dleted or changed because table "tblappoiontments"include related records.' Help? I have a deadline of Monday coming and cannot find a cogent answer either on Microsoft on line help or indeed in this forums previously addressed issues. |
#5
|
|||
|
|||
Hi Doug,
You'l be regretting having picked this one up. I'mm off to look at the rferential/ cascading etc...but in fact all I want to be able to do is enter or delete new appoiontment details in the subform 'tbleappointments' that has been created using the form wizard with Patients tabel as the prime and appointments as the sub.... Iain "Douglas J. Steele" wrote: Well, however you've set up referential integrity is what's preventing you from doing your deletion. It sounds as though you're trying to delete a row from either the Patient or Staff tables, and the row is linked to rows in the Appointments table. If you want the delete to happen and delete any related rows in the Appointments table, you can set up Cascade Delete. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Iain Mundell" Iain wrote in message ... Hi Doug. I have three tables. Table Patient, Table Staff and Table Appointments All in relationship via their P keys and foreign keys Help "Iain Mundell" wrote: I have a form based on a table of names and addresses with an autonumber Primary Key. It contains a subform based on another table of appointments. Adding new records works perfectly. I need to be able to delete appointments from the sub form but when I try to operate the button I get the following message. 'The record cannot be dleted or changed because table "tblappoiontments"include related records.' Help? I have a deadline of Monday coming and cannot find a cogent answer either on Microsoft on line help or indeed in this forums previously addressed issues. |
#6
|
|||
|
|||
Hi again Doug,
I checked and tried various formats of the relation shipps...as it stands it is 'enforced referential integrity' a 'one to many' relationship between the Patient table and the appointments table. I tried 'cascade delete related records' box ticked and the button worked except that it was about to delete all that patient's records! ??? "Douglas J. Steele" wrote: Well, however you've set up referential integrity is what's preventing you from doing your deletion. It sounds as though you're trying to delete a row from either the Patient or Staff tables, and the row is linked to rows in the Appointments table. If you want the delete to happen and delete any related rows in the Appointments table, you can set up Cascade Delete. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Iain Mundell" Iain wrote in message ... Hi Doug. I have three tables. Table Patient, Table Staff and Table Appointments All in relationship via their P keys and foreign keys Help "Iain Mundell" wrote: I have a form based on a table of names and addresses with an autonumber Primary Key. It contains a subform based on another table of appointments. Adding new records works perfectly. I need to be able to delete appointments from the sub form but when I try to operate the button I get the following message. 'The record cannot be dleted or changed because table "tblappoiontments"include related records.' Help? I have a deadline of Monday coming and cannot find a cogent answer either on Microsoft on line help or indeed in this forums previously addressed issues. |
#7
|
|||
|
|||
It's possible that when you created the relationships, Access guessed
incorrectly about the nature of the tables. I would have expected that your Staff and Patient tables each have a field like StaffId and PatientId set as the Primary Key for the table. I further expect that your Appointment table has the two fields StaffId and PatientId in it, along with a date/time field (and probably some other fields). When you set the relationships up by dragging the StaffId field from the Staff table onto the StaffId field in the Appointment table, the resultant line should have put a 1 at the Staff end, and an infinity (an 8 on its side) at the Appointment end. Similary, the relationship line between Patient and Appointment should have a 1 at the Patient end and an infinity at the Appointment end. With that setup, you should be able to delete appointments without any problems. If you try to delete a row from Staff or from Patient, and there's one or more rows in Appointment linked to either that Staff or Patient entry, that's when Referential Integrity should kick in. With Cascade Delete set, deleting the Staff member or the Patient entry would delete all appointments linked to that entry. Without Cascade Delete, you will be stopped from doing the deletion. If that's not what you're seeing, do you have the appropriate Primary Keys set on your Patient and Staff tables? That's critical for Access to recognize the relationship correctly. If everything looks as though it's set up correctly, post back more details here. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Iain Mundell" wrote in message ... Hi again Doug, I checked and tried various formats of the relation shipps...as it stands it is 'enforced referential integrity' a 'one to many' relationship between the Patient table and the appointments table. I tried 'cascade delete related records' box ticked and the button worked except that it was about to delete all that patient's records! ??? "Douglas J. Steele" wrote: Well, however you've set up referential integrity is what's preventing you from doing your deletion. It sounds as though you're trying to delete a row from either the Patient or Staff tables, and the row is linked to rows in the Appointments table. If you want the delete to happen and delete any related rows in the Appointments table, you can set up Cascade Delete. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Iain Mundell" Iain wrote in message ... Hi Doug. I have three tables. Table Patient, Table Staff and Table Appointments All in relationship via their P keys and foreign keys Help "Iain Mundell" wrote: I have a form based on a table of names and addresses with an autonumber Primary Key. It contains a subform based on another table of appointments. Adding new records works perfectly. I need to be able to delete appointments from the sub form but when I try to operate the button I get the following message. 'The record cannot be dleted or changed because table "tblappoiontments"include related records.' Help? I have a deadline of Monday coming and cannot find a cogent answer either on Microsoft on line help or indeed in this forums previously addressed issues. |
#8
|
|||
|
|||
Hi Doug,
here is the situation Three Tables First 'tblpatient' with these entities PatientID (Primary Key) Title Firstname Secondname Street Town Postcode TelephoneNumber DateofBirth Gender second 'tblStaff' with StaffID (Primary key) Titel FirstName SecondName Street Town PostCode TelephoneNumber dateofBirth RoomNumber AvailableDays Gender third 'tblAppointment' comprising AppointmentID (Primary Key) PatientID( Foreign Key) StaffID(ForeignKey) Date Time AppKept RoomNumber In the relationships view it is as you said at tblePatiemts end a 1 and at the tblappointments the infinity sign/ likewise a i at the tblstaff end and the infinity sign at the tbleappointments end. In relationships dialogue box 'Enforced Referential Integrity' is ticked and the other two, 'Cascade Update Related Fields' , and 'Cascade Delte Related Records' are not ticked Relationship Type is noted as 'One -To-Many' I experimented by ticking the other two cascades...together and individiually with Referential Integrity and without...no joy. messdages such as "Relationships that specify cascading deletes are about to cause 1 record(s) in this table and reated tables to be deleted. Are you sure you want to dletethese records?" Please note when that warning is on scree the subform shows the record highlighted for deleteion and the adjacent one both shown as having gone...therefore I click No. Should I just go for it? I'm concerned that I'll lose some hard won information....? thanks for your interest The appointments form is straightforward "Douglas J. Steele" wrote: It's possible that when you created the relationships, Access guessed incorrectly about the nature of the tables. I would have expected that your Staff and Patient tables each have a field like StaffId and PatientId set as the Primary Key for the table. I further expect that your Appointment table has the two fields StaffId and PatientId in it, along with a date/time field (and probably some other fields). When you set the relationships up by dragging the StaffId field from the Staff table onto the StaffId field in the Appointment table, the resultant line should have put a 1 at the Staff end, and an infinity (an 8 on its side) at the Appointment end. Similary, the relationship line between Patient and Appointment should have a 1 at the Patient end and an infinity at the Appointment end. With that setup, you should be able to delete appointments without any problems. If you try to delete a row from Staff or from Patient, and there's one or more rows in Appointment linked to either that Staff or Patient entry, that's when Referential Integrity should kick in. With Cascade Delete set, deleting the Staff member or the Patient entry would delete all appointments linked to that entry. Without Cascade Delete, you will be stopped from doing the deletion. If that's not what you're seeing, do you have the appropriate Primary Keys set on your Patient and Staff tables? That's critical for Access to recognize the relationship correctly. If everything looks as though it's set up correctly, post back more details here. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Iain Mundell" wrote in message ... Hi again Doug, I checked and tried various formats of the relation shipps...as it stands it is 'enforced referential integrity' a 'one to many' relationship between the Patient table and the appointments table. I tried 'cascade delete related records' box ticked and the button worked except that it was about to delete all that patient's records! ??? "Douglas J. Steele" wrote: Well, however you've set up referential integrity is what's preventing you from doing your deletion. It sounds as though you're trying to delete a row from either the Patient or Staff tables, and the row is linked to rows in the Appointments table. If you want the delete to happen and delete any related rows in the Appointments table, you can set up Cascade Delete. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Iain Mundell" Iain wrote in message ... Hi Doug. I have three tables. Table Patient, Table Staff and Table Appointments All in relationship via their P keys and foreign keys Help "Iain Mundell" wrote: I have a form based on a table of names and addresses with an autonumber Primary Key. It contains a subform based on another table of appointments. Adding new records works perfectly. I need to be able to delete appointments from the sub form but when I try to operate the button I get the following message. 'The record cannot be dleted or changed because table "tblappoiontments"include related records.' Help? I have a deadline of Monday coming and cannot find a cogent answer either on Microsoft on line help or indeed in this forums previously addressed issues. |
#9
|
|||
|
|||
Further grief,
Hi Doug Shortly after posting my last detail I tried editing the relationships one more time by unticking the Enforce Referential Integrity and trying to delete a single record that I had just entered. I got the warning that record(s) would be deleted but takinga deep breath I jumped and it removed all the records (2) against this patient.....I then went back to re -edit and tick the ERI box but now it wont let me... saying" this applicatioon can't create this relationship and ERI Data in the tbleappointments violates referential integrity rules for example there may be records relating to an employee in the related table, but no record for the employee in the primary table Edit the data so that records in the primary table exist for all related records. If you want to creat the relationship without following the rules of referential integrity, clear the eFI check box." I then went back and deleted the record from tblpatients that I had tried to delet a single recod from but no way hose iit still wont play... got a razor blade? "Iain Mundell" wrote: Hi Doug, here is the situation Three Tables First 'tblpatient' with these entities PatientID (Primary Key) Title Firstname Secondname Street Town Postcode TelephoneNumber DateofBirth Gender second 'tblStaff' with StaffID (Primary key) Titel FirstName SecondName Street Town PostCode TelephoneNumber dateofBirth RoomNumber AvailableDays Gender third 'tblAppointment' comprising AppointmentID (Primary Key) PatientID( Foreign Key) StaffID(ForeignKey) Date Time AppKept RoomNumber In the relationships view it is as you said at tblePatiemts end a 1 and at the tblappointments the infinity sign/ likewise a i at the tblstaff end and the infinity sign at the tbleappointments end. In relationships dialogue box 'Enforced Referential Integrity' is ticked and the other two, 'Cascade Update Related Fields' , and 'Cascade Delte Related Records' are not ticked Relationship Type is noted as 'One -To-Many' I experimented by ticking the other two cascades...together and individiually with Referential Integrity and without...no joy. messdages such as "Relationships that specify cascading deletes are about to cause 1 record(s) in this table and reated tables to be deleted. Are you sure you want to dletethese records?" Please note when that warning is on scree the subform shows the record highlighted for deleteion and the adjacent one both shown as having gone...therefore I click No. Should I just go for it? I'm concerned that I'll lose some hard won information....? thanks for your interest The appointments form is straightforward "Douglas J. Steele" wrote: It's possible that when you created the relationships, Access guessed incorrectly about the nature of the tables. I would have expected that your Staff and Patient tables each have a field like StaffId and PatientId set as the Primary Key for the table. I further expect that your Appointment table has the two fields StaffId and PatientId in it, along with a date/time field (and probably some other fields). When you set the relationships up by dragging the StaffId field from the Staff table onto the StaffId field in the Appointment table, the resultant line should have put a 1 at the Staff end, and an infinity (an 8 on its side) at the Appointment end. Similary, the relationship line between Patient and Appointment should have a 1 at the Patient end and an infinity at the Appointment end. With that setup, you should be able to delete appointments without any problems. If you try to delete a row from Staff or from Patient, and there's one or more rows in Appointment linked to either that Staff or Patient entry, that's when Referential Integrity should kick in. With Cascade Delete set, deleting the Staff member or the Patient entry would delete all appointments linked to that entry. Without Cascade Delete, you will be stopped from doing the deletion. If that's not what you're seeing, do you have the appropriate Primary Keys set on your Patient and Staff tables? That's critical for Access to recognize the relationship correctly. If everything looks as though it's set up correctly, post back more details here. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Iain Mundell" wrote in message ... Hi again Doug, I checked and tried various formats of the relation shipps...as it stands it is 'enforced referential integrity' a 'one to many' relationship between the Patient table and the appointments table. I tried 'cascade delete related records' box ticked and the button worked except that it was about to delete all that patient's records! ??? "Douglas J. Steele" wrote: Well, however you've set up referential integrity is what's preventing you from doing your deletion. It sounds as though you're trying to delete a row from either the Patient or Staff tables, and the row is linked to rows in the Appointments table. If you want the delete to happen and delete any related rows in the Appointments table, you can set up Cascade Delete. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Iain Mundell" Iain wrote in message ... Hi Doug. I have three tables. Table Patient, Table Staff and Table Appointments All in relationship via their P keys and foreign keys Help "Iain Mundell" wrote: I have a form based on a table of names and addresses with an autonumber Primary Key. It contains a subform based on another table of appointments. Adding new records works perfectly. I need to be able to delete appointments from the sub form but when I try to operate the button I get the following message. 'The record cannot be dleted or changed because table "tblappoiontments"include related records.' Help? I have a deadline of Monday coming and cannot find a cogent answer either on Microsoft on line help or indeed in this forums previously addressed issues. |
#10
|
|||
|
|||
Sorry, I'm having problems visually what you're doing.
How are you doing the delete? In other words, what's the code you're using, and where is it (on the form or the subform)? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Iain Mundell" wrote in message ... Further grief, Hi Doug Shortly after posting my last detail I tried editing the relationships one more time by unticking the Enforce Referential Integrity and trying to delete a single record that I had just entered. I got the warning that record(s) would be deleted but takinga deep breath I jumped and it removed all the records (2) against this patient.....I then went back to re -edit and tick the ERI box but now it wont let me... saying" this applicatioon can't create this relationship and ERI Data in the tbleappointments violates referential integrity rules for example there may be records relating to an employee in the related table, but no record for the employee in the primary table Edit the data so that records in the primary table exist for all related records. If you want to creat the relationship without following the rules of referential integrity, clear the eFI check box." I then went back and deleted the record from tblpatients that I had tried to delet a single recod from but no way hose iit still wont play... got a razor blade? "Iain Mundell" wrote: Hi Doug, here is the situation Three Tables First 'tblpatient' with these entities PatientID (Primary Key) Title Firstname Secondname Street Town Postcode TelephoneNumber DateofBirth Gender second 'tblStaff' with StaffID (Primary key) Titel FirstName SecondName Street Town PostCode TelephoneNumber dateofBirth RoomNumber AvailableDays Gender third 'tblAppointment' comprising AppointmentID (Primary Key) PatientID( Foreign Key) StaffID(ForeignKey) Date Time AppKept RoomNumber In the relationships view it is as you said at tblePatiemts end a 1 and at the tblappointments the infinity sign/ likewise a i at the tblstaff end and the infinity sign at the tbleappointments end. In relationships dialogue box 'Enforced Referential Integrity' is ticked and the other two, 'Cascade Update Related Fields' , and 'Cascade Delte Related Records' are not ticked Relationship Type is noted as 'One -To-Many' I experimented by ticking the other two cascades...together and individiually with Referential Integrity and without...no joy. messdages such as "Relationships that specify cascading deletes are about to cause 1 record(s) in this table and reated tables to be deleted. Are you sure you want to dletethese records?" Please note when that warning is on scree the subform shows the record highlighted for deleteion and the adjacent one both shown as having gone...therefore I click No. Should I just go for it? I'm concerned that I'll lose some hard won information....? thanks for your interest The appointments form is straightforward "Douglas J. Steele" wrote: It's possible that when you created the relationships, Access guessed incorrectly about the nature of the tables. I would have expected that your Staff and Patient tables each have a field like StaffId and PatientId set as the Primary Key for the table. I further expect that your Appointment table has the two fields StaffId and PatientId in it, along with a date/time field (and probably some other fields). When you set the relationships up by dragging the StaffId field from the Staff table onto the StaffId field in the Appointment table, the resultant line should have put a 1 at the Staff end, and an infinity (an 8 on its side) at the Appointment end. Similary, the relationship line between Patient and Appointment should have a 1 at the Patient end and an infinity at the Appointment end. With that setup, you should be able to delete appointments without any problems. If you try to delete a row from Staff or from Patient, and there's one or more rows in Appointment linked to either that Staff or Patient entry, that's when Referential Integrity should kick in. With Cascade Delete set, deleting the Staff member or the Patient entry would delete all appointments linked to that entry. Without Cascade Delete, you will be stopped from doing the deletion. If that's not what you're seeing, do you have the appropriate Primary Keys set on your Patient and Staff tables? That's critical for Access to recognize the relationship correctly. If everything looks as though it's set up correctly, post back more details here. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Iain Mundell" wrote in message ... Hi again Doug, I checked and tried various formats of the relation shipps...as it stands it is 'enforced referential integrity' a 'one to many' relationship between the Patient table and the appointments table. I tried 'cascade delete related records' box ticked and the button worked except that it was about to delete all that patient's records! ??? "Douglas J. Steele" wrote: Well, however you've set up referential integrity is what's preventing you from doing your deletion. It sounds as though you're trying to delete a row from either the Patient or Staff tables, and the row is linked to rows in the Appointments table. If you want the delete to happen and delete any related rows in the Appointments table, you can set up Cascade Delete. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Iain Mundell" Iain wrote in message ... Hi Doug. I have three tables. Table Patient, Table Staff and Table Appointments All in relationship via their P keys and foreign keys Help "Iain Mundell" wrote: I have a form based on a table of names and addresses with an autonumber Primary Key. It contains a subform based on another table of appointments. Adding new records works perfectly. I need to be able to delete appointments from the sub form but when I try to operate the button I get the following message. 'The record cannot be dleted or changed because table "tblappoiontments"include related records.' Help? I have a deadline of Monday coming and cannot find a cogent answer either on Microsoft on line help or indeed in this forums previously addressed issues. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Requerying a pop up form to display in the main form | Jennifer P | Using Forms | 13 | April 5th, 2005 06:59 PM |
Dates in a listbox connected to a form... | RusCat | Using Forms | 13 | November 25th, 2004 02:31 AM |
Recordset in subform based on field in parent form | Lyn | General Discussion | 15 | June 14th, 2004 03:10 PM |
Filter subform combobox on option button in main form | Emma | Using Forms | 1 | June 12th, 2004 12:24 AM |
Form Doesn't Go To New Record | Steve | New Users | 15 | May 16th, 2004 04:33 PM |