A delete button on a main form doesn't work

[Guest]

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.

 

[Douglas J. Steele]

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.

 

[Guest]

Hi Doug.

I have three tables. Table Patient, Table Staff and Table Appointments All
in relationship via their P keys and foreign keys

Help

 

[Douglas J. Steele]

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.

 

[Guest]

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

 

[Guest]

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]

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.

 

[Guest]

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

 

[Guest]

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?

 

[Douglas J. Steele]

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)?