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:
[code=text]
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.
[/code]

Here is my code:
[code=vb]
Private Sub cmdRptSummary_C lick()
'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.OpenR ecordset("QryFo recastSummary")

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.OpenRepor t "SumReport" , acViewPreview

End If
End If
End Sub
[/code]

The error focus is on the following line:
[Code=vb]
Set rst = CurrentDb.OpenR ecordset("QryFo recastSummary")
[/Code]

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
[code=vb]
Dim RS As DAO.Recordset
Set RS = CurrentDb.OpenR ecordset("Query 2")
If RS.RecordCount = 0 Then
MsgBox ("Empty")
Else
MsgBox ("Not Empty: " & RS.RecordCount)
End If
RS.Close
[/code]

[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:
[code=text]
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.
[/code]

Here is my code:
[code=vb]
Private Sub cmdRptSummary_C lick()
'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.OpenR ecordset("QryFo recastSummary")

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.OpenRepor t "SumReport" , acViewPreview

End If
End If
End Sub
[/code]

The error focus is on the following line:
[Code=vb]
Set rst = CurrentDb.OpenR ecordset("QryFo recastSummary")
[/Code]

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:
   [CODE=vb]
   Private Sub Report_NoData(C ancel As Integer)
   Cancel = True
   End Sub[/CODE]
2. In the Click() Event of your Command Button:
   [CODE=vb]
   Private Sub cmdReportSummar y_Click()
   On Error GoTo Err_cmdRptSumma ry
   Const conCANCEL_REPOR T_OPEN As Integer = 2501
   
   DoCmd.OpenRepor t "SumReport" , acViewPreview
   
   Exit_cmdRptSumm ary:
   Exit Sub
   
   Err_cmdRptSumma ry:
   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_cmdRptSumm ary
   End Sub[/CODE]
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:

[code=vb]Private Sub cmdRptSummary_C lick()
'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_cmdRptSumma ry
Const conCANCEL_REPOR T_OPEN As Integer = 2501

DoCmd.OpenRepor t "SumReport" , acViewPreview

Exit_cmdRptSumm ary:
Exit Sub

Err_cmdRptSumma ry:
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_cmdRptSumm ary
End If
End Sub[/code]

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. ;-)