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.

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.

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.

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.

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.

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.

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.

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.

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.

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.