Do not Print Report if one of the parameters is null 1

[villsthearcher]

Hello,

I am running a Report from a Form, which is used to enter criteria values. I am also using a Button in the Form, to pass the criteria values to the Query, which is used by the report, and opens up the Report with the corresponding data.

Now, I do not want the Report to Run/Open if one of the Parameter/Criteria vales is Null. Rather, I want a message to show "please enter all the criteria values".

I also desire to display a message "No records found according to the entered criteria" to ensure that a Report does not open with no data in it.

If some one could help me in this regard, it would be great.

Thanks,
Villsthearcher

 

[dhookom]

I assume you are openning the report with code. You can add lines of code prior to the OpenReport like:

Private Sub .....
On Error GoTo errOpenReport
If IsNull(Me.txtStartDate) Or _
      IsNull(Me.txtEndDate) Or _
      IsNull(Me.cboDepartment) Then
      MsgBox "You must enter all criteria values", _
           vbokonly+vbInformation, "PEBKAC"
   Else
      DoCmd.OpenReport....
End If
ExitOpenReport:
Exit Sub

errOpenReport:
    Select Case Err
    Case 2501
    Case Else
       Msgbox "Error: " & Err.Number & Err.Description
    End Select
    Resume ExitOpenReport
End Sub

You can add code to cancel the report printing in the On No Data event of the report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[villsthearcher]

Hi dhookom,

Thanks!

I am not opening the Report with a code. I simply used the Wizard to create the Report.

Is there anyway I could do, what I want, without a code. Because, first I am not good with code and the second is that we do not want to hard code anything.

But, still I will try to implement your code. I am not that poor in coding..lol.

villsthearcher

Thanks,
villsthearcher

 

[dhookom]

You first stated "running a Report from a Form" then you stated "I am not opening the Report with a code. I simply used the Wizard to create the Report."

What does a user do to open the report?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[villsthearcher]

Hi dhookom,

I am sorry if I used the wrong satatement "running the report".

All I am saying is that, I used a wizard to create the Report. The user opens the form and enters the parameter values and clicks on the button. on doing so it opens a report.

Now I want to add some features that I had previously mentioned to this process.

Thanks for your time.

Thanks,
villsthearcher

 

[dhookom]

The button most likely has code behind it that opens the report. Find that code and modify it as I noted in my first response.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[villsthearcher]

supeerrrrrr!!

I took a self crash course on vba coding! I realized that your first response with the code was adequate enough for solving my problem.



Thanks,
villsthearcher

 

[misscrf]

I am having the same issue. Can someone please help?

I have a form to choose a report, and like 80% of the code is working. It is the last part that makes sure the date criteria is entered.

Here is a pic of the form, to give you a visual:

Here is the ENTIRE code that I have on the onclick of the command button. The part in red is the problem that I am having:

Private Sub cmdOpenReport_Click()
On Error GoTo ErrorHandler
Dim Msg, Style, Title
Dim gstrReportName As String
Dim gstrWhere As String

Select Case Forms![frmReports]![GrpReportType]
Case 1
gstrReportName = "rptExclusions"

Case 2
gstrReportName = "rptObjections"

Case 3
gstrReportName = "rptNoForwardingAddress"

Case 4
gstrReportName = "rptUpdatedAddress"

Case 5
gstrReportName = "rptCommentsQuestions"

End Select

Select Case Forms![frmReports]![GrpReportDate]

   Case 1 'today
       gstrWhere = "ActivityDate = #" & Date & "#"   'SQL wants hashes around dates
       
   Case 2  'a particular date
       gstrWhere = "ActivityDate =#" & Me.txtFromChoose & "#"
       
   Case 3 ' date range
       gstrWhere = "ActivityDate between #" & Me.txtFromChoose & "# and #" & Me.txtTo & "#"
       
   Case 4 'all dates
      gstrWhere = ""                            'we are selecting all records so no filter needed"

End Select

DoCmd.SetWarnings False
[COLOR=#ff0000]If (Forms![frmReports]![GrpReportDate] = 2 And IsNull(Me.txtFromChoose)) Then[/color]
Msg = "You must enter a date in the Choose Date field!"
    Style = vbOKOnly
    Title = "Date Must be Entered!"
[COLOR=#ff0000]ElseIf (Forms![frmReports]![GrpReportDate] = 3 And (IsNull(Me.txtFromChoose) Or IsNull(Me.txtTo))) Then[/color]
Msg = "You must enter a start AND end date!"
    Style = vbOKOnly
    Title = "Dates Must be Entered!"
Else

DoCmd.OpenReport gstrReportName, acViewPreview, , gstrWhere

End If
DoCmd.SetWarnings True
ExitHandler:
    Exit Sub

ErrorHandler:
If Err = 2501 Then
    Resume ExitHandler
    
    Else
        MsgBox Err.Description
        Resume ExitHandler
        
        End If
'    msgbox Err.Description
'    Resume Exit_cmdPreviewRpt_Click
End Sub

I have tried sooo many ways to write the statement that if the date option group is 2 and the textbox is empty then say a message. I can list those, but you get the idea.

The form textbox is formatted as short date with an input mask.

Here is the behavior. If I choose any report, then choose today, I get the accurate nodata message. If I choose all dates, I get the right report. If I choose 1 date and enter in a date, I get the right response (nodata message if there is no data, and data if there is some for that day.) Same behavior for the date range.

If I choose options 2 or 3 on the date option group, but do not enter in anything in the text box(s) then NOTHING happens. I don't get a message telling me to enter something, I don't get a report, or a nodata message...
:-(

Can anyone please help?

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

I would check for the missing dates by using IsDate(Me.txtFromChoose) and IsDate(Me.txtTo). I don't use InputMasks and this may be causing your issue.

I think you can replace
Forms![frmReports]![GrpReportDate]
with just
Me.[GrpReportDate]
Similar replacements can be made elsewhere.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[misscrf]

Thanks for responding.

I get this error message when I use the isdate(textfield)

also, I know I can replace those with the me. I don't know why I did it that way, but in debuggin it works, so "if it ain't broke..."


Any other thoughts?


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

I'm not sure where you used IsDate(). Try something like:

If Me.[GrpReportDate] = 2 And Not IsDate(Me.txtFromChoose) Then
Msg = "You must enter a date in the Choose Date field!"
    Style = vbOKOnly
    Title = "Date Must be Entered!"
ElseIf Me.[GrpReportDate] = 3 And (Not IsDate(Me.txtFromChoose) Or Not IsDate(Me.txtTo)) Then
Msg = "You must enter a start AND end date!"
    Style = vbOKOnly
    Title = "Dates Must be Entered!"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[misscrf]

That is exactly how I used it. I got the error above. Sorry for not specifying that before.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Place a breakpoint in your code so that you can step through it to see what is happening.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]