I have an option group which opens a report dependent on the use of select case within the option
Group.

Occasionally there will be a report where the function being queried will have no issues associated
with it therefore the on No Data will be true.

I have tried several methods, including the OnNoData event in the report, display a msgbox saying
no data, and then returning to the main form for a different selection.

I have tried setting DoCmd.SetWarnings False, but cannot get rid of the error message, (2501) so
the programme is halting.

I saw one method using a public function but not sure how to call it. That would be the bbest way
I presume, as you could set up the Public Sub and then just put the call function in every report
with a generic message such as "No Data In Currently Selected Report"

The Report is called "AllDeficienciesReport"

Thanks

JBN



--
--------------------------------- --- -- -
Posted with NewsLeecher v3.9 Final
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -

:Trap the 2501 error in the form's procedure that opens the report:

Private Sub YourProcedureName_Something()
On Error GoTo ErrHandle
DoCmd.OpenReport "AllDeficienciesReport", etc.
Exit Sub
ErrHandle:
If Err.Number <> 0 And Err.Number <> 2501 Then _
MsgBox "Error " & Err.Number & " - " & Err.Description
End Sub

--

Ken Snell
http://www.accessmvp.com/KDSnell/


"divventknaa" <imat (AT) hotmail (DOT) com> wrote

On Jul 25, 3:54*pm, divventknaa <i... (AT) hotmail (DOT) com> wrote:
As an alternate to Ken's solution, it is my philosophy that the "No
Data" condition should be detected before trying to open the report.
As soon as the option is determined, you can determine whether the
report has data or not. I created a couple of auxiliary functions for
that purpose:

'Begin module code
Public Function DNoRecords(strSQL As String) As Boolean
Dim MyDB As Database
Dim CountRS As Recordset

DNoRecords = True
Set MyDB = CurrentDb
Set CountRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
If CountRS.RecordCount > 0 Then
DNoRecords = False
End If
CountRS.Close
Set CountRS = Nothing
Set MyDB = Nothing
End Function

Public Function DHasRecords(strSQL As String) As Boolean
Dim MyDB As Database
Dim CountRS As Recordset

DHasRecords = False
Set MyDB = CurrentDb
Set CountRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
If CountRS.RecordCount > 0 Then
DHasRecords = True
End If
CountRS.Close
Set CountRS = Nothing
Set MyDB = Nothing
End Function
'End module code

Example (code behind form):

If IsNull(Forms!MyForm!optAccountType.Value) Then
MsgBox("The AccountType must be selected. Please try again.")
Exit Sub
End If
strSQL = "SELECT * FROM MyTable WHERE AccountType = " & Forms!MyForm!
optAccountType.Value & " AND Year(EntryDate) = " & Forms!MyForm!
cbxEntryYear.Value & ";"
If DHasRecords(strSQL) Then
DoCmd.OpenReport "AllDeficienciesReport", acViewPreview, ,
"AccountType = " & Forms!MyForm!optAccountType.Value & " AND
Year(EntryDate) = " & Forms!MyForm!cbxEntryYear.Value
Else
MsgBox("There are no records of AccountType = " & Forms!MyForm!
optAccountType.Value & " for entry year " & Forms!MyForm!
cbxEntryYear.Value & ".")
End If

The auxiliary functions run very quickly if the fields in the WHERE
condition are indexed. You can still provide an error handler to
catch, say, the only record returned being deleted after the auxiliary
function is run, yet immediately before opening the report, but I
expect the conditions leading to that error being trapped are much,
much rarer than the "No Data" condition.

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com

Thanks for replies,

I have cleaned up the code and removed redundant bits, and used a variation of the method below,
which gives a discrete error message on 2501, and the inbuilt error message if <>0 and <> 2501

Works fine now.




'Try changing acPreview to acViewPreview. Both should work but the standard is
'the latter.

'In the current sub, I would add some error handling.

'Private Sub DeficiencySelect_AfterUpdate()
'On Error GoTo Proc_Error

'Select Case Me.DeficiencySelect
...
'End Select

'Exit Sub

'Proc_Error:
' If Err.Number = 2501 then
' 'Skip it
' Else
' MsgBox Err.Number & ": " & Err.Description
' End If
'End Sub



--
--------------------------------- --- -- -
Posted with NewsLeecher v3.9 Final
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -