Delect Parent & Child records

[Guest]

A Form it is based on the Master Table.

This Form has two subForms both based on separate Tables that have a basic
1:Many relationship to the Master Table.

i.e. Master Table is Employee info ; first subForm (Table) is Training
Classes that employee has attended; second subForm (Table) is Job Positions
that employee has had previously

very straightforward.

My question is about deleting an employee Record. To delete the employee
from the MasterForm (Table) is easily done and a wizard makes it simple to
use a Delete Record button on the form. But the deletion of the related
child Tables’ records does not occur.

The two child Tables are linked via a unique employee ID field.

I have found previously the Referential Integrity & Cascading features often
to be user unfriendly – often sending error messages due to blank fields that
are not intuitive to the user. Suggestions? What would be the proper syntax
to have a button delete the Parent record and all related Child records in
other Tables?

Thanks……..

 

[Stefan Hoffmann]

hi,

I have found previously the Referential Integrity & Cascading features often
to be user unfriendly – often sending error messages due to blank fields that
are not intuitive to the user.

The following case was once reported to me:

They had installed a SQL Server 6.5 for storing their data and using
Access as frontend.
Due to these nasty nag messages they also did it without referential
integrity.
After one and a half year the server was deactivated completly, because
the data was no longer useable or interpretable in any way. The were not
even capable of exporting important data.
They had to reenter it in new database manually.
Their personal costs were incredible, as you can imagine.

Conclusion:
If you really don't won't to use referential integrity, because your
controllers don't understand their importance: Use Excel.

Suggestions? What would be the proper syntax
to have a button delete the Parent record and all related Child records in
other Tables?

Use the referential integrity with cascading delete.


mfG
--> stefan <--

 

[John Spencer]

If you choose to not implement Cascade Delete, then you have to write VBA
code that will do it in your forms. The problem here is that if someone
deletes records in the Master table using a query or directly in the table
you will end up with "orphan" records in the subordinate tables.

How are your VBA skills?
Sample snippet (UNTESTED) below assumes that you have a control name
txtEmployeeID which has the Master table's employeeId field and that the
field is a number field

Dim strSQL as String
Dim dbAny as DAO.Database

Set dbAny = CurrentDb()
StrSQL = "DELETE * FROM Trainng WHERE EmployeeID = " & Me.txtEmployeeID
dbAny.Execute StrSQL, dbFailOnError

StrSQL = "DELETE * FROM [Job Positions] WHERE EmployeeID = " &
Me.txtEmployeeID
dbAny.Execute StrSQL, dbFailOnError

Set dbAny = Nothing

That snippet should added before the code that is generated by the button
wizard. AND if the user cancels the delete the Training and Job Positions
for that employee will have already been deleted. If that is a worry, then
you will need to do the entire delete process within a transaction which
adds more complexity to the code.