Problems with Delete Command

[Bob]

Hi folks,

I have a continuous subform on a main data entry form.

Each entry on the subform contains two controls - a combo-box and a textbox
beside it.

The combobox allows the user to select a WebComType (Web Communication
Type - eg work email, home email etc). The box beside it is for entering
the actual email address etc.

Once a user has inserted a record into the linked table (tblWebComs), I want
the user to be able to delete the record by simply clearing the text box.
For this purpose, I have inserted the following into the BeforeUpdate event
for the combo-box:

Private Sub txtWebComType_BeforeUpdate(Cancel As Integer)
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"


If MsgBox(strMsg, vbQuestion Or vbYesNo Or vbDefaultButton2,
strTitle) _
= vbYes Then
CurrentDb.Execute "DELETE * FROM WebComs WHERE
[WebComs].[WebComID]=" & Me.ContactWebComs_WebComID & "", , dbFailOnError
Else
Cancel = True
Me.Undo
End If
End If
End Sub

The subform is linked to my main form (Clients) using this query string
auto-generated by Access:

SELECT [ContactWebComs].[ContactID], [ContactWebComs].[WebComID] AS
ContactWebComs_WebComID, [WebComs].[WebComID] AS WebComs_WebComID,
[WebComs].[WebComType], [WebComs].[WebComDetails] FROM WebComs INNER JOIN
ContactWebComs ON [WebComs].[WebComID]=[ContactWebComs].[WebComID];

The Child/Master link fields is ContactsID which appears in my contacts
table (the record source for the main form) and my junction table
(ContactWebComs).


When I run the BeforeUpdate code, I experience two problems:

(1) When I clear the combo-box and shift the focus (eg to the text box
beside it), the message box pops up to confirm the delete as expected. But
after that, nothing happens until I change focus again (eg tab to the next
row).

(2) Once I change focus a second time, a message pops up saying that the
record has been deleted - but as soon as I press okay on this message box,
Access just shuts down - no error messages or anything, the entire
application just shuts down.

After I restart the application and check the entries in tblWebComs, I can
see that the record I selected was in fact deleted.

I've tried a couple of variations for delete command.

For instance, I've tried hard coding the WebComID to delete like so:

CurrentDb.Execute "DELETE * FROM WebComs WHERE
[WebComs].[WebComID]=20", dbFailOnError

Again, the record is actually deleted, but I get exactly the same errors.

I've also tried running the following code instead of the currentdb.execute
line:

strSQL = "DELETE * " & _
"FROM WebComs " & _
"WHERE [WebComs].[WebComID]=" & Me.ContactWebComs_WebComID
& ""


DoCmd.SetWarnings False ' Turn off Access warning messages
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True ' Warning set on again

This results in exactly the same problems - and a correctly deleted record.

If I run the above code without the SetWarnings lines, the following
happens:

(a) my custom message box to popup;
(b) then Access's own message box warning that I am about to delete a record
from the specified table pops up;
(c) then nothing happens until I change focus to another part of the form;
(d) then a box pops up saying that "record is deleted"
(e) then all the rows in my subform disappear except for the blank "new
record" line
(f) a check of the table confirms that the record was correctly deleted.

The difference here is that Access does not shut down, but I can't stop
access from deleting all my rows. If I try to do incorporate a Me.Requery
after the outside "If ... Then" statement, Access complains that the line is
preventing it from saving anything to the database. If I try a
"Me.txtWebComType.Requery" line instead, I get an error saying that "You
must save the current field before you run a query action". If I insert a
"Me.txtWebComType.Requery" line in the AfterUpdate event instead, I end up
with the same problems described above.

I all out of ideas. Does anyone else have any pointers as to what might be
wrong with my code, or what might be causing these problems?

I should mention that the DoCmd code above worked fine when was
(incorrectly) deleting entries in my junction table (ContactWebComs) instead
of my WebComs table. (Please note, I have cascade update and delete
selected for all relationships).



TIA
Bob

 

[Graham Mandeno]

Hi Bob

Hmmm... the "Access shutting down" bit is very nasty.

This is clearly a bug, but I suspect it is being triggered by the fact that
you are deleting a record which has been locked for editing.

Also, you are deleting the record directly using SQL, so the form doesn't
know it's going to happen. If Access did not shut down, then I would expect
you to see the ugly #Deleted message in the controls for that row.

Try this:

First, undo the form (Me.Undo) BEFORE attempting to delete.

Second, delete the record from the form's recordset, not directly from the
table.

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
Me.Recordset.Delete
End If
End If

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi folks,

I have a continuous subform on a main data entry form.

Each entry on the subform contains two controls - a combo-box and a
textbox beside it.

The combobox allows the user to select a WebComType (Web Communication
Type - eg work email, home email etc). The box beside it is for entering
the actual email address etc.

Once a user has inserted a record into the linked table (tblWebComs), I
want the user to be able to delete the record by simply clearing the text
box. For this purpose, I have inserted the following into the BeforeUpdate
event for the combo-box:

Private Sub txtWebComType_BeforeUpdate(Cancel As Integer)
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"


If MsgBox(strMsg, vbQuestion Or vbYesNo Or vbDefaultButton2,
strTitle) _
= vbYes Then
CurrentDb.Execute "DELETE * FROM WebComs WHERE
[WebComs].[WebComID]=" & Me.ContactWebComs_WebComID & "", , dbFailOnError
Else
Cancel = True
Me.Undo
End If
End If
End Sub

The subform is linked to my main form (Clients) using this query string
auto-generated by Access:

SELECT [ContactWebComs].[ContactID], [ContactWebComs].[WebComID] AS
ContactWebComs_WebComID, [WebComs].[WebComID] AS WebComs_WebComID,
[WebComs].[WebComType], [WebComs].[WebComDetails] FROM WebComs INNER JOIN
ContactWebComs ON [WebComs].[WebComID]=[ContactWebComs].[WebComID];

The Child/Master link fields is ContactsID which appears in my contacts
table (the record source for the main form) and my junction table
(ContactWebComs).


When I run the BeforeUpdate code, I experience two problems:

(1) When I clear the combo-box and shift the focus (eg to the text box
beside it), the message box pops up to confirm the delete as expected.
But after that, nothing happens until I change focus again (eg tab to the
next row).

(2) Once I change focus a second time, a message pops up saying that the
record has been deleted - but as soon as I press okay on this message box,
Access just shuts down - no error messages or anything, the entire
application just shuts down.

After I restart the application and check the entries in tblWebComs, I can
see that the record I selected was in fact deleted.

I've tried a couple of variations for delete command.

For instance, I've tried hard coding the WebComID to delete like so:

CurrentDb.Execute "DELETE * FROM WebComs WHERE
[WebComs].[WebComID]=20", dbFailOnError

Again, the record is actually deleted, but I get exactly the same errors.

I've also tried running the following code instead of the
currentdb.execute line:

strSQL = "DELETE * " & _
"FROM WebComs " & _
"WHERE [WebComs].[WebComID]=" & Me.ContactWebComs_WebComID
& ""


DoCmd.SetWarnings False ' Turn off Access warning messages
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True ' Warning set on again

This results in exactly the same problems - and a correctly deleted
record.

If I run the above code without the SetWarnings lines, the following
happens:

(a) my custom message box to popup;
(b) then Access's own message box warning that I am about to delete a
record from the specified table pops up;
(c) then nothing happens until I change focus to another part of the form;
(d) then a box pops up saying that "record is deleted"
(e) then all the rows in my subform disappear except for the blank "new
record" line
(f) a check of the table confirms that the record was correctly deleted.

The difference here is that Access does not shut down, but I can't stop
access from deleting all my rows. If I try to do incorporate a Me.Requery
after the outside "If ... Then" statement, Access complains that the line
is preventing it from saving anything to the database. If I try a
"Me.txtWebComType.Requery" line instead, I get an error saying that "You
must save the current field before you run a query action". If I insert a
"Me.txtWebComType.Requery" line in the AfterUpdate event instead, I end up
with the same problems described above.

I all out of ideas. Does anyone else have any pointers as to what might
be wrong with my code, or what might be causing these problems?

I should mention that the DoCmd code above worked fine when was
(incorrectly) deleting entries in my junction table (ContactWebComs)
instead of my WebComs table. (Please note, I have cascade update and
delete selected for all relationships).



TIA
Bob

 

[Bob]

Hi Graham,

I tried just cutting and pasting your code and it didn't work. I
assumed that this was because I needed to create a recordset first.

Anyways, I had another crack at this and the following code seems to
work but any advice you might have on improving it would be appreciated
(I assume the second set of Undo/Cancel commands are unnecessary):

Private Sub txtWebComType_BeforeUpdate(Cancel As Integer)

If IsNull(Me.txtWebComType) Then
Dim strMsg As String
Dim strTitle As String
Dim strSQL As String
Dim rstTemp As DAO.Recordset ' Using DAO
strMsg = "Would you like to delete this entry?"
strTitle = "Delete WebCommunication Record"
strSQL = "SELECT * FROM WebComs WHERE [WebComs].[WebComID] = " &
Me.ContactWebComs_WebComID & ""

Me.Undo ' do this unconditionally
Cancel = True

If MsgBox(strMsg, vbQuestion Or vbYesNo Or vbDefaultButton2,
strTitle) _
= vbYes Then

Set rstTemp = CurrentDb.OpenRecordset(strSQL)

' Make sure the recordset is not empty
If Not rstTemp.EOF Then
rstTemp.Delete
Me.Requery
End If

rstTemp.Close
Set rstTemp = Nothing

Else

Me.Undo ' do this unconditionally
Cancel = True

End If

End If

End Sub


I tried the code without the first Undo and Cancel but it didn't work.
Amazingly, Access didn't actually shut down but as you predicted the
row in subform showed "#Deleted" immediately after the code ran. If I
leave the Requery line in without the first set of Undo/Cancel
commands, Access complains. Hence, the only way I can get the form to
reflect the current state of the database (ie Requery) is if I leave
the first set of Undo/Cancel commands in place.

Since the code appears in the BeforeUpdate event, logically the the
field linked to txtWebComType has not yet been updated by the time the
code runs. I can see (now - thanks to you) that if the row is actually
deleted before the update event occurs then clearly by the time Access
goes to update the field it will error out since the relevant row (and
field) no longer exists. This being the case, I would have thought
that the error could be avoid (without the first set of Undo/Cancel
commands) if I simply move the code to the AfterUpdate event. But this
still does not work. Can you explain why this is so?



TIA
Bob

Hi Bob

Hmmm... the "Access shutting down" bit is very nasty.

This is clearly a bug, but I suspect it is being triggered by the fact that
you are deleting a record which has been locked for editing.

Also, you are deleting the record directly using SQL, so the form doesn't
know it's going to happen. If Access did not shut down, then I would expect
you to see the ugly #Deleted message in the controls for that row.

Try this:

First, undo the form (Me.Undo) BEFORE attempting to delete.

Second, delete the record from the form's recordset, not directly from the
table.

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
Me.Recordset.Delete
End If
End If

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi folks,

I have a continuous subform on a main data entry form.

Each entry on the subform contains two controls - a combo-box and a
textbox beside it.

The combobox allows the user to select a WebComType (Web Communication
Type - eg work email, home email etc). The box beside it is for entering
the actual email address etc.

Once a user has inserted a record into the linked table (tblWebComs), I
want the user to be able to delete the record by simply clearing the text
box. For this purpose, I have inserted the following into the BeforeUpdate
event for the combo-box:

Private Sub txtWebComType_BeforeUpdate(Cancel As Integer)
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"


If MsgBox(strMsg, vbQuestion Or vbYesNo Or vbDefaultButton2,
strTitle) _
= vbYes Then
CurrentDb.Execute "DELETE * FROM WebComs WHERE
[WebComs].[WebComID]=" & Me.ContactWebComs_WebComID & "", , dbFailOnError
Else
Cancel = True
Me.Undo
End If
End If
End Sub

The subform is linked to my main form (Clients) using this query string
auto-generated by Access:

SELECT [ContactWebComs].[ContactID], [ContactWebComs].[WebComID] AS
ContactWebComs_WebComID, [WebComs].[WebComID] AS WebComs_WebComID,
[WebComs].[WebComType], [WebComs].[WebComDetails] FROM WebComs INNER JOIN
ContactWebComs ON [WebComs].[WebComID]=[ContactWebComs].[WebComID];

The Child/Master link fields is ContactsID which appears in my contacts
table (the record source for the main form) and my junction table
(ContactWebComs).


When I run the BeforeUpdate code, I experience two problems:

(1) When I clear the combo-box and shift the focus (eg to the text box
beside it), the message box pops up to confirm the delete as expected.
But after that, nothing happens until I change focus again (eg tab to the
next row).

(2) Once I change focus a second time, a message pops up saying that the
record has been deleted - but as soon as I press okay on this message box,
Access just shuts down - no error messages or anything, the entire
application just shuts down.

After I restart the application and check the entries in tblWebComs, I can
see that the record I selected was in fact deleted.

I've tried a couple of variations for delete command.

For instance, I've tried hard coding the WebComID to delete like so:

CurrentDb.Execute "DELETE * FROM WebComs WHERE
[WebComs].[WebComID]=20", dbFailOnError

Again, the record is actually deleted, but I get exactly the same errors.

I've also tried running the following code instead of the
currentdb.execute line:

strSQL = "DELETE * " & _
"FROM WebComs " & _
"WHERE [WebComs].[WebComID]=" & Me.ContactWebComs_WebComID
& ""


DoCmd.SetWarnings False ' Turn off Access warning messages
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True ' Warning set on again

This results in exactly the same problems - and a correctly deleted
record.

If I run the above code without the SetWarnings lines, the following
happens:

(a) my custom message box to popup;
(b) then Access's own message box warning that I am about to delete a
record from the specified table pops up;
(c) then nothing happens until I change focus to another part of the form;
(d) then a box pops up saying that "record is deleted"
(e) then all the rows in my subform disappear except for the blank "new
record" line
(f) a check of the table confirms that the record was correctly deleted.

The difference here is that Access does not shut down, but I can't stop
access from deleting all my rows. If I try to do incorporate a Me.Requery
after the outside "If ... Then" statement, Access complains that the line
is preventing it from saving anything to the database. If I try a
"Me.txtWebComType.Requery" line instead, I get an error saying that "You
must save the current field before you run a query action". If I insert a
"Me.txtWebComType.Requery" line in the AfterUpdate event instead, I end up
with the same problems described above.

I all out of ideas. Does anyone else have any pointers as to what might
be wrong with my code, or what might be causing these problems?

I should mention that the DoCmd code above worked fine when was
(incorrectly) deleting entries in my junction table (ContactWebComs)
instead of my WebComs table. (Please note, I have cascade update and
delete selected for all relationships).



TIA
Bob

 

[Graham Mandeno]

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

Hi Graham,

I tried just cutting and pasting your code and it didn't work. I
assumed that this was because I needed to create a recordset first.

Anyways, I had another crack at this and the following code seems to
work but any advice you might have on improving it would be appreciated
(I assume the second set of Undo/Cancel commands are unnecessary):

Private Sub txtWebComType_BeforeUpdate(Cancel As Integer)

If IsNull(Me.txtWebComType) Then
Dim strMsg As String
Dim strTitle As String
Dim strSQL As String
Dim rstTemp As DAO.Recordset ' Using DAO
strMsg = "Would you like to delete this entry?"
strTitle = "Delete WebCommunication Record"
strSQL = "SELECT * FROM WebComs WHERE [WebComs].[WebComID] = " &
Me.ContactWebComs_WebComID & ""

Me.Undo ' do this unconditionally
Cancel = True

If MsgBox(strMsg, vbQuestion Or vbYesNo Or vbDefaultButton2,
strTitle) _
= vbYes Then

Set rstTemp = CurrentDb.OpenRecordset(strSQL)

' Make sure the recordset is not empty
If Not rstTemp.EOF Then
rstTemp.Delete
Me.Requery
End If

rstTemp.Close
Set rstTemp = Nothing

Else

Me.Undo ' do this unconditionally
Cancel = True

End If

End If

End Sub


I tried the code without the first Undo and Cancel but it didn't work.
Amazingly, Access didn't actually shut down but as you predicted the
row in subform showed "#Deleted" immediately after the code ran. If I
leave the Requery line in without the first set of Undo/Cancel
commands, Access complains. Hence, the only way I can get the form to
reflect the current state of the database (ie Requery) is if I leave
the first set of Undo/Cancel commands in place.

Since the code appears in the BeforeUpdate event, logically the the
field linked to txtWebComType has not yet been updated by the time the
code runs. I can see (now - thanks to you) that if the row is actually
deleted before the update event occurs then clearly by the time Access
goes to update the field it will error out since the relevant row (and
field) no longer exists. This being the case, I would have thought
that the error could be avoid (without the first set of Undo/Cancel
commands) if I simply move the code to the AfterUpdate event. But this
still does not work. Can you explain why this is so?



TIA
Bob

Hi Bob

Hmmm... the "Access shutting down" bit is very nasty.

This is clearly a bug, but I suspect it is being triggered by the fact
that
you are deleting a record which has been locked for editing.

Also, you are deleting the record directly using SQL, so the form doesn't
know it's going to happen. If Access did not shut down, then I would
expect
you to see the ugly #Deleted message in the controls for that row.

Try this:

First, undo the form (Me.Undo) BEFORE attempting to delete.

Second, delete the record from the form's recordset, not directly from
the
table.

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
Me.Recordset.Delete
End If
End If

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi folks,

I have a continuous subform on a main data entry form.

Each entry on the subform contains two controls - a combo-box and a
textbox beside it.

The combobox allows the user to select a WebComType (Web Communication
Type - eg work email, home email etc). The box beside it is for
entering
the actual email address etc.

Once a user has inserted a record into the linked table (tblWebComs), I
want the user to be able to delete the record by simply clearing the
text
box. For this purpose, I have inserted the following into the
BeforeUpdate
event for the combo-box:

Private Sub txtWebComType_BeforeUpdate(Cancel As Integer)
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"


If MsgBox(strMsg, vbQuestion Or vbYesNo Or vbDefaultButton2,
strTitle) _
= vbYes Then
CurrentDb.Execute "DELETE * FROM WebComs WHERE
[WebComs].[WebComID]=" & Me.ContactWebComs_WebComID & "", ,
dbFailOnError
Else
Cancel = True
Me.Undo
End If
End If
End Sub

The subform is linked to my main form (Clients) using this query string
auto-generated by Access:

SELECT [ContactWebComs].[ContactID], [ContactWebComs].[WebComID] AS
ContactWebComs_WebComID, [WebComs].[WebComID] AS WebComs_WebComID,
[WebComs].[WebComType], [WebComs].[WebComDetails] FROM WebComs INNER
JOIN
ContactWebComs ON [WebComs].[WebComID]=[ContactWebComs].[WebComID];

The Child/Master link fields is ContactsID which appears in my contacts
table (the record source for the main form) and my junction table
(ContactWebComs).


When I run the BeforeUpdate code, I experience two problems:

(1) When I clear the combo-box and shift the focus (eg to the text box
beside it), the message box pops up to confirm the delete as expected.
But after that, nothing happens until I change focus again (eg tab to
the
next row).

(2) Once I change focus a second time, a message pops up saying that
the
record has been deleted - but as soon as I press okay on this message
box,
Access just shuts down - no error messages or anything, the entire
application just shuts down.

After I restart the application and check the entries in tblWebComs, I
can
see that the record I selected was in fact deleted.

I've tried a couple of variations for delete command.

For instance, I've tried hard coding the WebComID to delete like so:

CurrentDb.Execute "DELETE * FROM WebComs WHERE
[WebComs].[WebComID]=20", dbFailOnError

Again, the record is actually deleted, but I get exactly the same
errors.

I've also tried running the following code instead of the
currentdb.execute line:

strSQL = "DELETE * " & _
"FROM WebComs " & _
"WHERE [WebComs].[WebComID]=" &
Me.ContactWebComs_WebComID
& ""


DoCmd.SetWarnings False ' Turn off Access warning messages
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True ' Warning set on again

This results in exactly the same problems - and a correctly deleted
record.

If I run the above code without the SetWarnings lines, the following
happens:

(a) my custom message box to popup;
(b) then Access's own message box warning that I am about to delete a
record from the specified table pops up;
(c) then nothing happens until I change focus to another part of the
form;
(d) then a box pops up saying that "record is deleted"
(e) then all the rows in my subform disappear except for the blank "new
record" line
(f) a check of the table confirms that the record was correctly
deleted.

The difference here is that Access does not shut down, but I can't stop
access from deleting all my rows. If I try to do incorporate a
Me.Requery
after the outside "If ... Then" statement, Access complains that the
line
is preventing it from saving anything to the database. If I try a
"Me.txtWebComType.Requery" line instead, I get an error saying that
"You
must save the current field before you run a query action". If I
insert a
"Me.txtWebComType.Requery" line in the AfterUpdate event instead, I end
up
with the same problems described above.

I all out of ideas. Does anyone else have any pointers as to what
might
be wrong with my code, or what might be causing these problems?

I should mention that the DoCmd code above worked fine when was
(incorrectly) deleting entries in my junction table (ContactWebComs)
instead of my WebComs table. (Please note, I have cascade update and
delete selected for all relationships).



TIA
Bob

 

[Bob]

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]

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

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

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

news:[email protected]...

 

[Bob]

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

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

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

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

news:[email protected]...

 

[Graham Mandeno]

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

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

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

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]

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

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

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

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

 

[Graham Mandeno]

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

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

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


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

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]

Thanks Graham.

Sorry for the delayed response. I wanted to see if I could cobble
something together myself without asking for help everytime. The
fruits of my labour appear below. Again, any suggestions for
improvement would be appreciated.


Private Sub txtWebComType_BeforeUpdate(Cancel As Integer)

'Prevent user warnings
DoCmd.SetWarnings False

If IsNull(Me.txtWebComType) Then

' Dimension variables
Dim strMsg_Del As String ' First delete confirmation
Dim strTitle_Del As String ' Title for first delete
confirmation
Dim strMsg_Contacts As String ' Second delete confirmation
(record relates to other contacts)
Dim strTitle_Contacts As String ' Title for second delete
confirmation

Dim strSQL_Contacts As String ' SQL string to check if record
relates to other Contacts
Dim strSQL_ScrollBars As String ' SQL string to determine if
vertical scrollbars are required
Dim strSQL_DeleteMain As String ' SQL string to delete entry in
main (not junction) table
Dim strSQL_ContactNames As String ' SQL string to get names of
related contacts

Dim rstTemp As DAO.Recordset ' Recordset to store details of
other Contacts related to existing record

' Initialise variables
strMsg_Del = "Would you like to delete this entry?"
strTitle_Del = "Delete WebCommunication Record"
strMsg_Contacts = "This record relates to the other Contacts listed
below. " & vbCrLf
strMsg_Contacts = strMsg_Contacts & "Do you want to want to delete
the record for these Contacts too?"
strTitle_Contacts = "Multi-Contact Record"

strSQL_Contacts = "SELECT * FROM ContactWebComs " & _
"WHERE ([ContactWebComs].[WebComID]=" &
Me.ContactWebComs_WebComID & ") " & _
"AND NOT ([ContactWebComs].[ContactID] = " &
Me.ContactID & ")"

strSQL_DeleteMain = "Delete * from WebComs where WebComID=" &
Me.ContactWebComs_WebComID & ""

strSQL_ContactNames = "SELECT Individuals.FirstName,
Individuals.LastName FROM Individuals WHERE Individuals.ContactID IN "

' Undo changes to existing row
Me.Undo
Cancel = True

' Make sure that the user is not simply trying to delete an empty
row
If IsNull(Me.ContactWebComs_WebComID) Then

' Row did not relate to an existing record - no need to access
database
' Just remove the empty row on the form
Me.AllowAdditions = False

' Row relates to an existing record
Else

' Does user want to proceed with delete?
If MsgBox(strMsg_Del, vbQuestion Or vbYesNo Or
vbDefaultButton2, strTitle_Del) _
= vbYes Then

' Delete command confirmed - now assess the user's options
' First: Check whether entry relates to other Contacts in
the Junction Table
Set rstTemp = CurrentDb.OpenRecordset(strSQL_Contacts)

' Make sure the recordset is not empty
If Not rstTemp.EOF Then

' record relates to other Contacts
Dim fld As Field
Dim strFields As String
Dim strContactIDS As String
Dim strContactNames As String

strContactIDS = "("
strContactNames = ""

rstTemp.MoveLast
rstTemp.MoveFirst

' Enumerate related ContactIDs
For i = 1 To rstTemp.RecordCount
strContactIDS = strContactIDS &
rstTemp.Fields("ContactID").Value
strContactIDS = strContactIDS & ","
rstTemp.MoveNext
Next i

' Remove last comma
strContactIDS = Mid(strContactIDS, 1,
(Len(strContactIDS) - 1))
strContactIDS = strContactIDS & ")"

' Clear existing recordset variable
rstTemp.Close
Set rstTemp = Nothing

' reset recordset variable
Set rstTemp =
CurrentDb.OpenRecordset(strSQL_ContactNames & strContactIDS)

rstTemp.MoveLast
rstTemp.MoveFirst

' Enumerate related Contact Names
For i = 1 To rstTemp.RecordCount
strContactNames = strContactNames &
rstTemp.Fields("FirstName").Value & " "
strContactNames = strContactNames &
rstTemp.Fields("LastName").Value & vbCrLf
rstTemp.MoveNext
Next i

' Query user: delete the underlying record or just
the Junction Table entry?
Select Case MsgBox(strMsg_Contacts & vbCrLf &
vbCrLf & _
"Related Contacts: " & vbCrLf & strContactNames, _
vbQuestion + vbYesNoCancel + vbDefaultButton2,
strTitle_Contacts)

Case vbYes
MsgBox ("Confirmed delete underlying table.")
GoTo Delete_Main
Case vbNo
MsgBox ("Confirmed delete junction table
entries only." & _
vbCrLf & vbCrLf & "strSQL_DeleteMain: " &
strSQL_DeleteMain)
GoTo Delete_Junction
Case vbCancel
Exit Sub
Case Else
Stop
End Select

Else
' If recordset is empty, record relates to this Contact only
GoTo Delete_Main
End If

End If ' User has cancelled delete command - No need to for
extra Undo and Cancel

End If ' Close second If ... Then statement
(IsNull(Me.ContactWebComs_WebComID))

End If ' Close first If ... Then statement (IsNull(Me.txtWebComType))

'Reset original settings
DoCmd.SetWarnings True

Exit Sub

Delete_Main:

' Delete entries in the primary table (cascade delete will delete
junction table entries)
CurrentDb.Execute strSQL_DeleteMain, dbFailOnError

Me.Requery

' Make sure there is no empty "add new record" row
Me.AllowAdditions = False

Exit Sub


Delete_Junction:

' WebCom relates to other Contacts, delete Junction Table entries
only
Set rstTemp2 = Me.RecordsetClone

' Make sure recordset is not empty
If Not Me.RecordsetClone.EOF Then

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

' Make sure there is no empty "add new record" row
Me.AllowAdditions = False

' Clean Up
rstTemp2.Close
Set rstTemp2 = Nothing

Exit Sub

End If

End Sub




Regards
Bob

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

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

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


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

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

 

[Graham Mandeno]

Hi Bob

Looks pretty good. You forgot to say if it's working or not

A few comments:

1. There is nothing to be gained here by disabling warnings. Why are you
using SetWarnings?

2. You are finding the names related to the WebComID using two queries - one
to find the list of ContactIDs from the junction table, and another to find
the list if individuals matching those ContactIDs using an IN clause. It
would be much easier and faster to run a single query:

"Select FirstName & ' ' & LastName as ContactName " _
& "from Individuals inner join ContactWebComs " _
& "on Individuals.ContactID = ContactWebComs.ContactID " _
& "where WebComID=" & Me.ContactWebComs_WebComID _
& " and Individuals.ContactID<>" & Me.ContactID

Note that the concatenation of first and last name is happening in the query
also.

3. The usual way to traverse a recordset is not to use RecordCount as a
count loop limit, but instead to loop until the EOF condition is met:

Do Until rstTemp.EOF
' do something with record
rstTemp.MoveNext
Loop

Then you don't need to do a MoveLast/MoveFirst to ascertain the RecordCount.

4. This is a style/readability thing: if you are going to use string
variables to construct messages and SQL strings and suchlike, set their
values just before you use them, otherwise someone trying to read the code
has to scroll back and forth to remember what's in the string.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks Graham.

Sorry for the delayed response. I wanted to see if I could cobble
something together myself without asking for help everytime. The
fruits of my labour appear below. Again, any suggestions for
improvement would be appreciated.


Private Sub txtWebComType_BeforeUpdate(Cancel As Integer)

'Prevent user warnings
DoCmd.SetWarnings False

If IsNull(Me.txtWebComType) Then

' Dimension variables
Dim strMsg_Del As String ' First delete confirmation
Dim strTitle_Del As String ' Title for first delete
confirmation
Dim strMsg_Contacts As String ' Second delete confirmation
(record relates to other contacts)
Dim strTitle_Contacts As String ' Title for second delete
confirmation

Dim strSQL_Contacts As String ' SQL string to check if record
relates to other Contacts
Dim strSQL_ScrollBars As String ' SQL string to determine if
vertical scrollbars are required
Dim strSQL_DeleteMain As String ' SQL string to delete entry in
main (not junction) table
Dim strSQL_ContactNames As String ' SQL string to get names of
related contacts

Dim rstTemp As DAO.Recordset ' Recordset to store details of
other Contacts related to existing record

' Initialise variables
strMsg_Del = "Would you like to delete this entry?"
strTitle_Del = "Delete WebCommunication Record"
strMsg_Contacts = "This record relates to the other Contacts listed
below. " & vbCrLf
strMsg_Contacts = strMsg_Contacts & "Do you want to want to delete
the record for these Contacts too?"
strTitle_Contacts = "Multi-Contact Record"

strSQL_Contacts = "SELECT * FROM ContactWebComs " & _
"WHERE ([ContactWebComs].[WebComID]=" &
Me.ContactWebComs_WebComID & ") " & _
"AND NOT ([ContactWebComs].[ContactID] = " &
Me.ContactID & ")"

strSQL_DeleteMain = "Delete * from WebComs where WebComID=" &
Me.ContactWebComs_WebComID & ""

strSQL_ContactNames = "SELECT Individuals.FirstName,
Individuals.LastName FROM Individuals WHERE Individuals.ContactID IN "

' Undo changes to existing row
Me.Undo
Cancel = True

' Make sure that the user is not simply trying to delete an empty
row
If IsNull(Me.ContactWebComs_WebComID) Then

' Row did not relate to an existing record - no need to access
database
' Just remove the empty row on the form
Me.AllowAdditions = False

' Row relates to an existing record
Else

' Does user want to proceed with delete?
If MsgBox(strMsg_Del, vbQuestion Or vbYesNo Or
vbDefaultButton2, strTitle_Del) _
= vbYes Then

' Delete command confirmed - now assess the user's options
' First: Check whether entry relates to other Contacts in
the Junction Table
Set rstTemp = CurrentDb.OpenRecordset(strSQL_Contacts)

' Make sure the recordset is not empty
If Not rstTemp.EOF Then

' record relates to other Contacts
Dim fld As Field
Dim strFields As String
Dim strContactIDS As String
Dim strContactNames As String

strContactIDS = "("
strContactNames = ""

rstTemp.MoveLast
rstTemp.MoveFirst

' Enumerate related ContactIDs
For i = 1 To rstTemp.RecordCount
strContactIDS = strContactIDS &
rstTemp.Fields("ContactID").Value
strContactIDS = strContactIDS & ","
rstTemp.MoveNext
Next i

' Remove last comma
strContactIDS = Mid(strContactIDS, 1,
(Len(strContactIDS) - 1))
strContactIDS = strContactIDS & ")"

' Clear existing recordset variable
rstTemp.Close
Set rstTemp = Nothing

' reset recordset variable
Set rstTemp =
CurrentDb.OpenRecordset(strSQL_ContactNames & strContactIDS)

rstTemp.MoveLast
rstTemp.MoveFirst

' Enumerate related Contact Names
For i = 1 To rstTemp.RecordCount
strContactNames = strContactNames &
rstTemp.Fields("FirstName").Value & " "
strContactNames = strContactNames &
rstTemp.Fields("LastName").Value & vbCrLf
rstTemp.MoveNext
Next i

' Query user: delete the underlying record or just
the Junction Table entry?
Select Case MsgBox(strMsg_Contacts & vbCrLf &
vbCrLf & _
"Related Contacts: " & vbCrLf & strContactNames, _
vbQuestion + vbYesNoCancel + vbDefaultButton2,
strTitle_Contacts)

Case vbYes
MsgBox ("Confirmed delete underlying table.")
GoTo Delete_Main
Case vbNo
MsgBox ("Confirmed delete junction table
entries only." & _
vbCrLf & vbCrLf & "strSQL_DeleteMain: " &
strSQL_DeleteMain)
GoTo Delete_Junction
Case vbCancel
Exit Sub
Case Else
Stop
End Select

Else
' If recordset is empty, record relates to this Contact only
GoTo Delete_Main
End If

End If ' User has cancelled delete command - No need to for
extra Undo and Cancel

End If ' Close second If ... Then statement
(IsNull(Me.ContactWebComs_WebComID))

End If ' Close first If ... Then statement (IsNull(Me.txtWebComType))

'Reset original settings
DoCmd.SetWarnings True

Exit Sub

Delete_Main:

' Delete entries in the primary table (cascade delete will delete
junction table entries)
CurrentDb.Execute strSQL_DeleteMain, dbFailOnError

Me.Requery

' Make sure there is no empty "add new record" row
Me.AllowAdditions = False

Exit Sub


Delete_Junction:

' WebCom relates to other Contacts, delete Junction Table entries
only
Set rstTemp2 = Me.RecordsetClone

' Make sure recordset is not empty
If Not Me.RecordsetClone.EOF Then

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

' Make sure there is no empty "add new record" row
Me.AllowAdditions = False

' Clean Up
rstTemp2.Close
Set rstTemp2 = Nothing

Exit Sub

End If

End Sub




Regards
Bob

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

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

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


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

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]

Thanks alot Graham.

As a matter of fact it is working :-D

I just wasn't sure if there was a better way to do some of things I was
doing. From the looks of the your comments, there's obviously room for
improvement. Thanks for the tips.


Regards
Bob

Hi Bob

Looks pretty good. You forgot to say if it's working or not

A few comments:

1. There is nothing to be gained here by disabling warnings. Why are you
using SetWarnings?

2. You are finding the names related to the WebComID using two queries - one
to find the list of ContactIDs from the junction table, and another to find
the list if individuals matching those ContactIDs using an IN clause. It
would be much easier and faster to run a single query:

"Select FirstName & ' ' & LastName as ContactName " _
& "from Individuals inner join ContactWebComs " _
& "on Individuals.ContactID = ContactWebComs.ContactID " _
& "where WebComID=" & Me.ContactWebComs_WebComID _
& " and Individuals.ContactID<>" & Me.ContactID

Note that the concatenation of first and last name is happening in the query
also.

3. The usual way to traverse a recordset is not to use RecordCount as a
count loop limit, but instead to loop until the EOF condition is met:

Do Until rstTemp.EOF
' do something with record
rstTemp.MoveNext
Loop

Then you don't need to do a MoveLast/MoveFirst to ascertain the RecordCount.

4. This is a style/readability thing: if you are going to use string
variables to construct messages and SQL strings and suchlike, set their
values just before you use them, otherwise someone trying to read the code
has to scroll back and forth to remember what's in the string.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks Graham.

Sorry for the delayed response. I wanted to see if I could cobble
something together myself without asking for help everytime. The
fruits of my labour appear below. Again, any suggestions for
improvement would be appreciated.


Private Sub txtWebComType_BeforeUpdate(Cancel As Integer)

'Prevent user warnings
DoCmd.SetWarnings False

If IsNull(Me.txtWebComType) Then

' Dimension variables
Dim strMsg_Del As String ' First delete confirmation
Dim strTitle_Del As String ' Title for first delete
confirmation
Dim strMsg_Contacts As String ' Second delete confirmation
(record relates to other contacts)
Dim strTitle_Contacts As String ' Title for second delete
confirmation

Dim strSQL_Contacts As String ' SQL string to check if record
relates to other Contacts
Dim strSQL_ScrollBars As String ' SQL string to determine if
vertical scrollbars are required
Dim strSQL_DeleteMain As String ' SQL string to delete entry in
main (not junction) table
Dim strSQL_ContactNames As String ' SQL string to get names of
related contacts

Dim rstTemp As DAO.Recordset ' Recordset to store details of
other Contacts related to existing record

' Initialise variables
strMsg_Del = "Would you like to delete this entry?"
strTitle_Del = "Delete WebCommunication Record"
strMsg_Contacts = "This record relates to the other Contacts listed
below. " & vbCrLf
strMsg_Contacts = strMsg_Contacts & "Do you want to want to delete
the record for these Contacts too?"
strTitle_Contacts = "Multi-Contact Record"

strSQL_Contacts = "SELECT * FROM ContactWebComs " & _
"WHERE ([ContactWebComs].[WebComID]=" &
Me.ContactWebComs_WebComID & ") " & _
"AND NOT ([ContactWebComs].[ContactID] = " &
Me.ContactID & ")"

strSQL_DeleteMain = "Delete * from WebComs where WebComID=" &
Me.ContactWebComs_WebComID & ""

strSQL_ContactNames = "SELECT Individuals.FirstName,
Individuals.LastName FROM Individuals WHERE Individuals.ContactID IN "

' Undo changes to existing row
Me.Undo
Cancel = True

' Make sure that the user is not simply trying to delete an empty
row
If IsNull(Me.ContactWebComs_WebComID) Then

' Row did not relate to an existing record - no need to access
database
' Just remove the empty row on the form
Me.AllowAdditions = False

' Row relates to an existing record
Else

' Does user want to proceed with delete?
If MsgBox(strMsg_Del, vbQuestion Or vbYesNo Or
vbDefaultButton2, strTitle_Del) _
= vbYes Then

' Delete command confirmed - now assess the user's options
' First: Check whether entry relates to other Contacts in
the Junction Table
Set rstTemp = CurrentDb.OpenRecordset(strSQL_Contacts)

' Make sure the recordset is not empty
If Not rstTemp.EOF Then

' record relates to other Contacts
Dim fld As Field
Dim strFields As String
Dim strContactIDS As String
Dim strContactNames As String

strContactIDS = "("
strContactNames = ""

rstTemp.MoveLast
rstTemp.MoveFirst

' Enumerate related ContactIDs
For i = 1 To rstTemp.RecordCount
strContactIDS = strContactIDS &
rstTemp.Fields("ContactID").Value
strContactIDS = strContactIDS & ","
rstTemp.MoveNext
Next i

' Remove last comma
strContactIDS = Mid(strContactIDS, 1,
(Len(strContactIDS) - 1))
strContactIDS = strContactIDS & ")"

' Clear existing recordset variable
rstTemp.Close
Set rstTemp = Nothing

' reset recordset variable
Set rstTemp =
CurrentDb.OpenRecordset(strSQL_ContactNames & strContactIDS)

rstTemp.MoveLast
rstTemp.MoveFirst

' Enumerate related Contact Names
For i = 1 To rstTemp.RecordCount
strContactNames = strContactNames &
rstTemp.Fields("FirstName").Value & " "
strContactNames = strContactNames &
rstTemp.Fields("LastName").Value & vbCrLf
rstTemp.MoveNext
Next i

' Query user: delete the underlying record or just
the Junction Table entry?
Select Case MsgBox(strMsg_Contacts & vbCrLf &
vbCrLf & _
"Related Contacts: " & vbCrLf & strContactNames, _
vbQuestion + vbYesNoCancel + vbDefaultButton2,
strTitle_Contacts)

Case vbYes
MsgBox ("Confirmed delete underlying table.")
GoTo Delete_Main
Case vbNo
MsgBox ("Confirmed delete junction table
entries only." & _
vbCrLf & vbCrLf & "strSQL_DeleteMain: " &
strSQL_DeleteMain)
GoTo Delete_Junction
Case vbCancel
Exit Sub
Case Else
Stop
End Select

Else
' If recordset is empty, record relates to this Contact only
GoTo Delete_Main
End If

End If ' User has cancelled delete command - No need to for
extra Undo and Cancel

End If ' Close second If ... Then statement
(IsNull(Me.ContactWebComs_WebComID))

End If ' Close first If ... Then statement (IsNull(Me.txtWebComType))

'Reset original settings
DoCmd.SetWarnings True

Exit Sub

Delete_Main:

' Delete entries in the primary table (cascade delete will delete
junction table entries)
CurrentDb.Execute strSQL_DeleteMain, dbFailOnError

Me.Requery

' Make sure there is no empty "add new record" row
Me.AllowAdditions = False

Exit Sub


Delete_Junction:

' WebCom relates to other Contacts, delete Junction Table entries
only
Set rstTemp2 = Me.RecordsetClone

' Make sure recordset is not empty
If Not Me.RecordsetClone.EOF Then

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

' Make sure there is no empty "add new record" row
Me.AllowAdditions = False

' Clean Up
rstTemp2.Close
Set rstTemp2 = Nothing

Exit Sub

End If

End Sub




Regards
Bob

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

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

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


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

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]

Hi Graham,

Is it possible to modify your single query statement so that grabs the
organisation name OR the firstname & lastname linked to the WebCom
depending on the entity type?

eg something along the lines of:

"Select ((FirstName & ' ' & LastName as ContactName " _
& "from Individuals inner join ContactWebComs " _
& "on Individuals.ContactID = ContactWebComs.ContactID) " _
& "OR (Organisations.Name" _
& "from Organisations inner join ContactWebComs " _
& "on Organisations.ContactID = ContactWebComs.ContactID))"
& "where WebComID=" & Me.ContactWebComs_WebComID _
& " and Individuals.ContactID<>" & Me.ContactID

I've tried using the query designer in Access but I can't seem to
construct a workable query.

I haven't got a clue how to join all the tables together to enable one
query. Should I perform two separate queries in this instance?: One to
check for a matching contactid in the individuals table and then a
separate query against the organisations table?


Regards
Bob

Thanks alot Graham.

As a matter of fact it is working :-D

I just wasn't sure if there was a better way to do some of things I was
doing. From the looks of the your comments, there's obviously room for
improvement. Thanks for the tips.


Regards
Bob

Hi Bob

Looks pretty good. You forgot to say if it's working or not

A few comments:

1. There is nothing to be gained here by disabling warnings. Why are you
using SetWarnings?

2. You are finding the names related to the WebComID using two queries - one
to find the list of ContactIDs from the junction table, and another to find
the list if individuals matching those ContactIDs using an IN clause. It
would be much easier and faster to run a single query:

"Select FirstName & ' ' & LastName as ContactName " _
& "from Individuals inner join ContactWebComs " _
& "on Individuals.ContactID = ContactWebComs.ContactID " _
& "where WebComID=" & Me.ContactWebComs_WebComID _
& " and Individuals.ContactID<>" & Me.ContactID

Note that the concatenation of first and last name is happening in the query
also.

3. The usual way to traverse a recordset is not to use RecordCount as a
count loop limit, but instead to loop until the EOF condition is met:

Do Until rstTemp.EOF
' do something with record
rstTemp.MoveNext
Loop

Then you don't need to do a MoveLast/MoveFirst to ascertain the RecordCount.

4. This is a style/readability thing: if you are going to use string
variables to construct messages and SQL strings and suchlike, set their
values just before you use them, otherwise someone trying to read the code
has to scroll back and forth to remember what's in the string.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks Graham.

Sorry for the delayed response. I wanted to see if I could cobble
something together myself without asking for help everytime. The
fruits of my labour appear below. Again, any suggestions for
improvement would be appreciated.


Private Sub txtWebComType_BeforeUpdate(Cancel As Integer)

'Prevent user warnings
DoCmd.SetWarnings False

If IsNull(Me.txtWebComType) Then

' Dimension variables
Dim strMsg_Del As String ' First delete confirmation
Dim strTitle_Del As String ' Title for first delete
confirmation
Dim strMsg_Contacts As String ' Second delete confirmation
(record relates to other contacts)
Dim strTitle_Contacts As String ' Title for second delete
confirmation

Dim strSQL_Contacts As String ' SQL string to check if record
relates to other Contacts
Dim strSQL_ScrollBars As String ' SQL string to determine if
vertical scrollbars are required
Dim strSQL_DeleteMain As String ' SQL string to delete entry in
main (not junction) table
Dim strSQL_ContactNames As String ' SQL string to get names of
related contacts

Dim rstTemp As DAO.Recordset ' Recordset to store details of
other Contacts related to existing record

' Initialise variables
strMsg_Del = "Would you like to delete this entry?"
strTitle_Del = "Delete WebCommunication Record"
strMsg_Contacts = "This record relates to the other Contacts listed
below. " & vbCrLf
strMsg_Contacts = strMsg_Contacts & "Do you want to want to delete
the record for these Contacts too?"
strTitle_Contacts = "Multi-Contact Record"

strSQL_Contacts = "SELECT * FROM ContactWebComs " & _
"WHERE ([ContactWebComs].[WebComID]=" &
Me.ContactWebComs_WebComID & ") " & _
"AND NOT ([ContactWebComs].[ContactID] = " &
Me.ContactID & ")"

strSQL_DeleteMain = "Delete * from WebComs where WebComID=" &
Me.ContactWebComs_WebComID & ""

strSQL_ContactNames = "SELECT Individuals.FirstName,
Individuals.LastName FROM Individuals WHERE Individuals.ContactID IN "

' Undo changes to existing row
Me.Undo
Cancel = True

' Make sure that the user is not simply trying to delete an empty
row
If IsNull(Me.ContactWebComs_WebComID) Then

' Row did not relate to an existing record - no need to access
database
' Just remove the empty row on the form
Me.AllowAdditions = False

' Row relates to an existing record
Else

' Does user want to proceed with delete?
If MsgBox(strMsg_Del, vbQuestion Or vbYesNo Or
vbDefaultButton2, strTitle_Del) _
= vbYes Then

' Delete command confirmed - now assess the user's options
' First: Check whether entry relates to other Contacts in
the Junction Table
Set rstTemp = CurrentDb.OpenRecordset(strSQL_Contacts)

' Make sure the recordset is not empty
If Not rstTemp.EOF Then

' record relates to other Contacts
Dim fld As Field
Dim strFields As String
Dim strContactIDS As String
Dim strContactNames As String

strContactIDS = "("
strContactNames = ""

rstTemp.MoveLast
rstTemp.MoveFirst

' Enumerate related ContactIDs
For i = 1 To rstTemp.RecordCount
strContactIDS = strContactIDS &
rstTemp.Fields("ContactID").Value
strContactIDS = strContactIDS & ","
rstTemp.MoveNext
Next i

' Remove last comma
strContactIDS = Mid(strContactIDS, 1,
(Len(strContactIDS) - 1))
strContactIDS = strContactIDS & ")"

' Clear existing recordset variable
rstTemp.Close
Set rstTemp = Nothing

' reset recordset variable
Set rstTemp =
CurrentDb.OpenRecordset(strSQL_ContactNames & strContactIDS)

rstTemp.MoveLast
rstTemp.MoveFirst

' Enumerate related Contact Names
For i = 1 To rstTemp.RecordCount
strContactNames = strContactNames &
rstTemp.Fields("FirstName").Value & " "
strContactNames = strContactNames &
rstTemp.Fields("LastName").Value & vbCrLf
rstTemp.MoveNext
Next i

' Query user: delete the underlying record or just
the Junction Table entry?
Select Case MsgBox(strMsg_Contacts & vbCrLf &
vbCrLf & _
"Related Contacts: " & vbCrLf & strContactNames, _
vbQuestion + vbYesNoCancel + vbDefaultButton2,
strTitle_Contacts)

Case vbYes
MsgBox ("Confirmed delete underlying table.")
GoTo Delete_Main
Case vbNo
MsgBox ("Confirmed delete junction table
entries only." & _
vbCrLf & vbCrLf & "strSQL_DeleteMain: " &
strSQL_DeleteMain)
GoTo Delete_Junction
Case vbCancel
Exit Sub
Case Else
Stop
End Select

Else
' If recordset is empty, record relates to this Contact only
GoTo Delete_Main
End If

End If ' User has cancelled delete command - No need to for
extra Undo and Cancel

End If ' Close second If ... Then statement
(IsNull(Me.ContactWebComs_WebComID))

End If ' Close first If ... Then statement (IsNull(Me.txtWebComType))

'Reset original settings
DoCmd.SetWarnings True

Exit Sub

Delete_Main:

' Delete entries in the primary table (cascade delete will delete
junction table entries)
CurrentDb.Execute strSQL_DeleteMain, dbFailOnError

Me.Requery

' Make sure there is no empty "add new record" row
Me.AllowAdditions = False

Exit Sub


Delete_Junction:

' WebCom relates to other Contacts, delete Junction Table entries
only
Set rstTemp2 = Me.RecordsetClone

' Make sure recordset is not empty
If Not Me.RecordsetClone.EOF Then

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

' Make sure there is no empty "add new record" row
Me.AllowAdditions = False

' Clean Up
rstTemp2.Close
Set rstTemp2 = Nothing

Exit Sub

End If

End Sub




Regards
Bob



Graham Mandeno wrote:

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

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

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


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

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