How to limit deletion of records?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What is the best way to limit the deletion of records based on the results of
3 tables? I have 1) 1 parent table-ContractMain, 2) 1 child related
table-ContractDetail, and an independent table called 3) Billing table. In
the ContractDetail table, it has 4 fields: 1) Agreemt, 2) master_agreemt , 3)
contract_no, & 4) line_item. In the MainContract table, it has 2 fields: 1)
contract_no, 2) master_agreemt. In the Billing table, it has all of the 4
fields (similar to the Detail table). If a specific contract_no, agreemt,
master_agreemt, and line_item exists in the Billing table, then DO NOT allow
user to delete record in the ContractDetail table.

I tried using a query in conjunction with the DCount to limit deletion in
the parent table, but I cannot use that method to limit deletion in the
Detail table because I need to check on the 4 existing fields before deleting
records in the Detail table.

I would appreciate if you could steer me in the right direction. Thank you!!!

geri
 
If you have relationships between the tables defined and you do not select
cascading deletes, then you will not be able to delete a parent record.
 
But, what is the way to validate records that can be deleted in the Parent
Child tables, based on the records in another table? Is it better to use a
DCount, DLookup, or Select statement?

Thank you!
 
If you need to look in another table to determine whether you can delete
records your database is not well normalized.

I would suggest the DLookup. If it returns a value, then the data exists.
If it returns Null, then no matching data was found. I guess this is the
circumstance where you would want to allow the deletes.
 
Geri,

What approach are you using to delete the ContractDetail record?... A
command button on a form? I would imagine the DCount idea would work
just as well.

Having said that, I would also say that this is reflective of a table
design problem. It is not really clear, of course, without knowing
further details of your project. But this replication of data into two
tables is inadvisable.
 
Hi Steve,

Please tell me if this design approach is bad.

Based on my MainContract table (Parent)-contains main contract header
records and DetailContract (Child)-containing detailed line_item detailed
records having a 1-to-many relationship, the user is able to delete specific
line_items in the DetailContract table only if the same contract_no,
agreemt_type, and line_item DO NOT exist in the Billing table. The Billing
table is a separate table that contains the monthly amounts that were billed
based on a specific contract_no, agreemt_type and line_item, and processing
month_year_date.

Also, the user has the ability to delete the MainContract record and it's
children (DetailContract) if no contract_no records are in the Billing table.

I'm just not sure what is the best way to go about doing this. This is
getting wordy. Sorry!

Thank you,
geri
 
I am using a delete button on the MainContract form as well as on the
ContractDetail form-OnClick event procedure.
 
Geri,

I think the main point here, as regards design, is that the contract_no,
master_agreemt, agreemt_type, and line_item, should not be in the
Billing table. In fact, you probably shouldn't even have a Billing
table at all. Forgive me if I get the details wrong here, due to not
knowing what you've got there. But most likely the Billing information
should be based on fields in the DetailContract table, i.e. whether and
when that line item was billed or whatever. If it is indeed valid to
split this out into a separate table, then most likely it should be
simply on the basis of a DetailContractID field or some such, and not on
the basis of a replication of the line item data into another table.
So, you asked a straight question, and here is the straight answer - yes
this is a bad design approach.
 
Thank you for your design advice. I will have to restructure this. Hope
it's not too late. Appreciate your help!
 
Geri,

It will still be possible to work with your existing structure. But if
you can modify it, you will save yourself a lot of headaches down the track.

If you need further advice on the design, a new post to the
..tablesdbdesign newsgroup, outlining your overall operation, will get
you some good help.
 
thank you! :)

Steve Schapel said:
Geri,

It will still be possible to work with your existing structure. But if
you can modify it, you will save yourself a lot of headaches down the track.

If you need further advice on the design, a new post to the
..tablesdbdesign newsgroup, outlining your overall operation, will get
you some good help.
Click to expand...
 
Back
Top