I think you have all this in the wrong order. Instead of having the code to open the frmDatePicker in the opening event of the report itself you should open it first from wherever you open the report (assuming is a form). On the form, once the dates are entered and validated open the reports using Docmd.OpenReport. Otherwise use Micron's suggestion of checking for a public boolean variable and cancel the report open event (Docmd.CancelEvent does nothing as it runs in the form not the report):
Code:
Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Date_Picker", , , , , acDialog, "Please Enter Dates."
If boCanceled = True Then
Cancel=True
End if
End Sub
'Cancel Button Code:
Private Sub Cancel_Click()
On Error GoTo Err_Cancel_Click
DoCmd.Close acForm, "Date_Picker"
'DoCmd.CancelEvent
boCanceled=True 'Public boCanceled as boolean variable declared in a standard module
Exit_Cancel_Click:
Exit Sub
Err_Cancel_Click:
MsgBox Err.Description
Resume Exit_Cancel_Click
Cheers,