Stop a report from running if query is empty

[kcdoell]

Hello:

What is the best way to stop a report from running if the query is empty? Currently, I have a form that has a command button on it. The user has to make selections from 3 combo boxes on the form and then via the cmdbutton the report opens in preview mode. I want to stop it from executing if the query is empty. I thought I could do this by checking the record count by the following code but I error out with the following message:

Too few parameters. Expected <number>. (Error 3061)
This parameter query requires the specified number of parameters, which you did not supply. Provide the expected number of parameters, and run the query again. 
 

Here is my code:

Private Sub cmdRptSummary_Click()
'Checks to see if any of the controls are null
 
    If Nz([CboYear], 0) = 0 Or Nz([CboMonth], 0) = 0 _
    Or Nz([cboWeek], 0) = 0 Then
 
      If Nz([CboYear], 0) = 0 Then MsgBox "Please select the Forecast Year from the" & _
    " drop down menu", 64, "Select Forecast Year"
    If Nz([CboMonth], 0) = 0 Then MsgBox "Please select the Forecast Month from the" & _
    " drop down menu", 64, "Select Forecast Month"
      If Nz([cboWeek], 0) = 0 Then MsgBox "Please select the Forecast Week from the" & _
    " drop down menu", 64, "Select Forecast Week"
 
       Else
 
'If no null values then check to see if there is data to report on
 
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("QryForecastSummary")
 
If rst.BOF And rst.EOF Then 'If none, then end process and send out MsgBox
 
        MsgBox "Given your selections, there are no records to delete and reload.", 64, "No Records Match"
 
     Else
 
'If there is data, open the Forecast Summary Report
 
    DoCmd.OpenReport "SumReport", acViewPreview
 
  End If
    End If
End Sub
 

The error focus is on the following line:

Set rst = CurrentDb.OpenRecordset("QryForecastSummary")
 

I never set a openrecordset to a query before so I am thinking that that is where my problem is.... I now the query works when I test it without the command button...

Any ideas?

Thanks,

Keith.

[Stewart Ross]

Hi Keith. The OpenRecordset method generally works fine with queries. I have checked that it works without error on a query returning no records (Query2 in the code below, deliberately set so that the WHERE clause won't return any records), and it works as expected - the recordcount property is 0 indicating no records.

I reckon the parameter error is resulting from the underlying query itself - I have seen such errors occur when a query refers to a form control (in the criteria of the query) which may work fine in the query editor and fail when opened in other contexts. There is a known bug in Access over the use of form field references in queries.

Could you check the underlying query to see whether it refers to a form control in its criteria somewhere?

Cheers

Stewart

    Dim RS As DAO.Recordset
    Set RS = CurrentDb.OpenRecordset("Query2")
    If RS.RecordCount = 0 Then
        MsgBox ("Empty")
    Else
        MsgBox ("Not Empty: " & RS.RecordCount)
    End If
    RS.Close
 

[kcdoell]

Stewart:

Thanks for the reply, and yes my query does refer to my combo boxes that are located on my form. Let me try your idea and see what I come up with...

Thanks,

Keith

[ADezii]

Hello:

What is the best way to stop a report from running if the query is empty? Currently, I have a form that has a command button on it. The user has to make selections from 3 combo boxes on the form and then via the cmdbutton the report opens in preview mode. I want to stop it from executing if the query is empty. I thought I could do this by checking the record count by the following code but I error out with the following message:

Too few parameters. Expected <number>. (Error 3061)
This parameter query requires the specified number of parameters, which you did not supply. Provide the expected number of parameters, and run the query again. 
 

Here is my code:

Private Sub cmdRptSummary_Click()
'Checks to see if any of the controls are null
 
    If Nz([CboYear], 0) = 0 Or Nz([CboMonth], 0) = 0 _
    Or Nz([cboWeek], 0) = 0 Then
 
      If Nz([CboYear], 0) = 0 Then MsgBox "Please select the Forecast Year from the" & _
    " drop down menu", 64, "Select Forecast Year"
    If Nz([CboMonth], 0) = 0 Then MsgBox "Please select the Forecast Month from the" & _
    " drop down menu", 64, "Select Forecast Month"
      If Nz([cboWeek], 0) = 0 Then MsgBox "Please select the Forecast Week from the" & _
    " drop down menu", 64, "Select Forecast Week"
 
       Else
 
'If no null values then check to see if there is data to report on
 
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("QryForecastSummary")
 
If rst.BOF And rst.EOF Then 'If none, then end process and send out MsgBox
 
        MsgBox "Given your selections, there are no records to delete and reload.", 64, "No Records Match"
 
     Else
 
'If there is data, open the Forecast Summary Report
 
    DoCmd.OpenReport "SumReport", acViewPreview
 
  End If
    End If
End Sub
 

The error focus is on the following line:

Set rst = CurrentDb.OpenRecordset("QryForecastSummary")
 

I never set a openrecordset to a query before so I am thinking that that is where my problem is.... I now the query works when I test it without the command button...

Any ideas?

Thanks,

Keith.

Keith, there is no need to go through all this unnecessary work, Why not use the Report Event that was specifically designed for this situation?

1. In your Report's NoData() Event:
   

   Private Sub Report_NoData(Cancel As Integer)
     Cancel = True
   End Sub

2. In the Click() Event of your Command Button:
   

   Private Sub cmdReportSummary_Click()
   On Error GoTo Err_cmdRptSummary
   Const conCANCEL_REPORT_OPEN As Integer = 2501
    
      DoCmd.OpenReport "SumReport", acViewPreview
    
   Exit_cmdRptSummary:
     Exit Sub
    
   Err_cmdRptSummary:
     If Err.Number = 2501 Then     'Report Open was Canceled
       MsgBox "Report contains no valid data!", vbExclamation, "No Data to Report"
     Else
       MsgBox Err.Description, vbExclamation, "Error in Opening Report"
     End If
     Resume Exit_cmdRptSummary
   End Sub

3. Let me know how you make out.

[kcdoell]

ADezii:

Using the Report's NoData Event worked perfectly. Below was my end solution for those inquiring minds:

Private Sub cmdRptSummary_Click()
'Checks to see if any of the controls are null
 
    If Nz([CboYear], 0) = 0 Or Nz([CboMonth], 0) = 0 _
    Or Nz([cboWeek], 0) = 0 Then
 
      If Nz([CboYear], 0) = 0 Then MsgBox "Please select the Forecast Year from the" & _
    " drop down menu", 64, "Select Forecast Year"
    If Nz([CboMonth], 0) = 0 Then MsgBox "Please select the Forecast Month from the" & _
    " drop down menu", 64, "Select Forecast Month"
      If Nz([cboWeek], 0) = 0 Then MsgBox "Please select the Forecast Week from the" & _
    " drop down menu", 64, "Select Forecast Week"
 
       Else
 
'If there is no data to report on, stop the report from running and display
'the error message, otherwise open the report in preview mode.
 
On Error GoTo Err_cmdRptSummary
Const conCANCEL_REPORT_OPEN As Integer = 2501
 
   DoCmd.OpenReport "SumReport", acViewPreview
 
Exit_cmdRptSummary:
  Exit Sub
 
Err_cmdRptSummary:
    If Err.Number = 2501 Then     'Report Open was Canceled
        MsgBox "Given the selections there is no data to report on," & _
        " please select different parameters and try again.", vbExclamation, "No Data to Report"
  Else
 
    MsgBox Err.Description, vbExclamation, "Error in Opening Report"
 
    End If
        Resume Exit_cmdRptSummary
            End If
End Sub

I never used that event before. It will sure come in handy!

Thanks to both of you for getting back to me. It is comforting to know I can get some ideas when I feel like I am running out of them. Unfortunately, My 1000 + page Access 2003 book was not doing the trick, though I have benn using it lately to bang my head a few times.... Your solution did the trick.

Take care,

Keith. ;-)