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

Graham Mandeno wrote:

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

"Bob" wrote in message
...
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