Private Sub cmdUpdate_Click()
On Error GoTo errHandler
[COLOR="SeaGreen"] 'save changes[/COLOR]
If Me.Dirty Then Me.Dirty = False
Dim N As Integer
[COLOR="SeaGreen"] 'I couldn't get this to work
'N = Nz(DLookup("SenderID", "DeptSeq", "DDate Between #" & Format(Me.txtDF, "mm/dd/yyyy") & "# AND #" & Format(Me.txtDT, "mm/dd/yyyy") & "#"), 0)[/COLOR]
[COLOR="seagreen"] 'This does work[/COLOR]
N = DCount("SenderID", "DeptSeq", "DDate Between #" & Format(Me.txtDF, "mm/dd/yyyy") & "# AND #" & Format(Me.txtDT, "mm/dd/yyyy") & "#")
If MsgBox("You are about to execute a major operation ! Do you really want to continue ?" & vbCrLf, vbYesNo, "Major Operation") = vbNo Then
MsgBox "Your operation execution is canceled !", vbOKOnly, "Operation Canceled"
Exit Sub
Else
If N > 1 Then
MsgBox N & " records in the selected date range will be deleted.", vbCritical, "Existing Data"
ElseIf N = 1 Then
MsgBox N & " record in the selected date range will be deleted.", vbCritical, "Existing Data"
Else
MsgBox "No records to delete"
End If
[COLOR="seagreen"] 'this deletes all existing records - not just those where SenderID Is Not Null[/COLOR]
CurrentDb.Execute "DELETE DeptSeq.*" & _
" FROM DeptSeq" & _
" WHERE (DDate Between #" & Format(Me.txtDF, "mm/dd/yyyy") & "# AND #" & Format(Me.txtDT, "mm/dd/yyyy") & "#)", dbFailOnError
[COLOR="SeaGreen"] 'this would only delete those where SenderID Is Not Null
'however it can't be used as your DAOAdding procedure replaces all values[/COLOR]
[COLOR="seagreen"] ' CurrentDb.Execute "DELETE DeptSeq.*" & _
" FROM DeptSeq" & _
" WHERE (((DeptSeq.DDate) Between #" & Format(Me.txtDF, "mm/dd/yyyy") & "# AND #" & Format(Me.txtDT, "mm/dd/yyyy") & "#) AND ((DeptSeq.SenderID) Is Not Null))", dbFailOnError[/COLOR]
[COLOR="seagreen"]
'add new records - NOT CHECKED or ALTERED
'However an APPEND query would do the same job and MUCH faster[/COLOR]
DAOAdding
[COLOR="SeaGreen"] ' On Error Resume Next 'NOT NEEDED[/COLOR]
MsgBox "Your operation execution has been completed successfully.", vbOKOnly, "Successful Operation"
End If
ExitProc:
Exit Sub
errHandler:
MsgBox Prompt:=Err & ": " & Err.Description, buttons:=vbOKOnly, Title:="Error"
Resume ExitProc
End Sub