#Errors-on report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that asks the user for a processing date. They would enter
mm/01/yyyy and the report would be generated. However, if that is an invalid
entry (that date does not exist in the Processing table) how do you go about
displaying a prompt letting the user know that the selection is invalid so
that they won't run the report using an invalid date and get #errors? They
currently select the report from a menu form.
 
I have a report that asks the user for a processing date. They would enter
mm/01/yyyy and the report would be generated. However, if that is an invalid
entry (that date does not exist in the Processing table) how do you go about
displaying a prompt letting the user know that the selection is invalid so
that they won't run the report using an invalid date and get #errors? They
currently select the report from a menu form.
Click to expand...

To prevent the report from running if there are no records, code the
report's OnNoData event:

MsgBox "there are no records for the date you selected."
Cancel = True

This will generate a 2501 error, so you should trap it in the form
event used to open the report:

On Error GoTo Err_Handler
DoCmd.OpenReport "ReportName}, acViewPreview
Exit_Sub:
Exit Sub
Err_Handler:
If Err = 2501 then
Else
MsgBox "Error #: " & Err.Number & " " & Err.Description
End If
Resume Exit_Sub

A sure fire way to avoid the entry of a non-existent date is to add a
combo box to the form for the date selection. If you make it's Row
Source a Select Distinct query , only valid dates will be available
for selection.

Select Distinct YourTable.DateField From YourTable Order By
YourTable.DateField;

Change the query criteria to:
Forms!FormName!ComboName
 
Thank you for your quick response!

I like using your combo box suggestion.
I'm not quite sure at what point I need to add the select distinct statement.
The report is already pulling in a query called qryBilling. It prompts for
the processing date in this query. Do I need to take out this prompt and
only prompt in the new combo box (with the drop down dates) in the new form?

Sorry! Thanks for your suggestions!
 
Hi Fred,

I think I almost got it...except =Name? is being displayed in the
ComboBox. Once I click on the down arrow, the dates are all there, but I am
unable to select any date. What else do I need to do?

Thank you!!!
 
Hi Fred,

I think I almost got it...except =Name? is being displayed in the
ComboBox. Once I click on the down arrow, the dates are all there, but I am
unable to select any date. What else do I need to do?

Thank you!!!
Click to expand...


1) The Combo Box should not be bound to any table, i.e. it's control
source should be blank. When you look at the combo in Design View, it
should say Unbound.

2) Examine the SQL of the Combo Box RowSource property.
It will be (with your own table and field names:

Select TableName.FieldName From YourTable Order By
YourTable.FieldName;

Change it to:
Select Distinct TableName.FieldName etc.

That assures that even if you have more than one record for any given
date, that date will only show once in the combo drop-down.

Set the Combo box bound column to 1.
Set the column Count to 1
Set the Column Widths to 1" (or whatever size you need)
Set the LimitToList property to Yes.
 
1) The Combo Box should not be bound to any table, i.e. it's control
source should be blank. When you look at the combo in Design View, it
should say Unbound.

2) Examine the SQL of the Combo Box RowSource property.
It will be (with your own table and field names:

Select TableName.FieldName From YourTable Order By
YourTable.FieldName;

Change it to:
Select Distinct TableName.FieldName etc.

That assures that even if you have more than one record for any given
date, that date will only show once in the combo drop-down.

Set the Combo box bound column to 1.
Set the column Count to 1
Set the Column Widths to 1" (or whatever size you need)
Set the LimitToList property to Yes.
Click to expand...

I forgot to add that the form must be open when the query is run.
And I see you actually are using the query in a report. So...

Code the Report's Open Event:
DoCmd.OpenForm "FormName", , , , , acDialog

Code the Report's Close event:
DoCmd.Close acForm, "FormName"

Add a command button to the form.
Code it's click event:

Me.Visible = false

In the query, set the criteria for the date field to:
forms!FormName!ComboName

Open the report.
The report will open this form.
Enter the date and click on the command button.
The form will become not visible.
The report will run.
When you close the report, it will also close the form.
 
Back
Top