Between And strCriteria Cancel

[Andy]

Hi;

The code below "Automates the Report's Title", (ala Martin Green).

It works correctly, when the user enters the date ranges and clicks the OK
button.

If the user clicks the Cancel button the error trap msg is displayed.

What is needed is something along the lines of "vbOkCancel" that works with
a MsgBox.

When a MsgBox is used the code either doesn't work at all or the MsgBox
appears before the "Between And" dialog box appears and the Cancel on the
dialog box still displays the Error Trap Msg.

Have searched Microsoft MSDN and KB and, and, and a few "Gazillion" more and
haven't found the solution. Would someone be so kind to point me in the
correct direction?

Thank You for taking the time to read this Post.

Andy

Private Sub cmdContractDateRange_Click()
On Error GoTo Err_cmdContractDateRange_Click
Dim strCriteria As String
strCriteria = "[ContractDate] Between [Enter Beginning Contract Date:]
And [Enter Ending Contract Date:]"

' Change the Report Label
DoCmd.Echo False
DoCmd.OpenReport "rptContracts", acViewDesign
With Reports("rptContracts")
.Controls("lblrptCriteria").Caption = "By Date Range"
End With
DoCmd.Close , , acSaveYes
DoCmd.Echo True

' Open the Report
DoCmd.OpenReport "rptContracts", acPreview, , strCriteria

Exit_cmdContractDateRange_Click:
Exit Sub

Err_cmdContractDateRange_Click:
MsgBox "This command ''cmdContractDateRange'' isn't working correctly. "
& vbCrLf & "Please inform Your manager."
Resume Exit_cmdContractDateRange_Click

End Sub

 

[Wayne Morgan]

Have the error message include the error number (Err.Number). This will tell
you what error number you are getting when a user clicks Cancel. You can
then test for this error number in the error handler. Display your message
if the number isn't this number by using an If...Then statement.

It is not necessary to change the label in design view and save the changes
to the report. What you may find works better than prompting a user twice
for parameters (start date and end date) is to create a form with two
textboxes, one for each date. You could even use something like Stephen
Lebans's date picker (http://www.lebans.com/monthcalendar.htm) to help the
user pick the date. You would have two buttons on this form, an Ok button
and a Cancel button. The Cancel button would close the form, the Ok button
would verify that valid dates had been entered then hide the form. The
reason for hiding the form is because when you open it, you would use the
acDialog window mode argument to open it. This will cause the calling code
to pause until you close or hide the form. With the form hidden the code
will continue, but since the form is still open the values entered can be
read. The code in your cmdContractDateRange button would then see if the
form is still open and, if so, get the dates entered and use them for
strCriteria. If not, then the user chose Cancel, so exit the routine. You
can use the IsLoaded property of the AllForms collection to see if the form
is open.

In the Open event of the form, you could have it check its Filter property.
If the filter starts with "[ContractDate] Between ", then change the label's
caption.

If Left(Me.Filter, 22) = "[ContractDate] Between" Then
Me.lblrptCriteria.Caption = "By Date Range"
End If

Close the popup form when you exit your button's routine.

Exit_cmdContractDateRange_Click:
DoCmd.Close acForm, "frmMyPopup", acSaveNo
Exit Sub

--
Wayne Morgan
MS Access MVP

Hi;

The code below "Automates the Report's Title", (ala Martin Green).

It works correctly, when the user enters the date ranges and clicks the OK
button.

If the user clicks the Cancel button the error trap msg is displayed.

What is needed is something along the lines of "vbOkCancel" that works
with
a MsgBox.

When a MsgBox is used the code either doesn't work at all or the MsgBox
appears before the "Between And" dialog box appears and the Cancel on the
dialog box still displays the Error Trap Msg.

Have searched Microsoft MSDN and KB and, and, and a few "Gazillion" more
and
haven't found the solution. Would someone be so kind to point me in the
correct direction?

Thank You for taking the time to read this Post.

Andy

Private Sub cmdContractDateRange_Click()
On Error GoTo Err_cmdContractDateRange_Click
Dim strCriteria As String
strCriteria = "[ContractDate] Between [Enter Beginning Contract Date:]
And [Enter Ending Contract Date:]"

' Change the Report Label
DoCmd.Echo False
DoCmd.OpenReport "rptContracts", acViewDesign
With Reports("rptContracts")
.Controls("lblrptCriteria").Caption = "By Date Range"
End With
DoCmd.Close , , acSaveYes
DoCmd.Echo True

' Open the Report
DoCmd.OpenReport "rptContracts", acPreview, , strCriteria

Exit_cmdContractDateRange_Click:
Exit Sub

Err_cmdContractDateRange_Click:
MsgBox "This command ''cmdContractDateRange'' isn't working correctly.
"
& vbCrLf & "Please inform Your manager."
Resume Exit_cmdContractDateRange_Click

End Sub

 

[Andy]

Wayne;

Thank You.

Andy

Have the error message include the error number (Err.Number). This will tell
you what error number you are getting when a user clicks Cancel. You can
then test for this error number in the error handler. Display your message
if the number isn't this number by using an If...Then statement.

It is not necessary to change the label in design view and save the changes
to the report. What you may find works better than prompting a user twice
for parameters (start date and end date) is to create a form with two
textboxes, one for each date. You could even use something like Stephen
Lebans's date picker (http://www.lebans.com/monthcalendar.htm) to help the
user pick the date. You would have two buttons on this form, an Ok button
and a Cancel button. The Cancel button would close the form, the Ok button
would verify that valid dates had been entered then hide the form. The
reason for hiding the form is because when you open it, you would use the
acDialog window mode argument to open it. This will cause the calling code
to pause until you close or hide the form. With the form hidden the code
will continue, but since the form is still open the values entered can be
read. The code in your cmdContractDateRange button would then see if the
form is still open and, if so, get the dates entered and use them for
strCriteria. If not, then the user chose Cancel, so exit the routine. You
can use the IsLoaded property of the AllForms collection to see if the form
is open.

In the Open event of the form, you could have it check its Filter property.
If the filter starts with "[ContractDate] Between ", then change the label's
caption.

If Left(Me.Filter, 22) = "[ContractDate] Between" Then
Me.lblrptCriteria.Caption = "By Date Range"
End If

Close the popup form when you exit your button's routine.

Exit_cmdContractDateRange_Click:
DoCmd.Close acForm, "frmMyPopup", acSaveNo
Exit Sub

--
Wayne Morgan
MS Access MVP

Hi;

The code below "Automates the Report's Title", (ala Martin Green).

It works correctly, when the user enters the date ranges and clicks the OK
button.

If the user clicks the Cancel button the error trap msg is displayed.

What is needed is something along the lines of "vbOkCancel" that works
with
a MsgBox.

When a MsgBox is used the code either doesn't work at all or the MsgBox
appears before the "Between And" dialog box appears and the Cancel on the
dialog box still displays the Error Trap Msg.

Have searched Microsoft MSDN and KB and, and, and a few "Gazillion" more
and
haven't found the solution. Would someone be so kind to point me in the
correct direction?

Thank You for taking the time to read this Post.

Andy

Private Sub cmdContractDateRange_Click()
On Error GoTo Err_cmdContractDateRange_Click
Dim strCriteria As String
strCriteria = "[ContractDate] Between [Enter Beginning Contract Date:]
And [Enter Ending Contract Date:]"

' Change the Report Label
DoCmd.Echo False
DoCmd.OpenReport "rptContracts", acViewDesign
With Reports("rptContracts")
.Controls("lblrptCriteria").Caption = "By Date Range"
End With
DoCmd.Close , , acSaveYes
DoCmd.Echo True

' Open the Report
DoCmd.OpenReport "rptContracts", acPreview, , strCriteria

Exit_cmdContractDateRange_Click:
Exit Sub

Err_cmdContractDateRange_Click:
MsgBox "This command ''cmdContractDateRange'' isn't working correctly.
"
& vbCrLf & "Please inform Your manager."
Resume Exit_cmdContractDateRange_Click

End Sub