delete record button challenge for Dirk Goldgar or any Access Mast

[Guest]

Dirk helped extensively in designing a subform that I use to create bills of
material. This form simply associates one txtProfileID with many. Here’s how
it’s coded:

Private Sub Form_BeforeUpdate(Cancel As Integer)

' Pick up previous associated profile, if any.
' If the old value is Null, the following assignment will result in
' a zero-length string.
mstrOldAssociations = ("'" + Me.cbProfilesAssociations.OldValue + "'") &
vbNullString

End Sub

Private Sub Form_AfterUpdate()

With CurrentDb

' Delete the old association, if it existed.
If Len(mstrOldAssociations) > 0 Then
.Execute _
"DELETE * FROM tblPKProfilesAssociations " & _
"WHERE ProfilesAssociations='" & Me!txtProfileID & "' " & _
"AND txtProfileID=" & mstrOldAssociations
End If

' Create a reciprocal record to match the record being saved.
.Execute _
"INSERT INTO tblPKProfilesAssociations " & _
"(ProfilesAssociations, txtProfileID) " & _
"VALUES ('" & Me!txtProfileID & "', '" &
Me.cbProfilesAssociations & "')"


End With

Private Sub Form_Delete(Cancel As Integer)

' Capture the old association from each deleted record.
mstrOldAssociations = mstrOldAssociations & ",'" &
Me.cbProfilesAssociations & "'"

End Sub

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)

If MsgBox("Would you like to delete this record?", _
vbQuestion + vbYesNoCancel + vbDefaultButton2) = vbYes Then

Cancel = False
Response = acDataErrContinue
Else
Cancel = True
End If

End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)

' Don't do anything unless the delete was successful.
If Status = acDeleteOK Then

' We will have created a comma-separated list of the IDs to be
deleted.

CurrentDb.Execute _
"DELETE * FROM tblPKProfilesAssociations " & _
"WHERE ProfilesAssociations='" & Me.Parent!txtProfileID & "'
" & _
"AND txtProfileID IN (" & Mid$(mstrOldAssociations, 2) & ")"

End If

mstrOldAssociations = vbNullString

End Sub

All of this works fine, however, I’ve come across another design challenge.
When I delete a txtProfileID using the Access delete record button it prompts
if I want to delete all related records. Of course, this works fine, too. The
challenge is that this results in records leftover on the
tblPKProfilesAssociations. For example, deleting txtProfileID 12345 which is
associated with 200000 results in the record:
txtProfileID 12345 | AssociatedProfile 200000
being deleted from tblPKProfilesAssociations BUT it leaves the record:
txtProfileID 200000 | AssociatedProfile 12345

YIKES!

I feel like I'm painted into a corner. Any way to resolve this? As always,
any help would greatly be appreciated!

THANKS!

 

[Dirk Goldgar]

Dirk helped extensively in designing a subform that I use to create
bills of material. This form simply associates one txtProfileID with
many. Here's how it's coded:

Private Sub Form_BeforeUpdate(Cancel As Integer)

' Pick up previous associated profile, if any.
' If the old value is Null, the following assignment will result
in ' a zero-length string.
mstrOldAssociations = ("'" + Me.cbProfilesAssociations.OldValue +
"'") & vbNullString

End Sub

Private Sub Form_AfterUpdate()

With CurrentDb

' Delete the old association, if it existed.
If Len(mstrOldAssociations) > 0 Then
.Execute _
"DELETE * FROM tblPKProfilesAssociations " & _
"WHERE ProfilesAssociations='" & Me!txtProfileID & "' " & _
"AND txtProfileID=" & mstrOldAssociations
End If

' Create a reciprocal record to match the record being saved.
.Execute _
"INSERT INTO tblPKProfilesAssociations " & _
"(ProfilesAssociations, txtProfileID) " & _
"VALUES ('" & Me!txtProfileID & "', '" &
Me.cbProfilesAssociations & "')"


End With

Private Sub Form_Delete(Cancel As Integer)

' Capture the old association from each deleted record.
mstrOldAssociations = mstrOldAssociations & ",'" &
Me.cbProfilesAssociations & "'"

End Sub

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As
Integer)

If MsgBox("Would you like to delete this record?", _
vbQuestion + vbYesNoCancel + vbDefaultButton2) = vbYes Then

Cancel = False
Response = acDataErrContinue
Else
Cancel = True
End If

End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)

' Don't do anything unless the delete was successful.
If Status = acDeleteOK Then

' We will have created a comma-separated list of the IDs to be
deleted.

CurrentDb.Execute _
"DELETE * FROM tblPKProfilesAssociations " & _
"WHERE ProfilesAssociations='" &
Me.Parent!txtProfileID & "' " & _
"AND txtProfileID IN (" & Mid$(mstrOldAssociations,
2) & ")"

End If

mstrOldAssociations = vbNullString

End Sub

All of this works fine, however, I've come across another design
challenge. When I delete a txtProfileID using the Access delete
record button it prompts if I want to delete all related records. Of
course, this works fine, too. The challenge is that this results in
records leftover on the tblPKProfilesAssociations. For example,
deleting txtProfileID 12345 which is associated with 200000 results
in the record:
txtProfileID 12345 | AssociatedProfile 200000
being deleted from tblPKProfilesAssociations BUT it leaves the record:
txtProfileID 200000 | AssociatedProfile 12345

YIKES!

I feel like I'm painted into a corner. Any way to resolve this? As
always, any help would greatly be appreciated!

THANKS!

Hi, John -

Unless I've missed something, I think the simplest approach to this is
to define the relationship between tblProfiles.txtProfileID and
tblPKProfilesAssociations.ProfilesAssociations, and set cascading
deletes on that relationship. In the copy of the database thatr I have
from our last collaboration, that relationship is not defined. You do
have a relationship between the tables, but only between txtProfileID
and tblPKProfilesAssociations.txtProfileID. That relationship is quite
proper, but you can create an *additional*, separate relationship
between the two tables, linking
tblPKProfilesAssociations.ProfilesAssociations with
tblProfiles.txtProfileID.

You'll find that, when you do that, a second copy of tblProfiles, named
"tblProfiles_1", will be displayed in the Relationship window. Don't be
thrown by that; it's just a display mechanism. Also note that Access
won't let you create and enforce this relationship if there are records
in tblProfilesAssociations that don't have a matching record in
tblProfiles. So you may have some cleaning up to do first. A simple
"find unmatched" query can help you with that.

Once the new relationship has been created, set it to enforce
referential integrity and to cascade updates and deletes. I believe
that you will then find that deleting a record from tblProfiles also
deletes all records in tblProfilesAssociations that refer to that
ProfileID, whether in the txtProfileID field or in the
ProfilesAssociations field.

 

[Guest]

Dirk to the rescue!

I updated the existing relationship to cascade delete. I didn't set this
initially as I learned in a class that doing so should be avoided unless you
truly understand the ramifications. At the time I didn't understand the
ramifications. I see where it's needed now.

I ran the find unmatched query and found over 300 records:

SELECT tblProfiles.txtProfileID
FROM tblProfiles LEFT JOIN tblPKProfilesAssociations ON
tblProfiles.txtProfileID = tblPKProfilesAssociations.txtProfileID
WHERE (((tblPKProfilesAssociations.ProfilesAssociations) Is Null));

One of the records found unmatched is tblProfileID.txtProfileID: 00533. This
particular txtProfileID currently has no associations. I'm confused. I can't
delete tblProfiles.txtProfileID: 00533 and I can't add it to
tblPKProfilesAssociations.ProfilesAssociations.

Obviously, I'm missing something...?

JohnLute

 

[Dirk Goldgar]

Dirk to the rescue!

I updated the existing relationship to cascade delete. I didn't set
this initially as I learned in a class that doing so should be
avoided unless you truly understand the ramifications. At the time I
didn't understand the ramifications. I see where it's needed now.

I ran the find unmatched query and found over 300 records:

SELECT tblProfiles.txtProfileID
FROM tblProfiles LEFT JOIN tblPKProfilesAssociations ON
tblProfiles.txtProfileID = tblPKProfilesAssociations.txtProfileID
WHERE (((tblPKProfilesAssociations.ProfilesAssociations) Is Null));

One of the records found unmatched is tblProfileID.txtProfileID:
00533. This particular txtProfileID currently has no associations.
I'm confused. I can't delete tblProfiles.txtProfileID: 00533 and I
can't add it to tblPKProfilesAssociations.ProfilesAssociations.

Obviously, I'm missing something...?

I think you're using the wrong "find unmatched" query. The one you
posted will find record in tblProfiles that don't have associations.
Such records would be perfectly okay, it seems to me. You want a query
to return records in tblPKProfilesAssociations that don't have matching
records in tblProfiles. For that, the SQL should be:

SELECT tblPKProfilesAssociations.*
FROM
tblPKProfilesAssociations
LEFT JOIN
tblProfiles
ON
tblPKProfilesAssociations.ProfilesAssociations =
tblProfiles.txtProfileID
WHERE
tblProfiles.txtProfileID Is Null;

I don't think you have to worry about the other relationship between the
two tables, the one on tblPKProfilesAssociations.txtProfileID =
tblProfiles.txtProfileID, because that relationship already exists and
is enforced.

 

[Guest]

All set now! As always I'm completely amazed by your Access skills and
knowledge! Once again I thank you very much!

So have you ever been asked about Darth Vader? With your fencing skills do
you think you could've taken him with a light sabre...?

JohnLute

 

[Dirk Goldgar]

All set now! As always I'm completely amazed by your Access skills and
knowledge! Once again I thank you very much!

Aw, shucks.

So have you ever been asked about Darth Vader? With your fencing
skills do you think you could've taken him with a light sabre...?

LOL

Believe it or not, this has been extensively discussed in sport-fencing
circles. The light-sabre technique we see in the movies -- though it
varies according to director and choreographer -- only makes sense if
the Force plays a major role in tactics and technique. Otherwise,
unless light has suddenly become heavy <g>, you'd expect to see
something like modern sport sabre technique, and those guys on the
screen are way too slow. Even Yoda. And did no one ever teach them
about the feint-disengage?

 

[Fred Boer]

You are lucky Jeff is away on a family vacation! He'd have a thing or two to
say about casting aspersions about Yoda!! And lift a Tie Fighter from a
muddy swamp with the power of your mind can you? Hmm?

Fred

 

[Dirk Goldgar]

You are lucky Jeff is away on a family vacation! He'd have a thing or
two to say about casting aspersions about Yoda!! And lift a Tie
Fighter from a muddy swamp with the power of your mind can you? Hmm?

LOL Every day before breakfast.

 

[Guest]

LOL!!! Hysterical are you guys!