My problem was that I was trying to do everything in Form_BeforeDelConfirm -
rather than Form_Delete
Now I only have this in Form_BeforeDelConfirm:
Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
Response = acDataErrContinue
End Sub
and do all the heavy lifting in Form_Delete.
here's the code if anyone's interested...
Private Sub Form_Delete(Cancel As Integer)
On Error GoTo HandleErr
Dim strDa As String
Dim j As String
Dim strDoc As String
Dim strSql As String
Dim strNh As String
Dim strHs As String
Dim strCh As String
Dim strOe As String
Dim strMsg As Variant
Dim intResponse As Integer
Dim lngCt As Long
Dim rst As DAO.Recordset
Dim objFile As Object
strDoc = HyperlinkPart(Me!Document, 0)
intResponse = MsgBox("Are you sure you want to unlink the document:" &
vbCrLf & vbCrLf & strDoc & " ?", vbQuestion + vbOKCancel + vbDefaultButton1,
" Confirm Unlink")
If intResponse = vbCancel Then
Cancel = True
Exit Sub
End If
'get list of any other entities linked to document
strSql = "SELECT Entity_ID FROM tblDocuments WHERE
HyperlinkPart(Document, 0) = " & Chr(34) & strDoc & Chr(34) & " AND
Entity_ID <> " & Me!Entity_ID
Set rst = CurrentDb.OpenRecordset(strSql)
Do Until rst.EOF
lngEid = rst!Entity_ID
strNh = Nz(DLookup("FullName", "qryEntity", "Entity_ID = " & lngEid), 0)
strCh = Nz(DLookup("Company", "qryEntity", "Entity_ID = " & lngEid), 0)
If strNh = "0" Then
strHs = strCh
End If
If strCh = "0" Then
strHs = strNh
End If
If strNh <> "0" And strCh <> "0" Then
strHs = strNh & ", " & strCh
End If
strOe = "Entity ID " & lngEid & vbTab & strHs & vbCrLf & strOe
rst.MoveNext
Loop
lngCt = rst.RecordCount
'alert to other entities linked to document
If lngCt = 1 Then
strMsg = "The following entity is also linked to " & strDoc & ": "
Else
strMsg = "The following entities are also linked to " & strDoc & ":
"
End If
If lngCt >= 1 Then intResponse = MsgBox(strMsg & vbCrLf & vbCrLf &
strOe, vbInformation, " Other Entities Linked To Document")
If DLookup("DocOpt", "tblOutput") <> 3 Then 'deletion only applicable
when using linked document folder
intResponse = MsgBox("Do you want to delete " & strDoc & " after it
is unlinked from this entity?", vbYesNo + vbExclamation + vbDefaultButton1,
" Confirm Delete")
If intResponse = vbYes Then
j = (InStr(1, Me!Document, "#", 1)) + 7
strDa = Right(Me!Document, Len(Me!Document) - j)
Set objFile = CreateObject("Scripting.FileSystemObject")
objFile.DeleteFile strDa
End If
End If
Set objFile = Nothing
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryModDocs"
DoCmd.SetWarnings True
Exit_Here:
Exit Sub
HandleErr:
Select Case Err.Number
Case 52 'Bad file name or number
Resume Next
Case 53 'File not found
Resume Next
Case Else
modHandler.LogErr (Me.Form.Name)
Resume Exit_Here
End Select
End Sub
"rkc" <rk*@yabba.dabba.do.rochester.rr.nope> wrote in message
news:ur******************@twister.nyroc.rr.com...
"deko" <dj****@hotmail.com> wrote in message
news:Cj****************@newssvr25.news.prodigy.com ... I have a form with a subform datasheet - I need code behind the OnDelete
event of the subform:
Private Sub Form_Delete(Cancel As Integer)
'do something that depends on which record is deleted
End Sub
This works okay when only one record is selected and the delete key is
pressed (or right-click delete is selected - makes no difference). But
if multiple records are selected in the datasheet, the code runs for the first record only.
What exactly are you trying to do?
When I try this the Delete event is fired, in succession, for each
selected record.
The following debug prints the asked for information for each
selected record when the Delete key is pressed.
Private Sub Form_Delete(Cancel As Integer)
With Me
Debug.Print !EmployeeID;" "; !LastName; " "; !FirstName
End With
Cancel = True
End Sub
