Date parameters for report

[gorsoft]

I am using a form opened in dialog mode to enter start and end dates
as parameters for a report. The form is opened when I call the report.
Once the date parameters are entered, I make the diaolog form
invisible and allow the rest of the report code to run.

The form is specified in a constantrivate Const ScheduleCriteriaForm
As String = "frmChooseReportDate2"
Then in the open event of the report:
DoCmd.OpenForm ScheduleCriteriaForm, acNormal, , , , acDialog
' See if criteria form is still open:
On Error Resume Next
Set objFRM = Forms(ScheduleCriteriaForm)
' Stop report from opening if criteria form is closed:
If Err.Number <> 0 Then
Cancel = True
End If

Then I build the code for the data source of the report (strSQL) and
the criteria (strWhere) and the rest of the report code is as follows:

'Initialize SELECT statement.
MyRecordsource = strSql
' Set RecordSource property of Details Report.
Reports!rptInvoiceSchedule.RecordSource = MyRecordsource


In the close event of the report, the invisible dialog box itself is
closed.

This all works fine but I want to insert error trapping code where the
user forgets to enter either or both of the date parameters (txtFrom
and txtTo) in the dialog box.

I have tried the following:

Select Case Forms!frmChooseReportDate2!txtFrom
Case Is = Null
strMsg = "You must enter a date in both fields."
If MsgBox(strMsg, vbOKCancel) = vbCancel Then Exit Sub
Case Is = Not Null
Select Case Me!txtTo
Case Is = Null
strMsg = "You must enter a date in both fields."
If MsgBox(strMsg, vbOKCancel) = vbCancel Then Exit Sub
Case Is = Not Null
'Do nothing
End Select
End select

But where should I place this code? I tried putting it in the View
Report button on the dialog form but this just hangs.

Any ideas appreciated.

Gina

 

[gorsoft]

In the event of the form where you hide it, check for the missing values:

If Len([txtStartDate & vbNullString) > 0 Then ' This one is OK
    If Len([txtEndDate & vbNullString) >0 Then
' Hide the form
Else
    MsgBox "No way Jose, fill in the dates", vbOKOnly
    Exit Sub
End If
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.accessmvp.comhttp://www.mvps.org/access
Co-author: "Access Solutions", published by Wiley

I am using a  form opened in dialog mode to enter start and end dates
as parameters for a report. The form is opened when I call the report.
Once the date parameters are entered, I make the diaolog form
invisible and allow the rest of the report code to run.

The form is specified in a constantrivate Const ScheduleCriteriaForm
As String = "frmChooseReportDate2"
Then in the open event of the report:
DoCmd.OpenForm ScheduleCriteriaForm, acNormal, , , , acDialog
' See if criteria form is still open:
On Error Resume Next
Set objFRM = Forms(ScheduleCriteriaForm)
' Stop report from opening if criteria form is closed:
If Err.Number <> 0 Then
Cancel = True
End If

Then I build the code for the data source of the report (strSQL) and
the criteria (strWhere) and the rest of the report code is as follows:

'Initialize SELECT statement.
MyRecordsource = strSql
' Set RecordSource property of Details Report.
Reports!rptInvoiceSchedule.RecordSource = MyRecordsource

In the close event of the report, the invisible dialog box itself is
closed.

This all works fine but I want to insert error trapping code where the
user forgets to enter either or both of the date parameters (txtFrom
and txtTo) in the dialog box.

I have tried the following:

Select Case Forms!frmChooseReportDate2!txtFrom
Case Is = Null
strMsg = "You must enter a date in both fields."
If MsgBox(strMsg, vbOKCancel) = vbCancel Then Exit Sub
Case Is = Not Null
   Select Case Me!txtTo
   Case Is = Null
   strMsg = "You must enter a date in both fields."
   If MsgBox(strMsg, vbOKCancel) = vbCancel Then Exit Sub
   Case Is = Not Null
   'Do nothing
   End Select
End select

But where should I place this code? I tried putting it in the View
Report button on the dialog form but this just hangs.

Any ideas appreciated.

Gina- Hide quoted text -

- Show quoted text -

Thanks for your reply Arvin but putting that code in the event of the
form where I hide it (the view report button) does nothing - no error
message - nothing. When I then close that dialog box, I can see an
error message about cancelling an event (OpenReportCancel or
something). There must be something in the open event of the report
(see code above) which is causing this.

Gina

 

[gorsoft]

In the event of the form where you hide it, check for the missing values:

If Len([txtStartDate & vbNullString) > 0 Then ' This one is OK
    If Len([txtEndDate & vbNullString) >0 Then
' Hide the form
Else
    MsgBox "No way Jose, fill in the dates", vbOKOnly
    Exit Sub
End If

- Show quoted text -

Thanks for your reply Arvin but putting that code in the event of the
form where I hide it (the view report button) does nothing - no error
message - nothing. When I then close that dialog box, I can see an
error message about cancelling an event (OpenReportCancel or
something).  There must be something in the open event of the report
(see code above) which is causing this.

Gina- Hide quoted text -

- Show quoted text -

Update:

As is usually the case with these things, if you look at the problem
long enough, you manage to sort it out yourself. A misplaced "End if"
was all that was wrong!

Gina