Only deleting records from 'main' table

[Guest]

I have a query that contains 5 tables that have a one-to-many relationship.
In the join properties, I chose to include All records from my 'main' table
and only those matching records from all other 4 tables. My form's data
source is this query. I added a button to my form that deletes a records.
The record is being deleted from my 'main' table, which I want, but it's also
being deleted from one of my other tables as well, which I don't want.

I usually only create relationships within queries and not in the
relationship window, but I though that maybe Enforce Referential Integrity
was automatically happening, so I created the relationships in the
relationship window, unchecking ERI on all joins. I still kept my
relationships in my query design though. When I delete a record, it is still
deleted in both tables. Any ideas how I can fix this? Thank you.

 

[Allen Browne]

Alex, it's really hard to picture what kind of data this would be valid for.

Just look an one pair of tables: the main one, and one of the related
tables. The main table contains some data (such as a student), and the other
table contains related data (such as the marks that student earned.) Now you
want to delete the student, but keep the records of their marks??? So the
related table now contains records that you would have to describe as, "Well
somebody got these grades, but we really have no idea who it was." That
would be kinda meaningless data, wouldn't you agree?

There are some cases where you might want to delete the primary records, and
set the foreign key field to Null. If that is what you want to do, Access is
able to do it for you, using cascade-to-null relations. These are not widely
known, but you can read up about them here:
Cascade-to-Null Relations
at
http://allenbrowne.com/ser-64.html

However, I can't imagine any scenario where:
a) you want data without enforced relations;
b) you want multiple relations that all cascade to null;
c) you are happy with orphan records in lots of tables.
This doesn't sound like the way a relational database should work.

I am guessing that there is something else here that is drastically wrong
with the way this is set up.

 

[Guest]

Allen,

My database if for machine parts. My main table called NewParts, contains a
list of new machine parts with fields like; part (key), qty, cost, reference
part. My related table called ReferenceParts, contains a list of reference
machine parts with fields like; part (key), matl cost, var cost. My query
links the two tables by NewParts tbl - reference part to ReferenceParts tbl -
part. In the form, a user can select a reference part from the
ReferenceParts tbl and store the reference part in the new parts tbl. This
will show the reference part matl cost and var cost in the form. There will
be many times when the record in the NewParts tbl will be deleted, but I
don't want the ReferencePart tbl record to be deleted because it can be used
in other new parts records. If I don't link the two tables, then I'll have
to store all the values from the ReferencePart tbl in the NewParts tbl, which
wouldn't make sense to me. Thanks.

 

[Allen Browne]

Alex, I'm still struggling to understand how this data connects.

How does the one-to-many relation work between these 2 tables?
Does one record in NewParts have many entries in ReferenceParts?
Or, does on record in ReferenceParts have many in NewParts?

What field are they connected by?
Is it the Part field?
If it is the Part field in both tables, and this field is the primary key of
both tables, it would seem that you have a one-to-one relation?

You say there are times when you want to keep a record in ReferenceParts,
but not have in in NewParts. Would there ever be a case when you would want
a record in NewParts that has no match in ReferenceParts?

 

[Guest]

Actually, each record in NewParts will only have one ReferencePart record.
One ReferencePart record could be in several NewParts. They are connected by
NewPartstbl;referencepart to ReferencePartstbl;part. ReferenceParttbl; part
is key, but NewPartstbl; referencepart is not key. In the NewPartstbl, Part
is key. In my query join properties, I picked "include all records in
NewParts and only those records from ReferenceParts where join fields are
equal. That's because I need every record in the NewPart table included in
my query.

The ReferenceParttbl is deleted and recreated weekly from an imported Excel
worksheet. The referencepart in the form is a originally picked via a combo
box from with data source from the part field in the ReferenceParttbl and is
then stored in the NewPartstbl. There may be a referencepart in the
NewPartstbl that used to be in the ReferenceParttbl that no longer exists
with the new import, which is fine and why I want all records from
NewPartstbl and only those matching records from ReferencePartstbl. Thanks
for sticking with me on this. Alex.

 

[Allen Browne]

Okay, so I understand there is an unenforced relationship like this:
ReferencePart.Part 1 >>--->> many NewParts.ReferencePart

And the issue is that your form is based on a query that uses multiple
tables, and so when you delete from the form, you end up deleting from
multiple queries.

That suggests the solution might be to break that form down into a form and
a subform instead of using a single query for both which is the problem. The
main form would be based on the ReferencePart table, and would show one
record at a time. The subform would be based on the NewParts table, and
would show all the matching records from NewParts for the ReferencePart in
the main form.

Now if you delete from the subform, you are deleting the row from NewParts
only (since that is the source of the subform.)

Does that sound like a feasible solution?

 

[Guest]

oh boy - I could do that, but isn't there some code that I can write behind
the delete button that deletes the record only from the NewPartstbl? That
would be much easier then re-doing my form. There are several fields on the
form (I only gave you a sampling to post my question). There are also some
setvalues and lots of other code behind my form and some of the controls on
the form. Writing code behind my delete button would be much easier. Do you
have any idea how to write that? Thanks again for all your help; it's much
appreciated. Alex

 

[Guest]

I have a button that I'll try to code to only delete records from the
NewPartstbl. I have the hardest time with syntax for these things. How can
I change the strSQL in the example below to add my other 2 key fields? Also,
I don't think I'll need to include [forms]![formname]! because the form I'm
referring to is open.

So what I need is to delete from AllPartsTable where Model# = me.Model# and
Part# = me.New Part# and NHL = New Part NHL. They are all text fields and I
know I shouldn't have used #, I messed up a long time ago and it's too late
to turn back now. Thanks so much for all your help. Alex.

Dim strSQL As String

If Me.New_Part_NHL <> "" And Me.New_Part_ <> "" Then

DoCmd.SetWarnings False

strSQL = "DELETE * FROM ExcelARefParts WHERE [Model#] = ' " & _
[Forms]![NewPart]![ModelToImport] & " ' "


DoCmd.SetWarnings True

Else
Me.Undo

 

[Allen Browne]

Sure:
Dim strSql As String
strSql = "DELETE FROM NewPartstbl WHERE ...
dbEngine(0)(0).Execute strSql, dbFailOnError

You will need to complete the WHERE statement so it deletes only the record
you want gone.