problem cascading deleting

[Guest]

I have a training database in which I have tables including a bookings table
(details of a employee being booked on a course) and employee info
table(which includes the employee name, post title etc). When a booking is
made, the administrator selects the employees name from a dropdown list based
on the employeeinfo table. If however, that person pulls out of the training
and the administrator deletes the booking on the form, the persons record is
deleted from the employeeinfo table - not good. Any other bookings for that
person are still in the bookings table, but as far as the administrator is
concerned, the person no longer exists in the database as they are deleted
from the employeeinfo table.


the code behind the bookings form is
SELECT bookings.[event ID], bookings.[booking ID], bookings.employeeID,
employeeinfo.surname, employeeinfo.forename, employeeinfo.[post/role],
employeeinfo.Project, bookings.[booking taken], bookings.[confirmed date],
bookings.confirmed, bookings.status, bookings.explain, bookings.[cert
issued], bookings.datemodified, bookings.datecreated, employeeinfo.[support
needs]
FROM employeeinfo INNER JOIN bookings ON employeeinfo.[employee ID] =
bookings.employeeID
ORDER BY bookings.[booking taken];


I have checked the relationship between the tables and they do not have
cascade delete related records switched on.

How can I stop delete bookings in the form without deleting the person?

regards

 

[Roger Carlson]

First of all, it doesn't sound like you even have a relationship
established, because the database would not allow you to delete a parent
record if it would leave orphaned child records. Make sure your
relationship has "Enforce Referential Integrity" checked. (Also that the
line between the tables has a 1 on one end and the infinity symbol at the
other.) If this is not checked, you don't really have a relationship. At
best you have a hint for how to join tables in a query.

Secondly, I'd suggest that you use a form/subform set up for this type
relationship. The main form would have the parent table (one-side table) as
it's record source and the subform would have the child table (many-side
table) as its record source. That way, deleting a particular booking will
not delete the employee.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "FormSubform.mdb" which illustrates this.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

thanks for that.Your right, my relationship is not set up properly. This is
partly because I made a change to the database when it already contained
data. It is therefore telling me it is breaking integrity rules. What is the
best way to set up the tables with their relationships whilst they are empty,
then import the data from existing tables? I want to keep the existing
autonumbering etc.

First of all, it doesn't sound like you even have a relationship
established, because the database would not allow you to delete a parent
record if it would leave orphaned child records. Make sure your
relationship has "Enforce Referential Integrity" checked. (Also that the
line between the tables has a 1 on one end and the infinity symbol at the
other.) If this is not checked, you don't really have a relationship. At
best you have a hint for how to join tables in a query.

Secondly, I'd suggest that you use a form/subform set up for this type
relationship. The main form would have the parent table (one-side table) as
it's record source and the subform would have the child table (many-side
table) as its record source. That way, deleting a particular booking will
not delete the employee.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "FormSubform.mdb" which illustrates this.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have a training database in which I have tables including a bookings table
(details of a employee being booked on a course) and employee info
table(which includes the employee name, post title etc). When a booking is
made, the administrator selects the employees name from a dropdown list based
on the employeeinfo table. If however, that person pulls out of the training
and the administrator deletes the booking on the form, the persons record is
deleted from the employeeinfo table - not good. Any other bookings for that
person are still in the bookings table, but as far as the administrator is
concerned, the person no longer exists in the database as they are deleted
from the employeeinfo table.


the code behind the bookings form is
SELECT bookings.[event ID], bookings.[booking ID], bookings.employeeID,
employeeinfo.surname, employeeinfo.forename, employeeinfo.[post/role],
employeeinfo.Project, bookings.[booking taken], bookings.[confirmed date],
bookings.confirmed, bookings.status, bookings.explain, bookings.[cert
issued], bookings.datemodified, bookings.datecreated, employeeinfo.[support
needs]
FROM employeeinfo INNER JOIN bookings ON employeeinfo.[employee ID] =
bookings.employeeID
ORDER BY bookings.[booking taken];


I have checked the relationship between the tables and they do not have
cascade delete related records switched on.

How can I stop delete bookings in the form without deleting the person?

regards

 

[Guest]

OK I have established the relationship as a one to many and imported the data
again etc, but still have the same problem. The booking form is already a
subform on a parent form. All I am wanting is the person to be 'looked up'
from the employeeinfo table and the details displayed against the booking.
Does this make sense? Have I made the subform already complicated?

First of all, it doesn't sound like you even have a relationship
established, because the database would not allow you to delete a parent
record if it would leave orphaned child records. Make sure your
relationship has "Enforce Referential Integrity" checked. (Also that the
line between the tables has a 1 on one end and the infinity symbol at the
other.) If this is not checked, you don't really have a relationship. At
best you have a hint for how to join tables in a query.

Secondly, I'd suggest that you use a form/subform set up for this type
relationship. The main form would have the parent table (one-side table) as
it's record source and the subform would have the child table (many-side
table) as its record source. That way, deleting a particular booking will
not delete the employee.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "FormSubform.mdb" which illustrates this.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have a training database in which I have tables including a bookings table
(details of a employee being booked on a course) and employee info
table(which includes the employee name, post title etc). When a booking is
made, the administrator selects the employees name from a dropdown list based
on the employeeinfo table. If however, that person pulls out of the training
and the administrator deletes the booking on the form, the persons record is
deleted from the employeeinfo table - not good. Any other bookings for that
person are still in the bookings table, but as far as the administrator is
concerned, the person no longer exists in the database as they are deleted
from the employeeinfo table.


the code behind the bookings form is
SELECT bookings.[event ID], bookings.[booking ID], bookings.employeeID,
employeeinfo.surname, employeeinfo.forename, employeeinfo.[post/role],
employeeinfo.Project, bookings.[booking taken], bookings.[confirmed date],
bookings.confirmed, bookings.status, bookings.explain, bookings.[cert
issued], bookings.datemodified, bookings.datecreated, employeeinfo.[support
needs]
FROM employeeinfo INNER JOIN bookings ON employeeinfo.[employee ID] =
bookings.employeeID
ORDER BY bookings.[booking taken];


I have checked the relationship between the tables and they do not have
cascade delete related records switched on.

How can I stop delete bookings in the form without deleting the person?

regards

 

[Roger Carlson]

It is usually best to create your relationships before there is any data in
the tables. However it IS possible to relate tables with existing data.
The only catch is that your data has to match the new relationship. If you
try to check Referential Integrity and there is already an orphaned record,
Access won't let you establish the relationship until you fix the data by
either deleting the orphaned record or adding a related parent record.

One way to find these orphaned records is by creating an Unmatched Query
(there's a wizard for that). Once you've corrected them all, you can create
your relationship.

As for keeping your Autonumbers, you're only concern should be whether they
match between related records. The Autonumber should never be used for any
other purpose. It should never matter to the user what the autonumber value
is.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

thanks for that.Your right, my relationship is not set up properly. This is
partly because I made a change to the database when it already contained
data. It is therefore telling me it is breaking integrity rules. What is the
best way to set up the tables with their relationships whilst they are empty,
then import the data from existing tables? I want to keep the existing
autonumbering etc.

First of all, it doesn't sound like you even have a relationship
established, because the database would not allow you to delete a parent
record if it would leave orphaned child records. Make sure your
relationship has "Enforce Referential Integrity" checked. (Also that the
line between the tables has a 1 on one end and the infinity symbol at the
other.) If this is not checked, you don't really have a relationship. At
best you have a hint for how to join tables in a query.

Secondly, I'd suggest that you use a form/subform set up for this type
relationship. The main form would have the parent table (one-side table) as
it's record source and the subform would have the child table (many-side
table) as its record source. That way, deleting a particular booking will
not delete the employee.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "FormSubform.mdb" which illustrates this.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have a training database in which I have tables including a bookings table
(details of a employee being booked on a course) and employee info
table(which includes the employee name, post title etc). When a booking is
made, the administrator selects the employees name from a dropdown

list
based

on the employeeinfo table. If however, that person pulls out of the training
and the administrator deletes the booking on the form, the persons

record
is

deleted from the employeeinfo table - not good. Any other bookings for that
person are still in the bookings table, but as far as the administrator is
concerned, the person no longer exists in the database as they are deleted
from the employeeinfo table.


the code behind the bookings form is
SELECT bookings.[event ID], bookings.[booking ID], bookings.employeeID,
employeeinfo.surname, employeeinfo.forename, employeeinfo.[post/role],
employeeinfo.Project, bookings.[booking taken], bookings.[confirmed date],
bookings.confirmed, bookings.status, bookings.explain, bookings.[cert
issued], bookings.datemodified, bookings.datecreated, employeeinfo.[support
needs]
FROM employeeinfo INNER JOIN bookings ON employeeinfo.[employee ID] =
bookings.employeeID
ORDER BY bookings.[booking taken];


I have checked the relationship between the tables and they do not have
cascade delete related records switched on.

How can I stop delete bookings in the form without deleting the person?

regards

 

[Roger Carlson]

That's because your form (or subform rather) is still based on a JOIN query.
All you really need (from the form's perspective) is a combo box to select
the employee and store his or her record number. But you want to user to
see more employee data, is that it?

One way to do this is to have your combobox RowSource contain the other
fields, then write these values into UNBOUND text boxes on the form in the
AfterUpdate event of the combo and the OnCurrent event of the subform.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

OK I have established the relationship as a one to many and imported the data
again etc, but still have the same problem. The booking form is already a
subform on a parent form. All I am wanting is the person to be 'looked up'
from the employeeinfo table and the details displayed against the booking.
Does this make sense? Have I made the subform already complicated?

First of all, it doesn't sound like you even have a relationship
established, because the database would not allow you to delete a parent
record if it would leave orphaned child records. Make sure your
relationship has "Enforce Referential Integrity" checked. (Also that the
line between the tables has a 1 on one end and the infinity symbol at the
other.) If this is not checked, you don't really have a relationship. At
best you have a hint for how to join tables in a query.

Secondly, I'd suggest that you use a form/subform set up for this type
relationship. The main form would have the parent table (one-side table) as
it's record source and the subform would have the child table (many-side
table) as its record source. That way, deleting a particular booking will
not delete the employee.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "FormSubform.mdb" which illustrates this.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have a training database in which I have tables including a bookings table
(details of a employee being booked on a course) and employee info
table(which includes the employee name, post title etc). When a booking is
made, the administrator selects the employees name from a dropdown

list
based

on the employeeinfo table. If however, that person pulls out of the training
and the administrator deletes the booking on the form, the persons

record
is

deleted from the employeeinfo table - not good. Any other bookings for that
person are still in the bookings table, but as far as the administrator is
concerned, the person no longer exists in the database as they are deleted
from the employeeinfo table.


the code behind the bookings form is
SELECT bookings.[event ID], bookings.[booking ID], bookings.employeeID,
employeeinfo.surname, employeeinfo.forename, employeeinfo.[post/role],
employeeinfo.Project, bookings.[booking taken], bookings.[confirmed date],
bookings.confirmed, bookings.status, bookings.explain, bookings.[cert
issued], bookings.datemodified, bookings.datecreated, employeeinfo.[support
needs]
FROM employeeinfo INNER JOIN bookings ON employeeinfo.[employee ID] =
bookings.employeeID
ORDER BY bookings.[booking taken];


I have checked the relationship between the tables and they do not have
cascade delete related records switched on.

How can I stop delete bookings in the form without deleting the person?

regards