Generic Routine For On no Data and removing error messages
in [Help]
|
From: divventknaa on 25 Jul 2010 15:54 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 ------------------- ----- ---- -- -
From: Ken Snell on 25 Jul 2010 16:52 :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(a)hotmail.com> wrote in message news:H4GdndhHWaj2C9HRnZ2dnUVZ8jMAAAAA(a)giganews.com... > > 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 > ------------------- ----- ---- -- - >
From: James A. Fortune on 26 Jul 2010 15:03 On Jul 25, 3:54 pm, divventknaa <i...(a)hotmail.com> wrote: > 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 > ------------------- ----- ---- -- - 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(a)FortuneJames.com
From: divventknaa on 26 Jul 2010 16:27 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 ------------------- ----- ---- -- -
|
Pages: 1 Prev: On No Data - Suppress Errors And Generic Routine? Next: Pie Chart Size? |