Re: Problems with Delete Command

Hi Bob

I see your misunderstanding. RecordsetClone should be used only to delete
the junction record that is currently displayed in your subform, simply to
avoid the #Deleted display. You could do a SQL delete followed by
Me.Requery if you prefer (remembering the Me.Undo first, of course!)

To delete the record in WebComs, do a SQL delete:

CurrentDb.Execute "Delete * from tblWebComs where WebComID=" _
& lngID, dbFailOnError

If you ALWAYS want to delete ALL the related junction table records, then
forget the RecordsetClone.Delete and just execute the SQL delete followed by
Me.Requery. (The cascade deletes will take care of the rest.)
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Bob" <boblanders123@xxxxxxxxx> wrote in message
news:1155634546.940035.82070@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi Graham,

I have cascade deletes set at the moment. But how do I get
recordsetclone to point to one table rather than the other to
facilitate switching between options? (ie to delete from WebComs and
the junction table for option 3, but only from the junction table if
the record in WebComs is required for other relationships as per option
2).

Do I need to fiddle with the select query that I am currently using as
the record source for the subform (see my first post) to enable
recordsetclone to achieve this? Or is recordsetclone not able to be
used where you have a many to many relationship?


Regards
Bob



Hi Bob

Yes - basically the user has four choices (you may not wish to offer all
of
them):

1. Do nothing and undo the change to the record.
2. Delete the junction record and leave the WebComs record intact
3. Delete the junction record and the WebComs record if it is no longer
referenced.
4. Delete all related junction records AND the WebComs record

Currently you are offering only 1 or 2.

You can facilitate 4 by setting Cascade Deletes on the relation between
WebComs and the junction table.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Bob" <fakeemail@xxxxxxxxx> wrote in message
news:44e0d94a$0$19634$5a62ac22@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi Graham,

Yes, there is meant to be a many to many relationship here. I am
surprised
that the recordsetclone property doesn't delete the entries in both
tables
at the same time since the form's recordsource contains an inner join.

As for "lights out" code you mentioned, to be honest I hadn't even
thought
of that. Conceptually, then, I take it that the code has to follow the
following procedure:

1. Allow user to invoke delete (by clearing text box)
2. Get user confirmation to delete
3. Lookup the ContactWebComs table to check if current WebCom is linked
to
any other entries
4. If other entries exist, advise user - and delete the relevant
junction
table entries only (ie leave entry in WebComs alone)
5. Else, delete entries in both tables.

Is this the way it's normally done?


Thanks
Bob


"Graham Mandeno" <Graham.Mandeno@xxxxxxxxxxxxx> wrote in message
news:OY54NT5vGHA.724@xxxxxxxxxxxxxxxxxxxxxxx
But the fact that you have a junction table suggests a many-to-many
relationship. One contact has many WebComs and one WebCom can be
shared
by many contacts.

Is this the case?

If not, then you need to change your design. If one WebCom cannot be
shared by many contacts then you don't need a junction table - you
just
need a ContactID field in tblWebComs and a ONE-to-many relationship.

If it truly is many-to-many, then perhaps you want to have additional
code that says, "If this guy is the last to leave then turn out the
lights". In other words, if no further ContactWebComs records refer to
this WebComs record, then delete it.

--

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Bob" <boblanders123@xxxxxxxxx> wrote in message
news:1155540093.550085.288790@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi Graham,

Just in case I've got part of this wrong, this is what I now have in
my
BeforeUpdate event:

If IsNull(Me.txtWebComType) Then
Dim strMsg As String
Dim strTitle As String
Dim strSQL As String
strMsg = "Would you like to delete this entry?"
strTitle = "Delete WebCommunication Record"
Me.Undo ' do this unconditionally
Cancel = True
If MsgBox(strMsg, vbQuestion Or vbYesNo Or vbDefaultButton2,
strTitle) _
= vbYes Then

With Me.RecordsetClone
.Bookmark = Me.Bookmark
.Delete
End With

End If
End If

This certainly works without have to requery the database after the
delete. Unfortunately, the code is only deleting the entries in my
junction table (ContactWebComs) - not the entries in my linked table
(WebComs).


Regards
Bob


Graham Mandeno wrote:

Hi Bob

Instead of
Me.Recordset.Delete
try
With Me.RecordsetClone
.Bookmark = Me.Bookmark
.Delete
End With

The method using RecordsetClone should work with all versions of
Access
and
not require a Requery. Opening a separate Recordset will still
require
a
requery (as will a SQL delete) because the form's recordset is not
being
used.

And no, you do not require the second Undo/Cancel. If txtWebComType
has
been cleared then the Undo/Cancel must happen in any case - either
because
the user does not want to delete the record, or because the lock on
the
record must be released so it can be deleted. That's why I moved it
up
before the If MsgBox...

The reason the code does not work in AfterUpdate is that only the
FIELD
has
been updated at that point, not the entire record, so the record is
still in
the process of being edited.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Bob" <boblanders123@xxxxxxxxx> wrote in message
news:1155514105.836503.123510@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx








.

Loading