If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
#Errors-on report
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. |
#2
|
|||
|
|||
#Errors-on report
On Tue, 6 Dec 2005 15:50:01 -0800, gg wrote:
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. 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 -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
#3
|
|||
|
|||
#Errors-on report
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! "fredg" wrote: On Tue, 6 Dec 2005 15:50:01 -0800, gg wrote: 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. 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 -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
#4
|
|||
|
|||
#Errors-on report
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!!! "gg" wrote: 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. |
#5
|
|||
|
|||
#Errors-on report
On Tue, 6 Dec 2005 16:44:01 -0800, gg wrote:
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!!! "gg" wrote: 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. 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. -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
#6
|
|||
|
|||
#Errors-on report
On Tue, 6 Dec 2005 18:20:32 -0800, fredg wrote:
On Tue, 6 Dec 2005 16:44:01 -0800, gg wrote: 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!!! "gg" wrote: 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. 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. 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. -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
#7
|
|||
|
|||
#Errors-on report
Fred,
Thank you for your detailed response. Appreciate all of your help. gg "fredg" wrote: On Tue, 6 Dec 2005 18:20:32 -0800, fredg wrote: On Tue, 6 Dec 2005 16:44:01 -0800, gg wrote: 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!!! "gg" wrote: 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. 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. 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. -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
subreport not displaying in main report | JohnLute | Setting Up & Running Reports | 15 | November 17th, 2005 04:02 PM |
Has anyone seen this behaviour? What might it be? | tw | General Discussion | 4 | June 30th, 2005 03:23 PM |
To Sharkbyte and all: Calculate a total values in group level | Ally | General Discussion | 6 | June 13th, 2005 08:16 PM |
Still Hoping for help with a Query problem | Don Sealer | Using Forms | 15 | November 13th, 2004 06:24 AM |
Save Report With CreateReport Coding Issue | Jeff Conrad | Setting Up & Running Reports | 8 | July 12th, 2004 08:39 AM |