Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

No Data and Report Error

Report Source Query or Table can end up with no output records. In that case some of the controls with formula on the Report will show #Error on them. An image of a sample report is given below:

The #Error at the right top is a control that has a formula for showing the Period for which the Report is currently prepared for. The controls to the right of the word TOTAL are Sub-Totals and Totals of detail line values and all of them ended up with Errors because the underlying Query of the Report doesn't have any record to show for the current period selected by the User.

Even though this is not a serious issue if it became necessary to show this Report to some one as a NIL REPORT or printed and kept for future references leaving the controls with errors this way is not in good taste on the part of the Database Developer.

The modified version of the above Report is given below with a Comment, Summary Controls showing Zero Values and Report period printed correctly.

I have made few modifications to the Report Design to add a hidden label at the footer of the Report with the Caption: *** Congratulations *** Nothing Pending to show up when there is no output Records for the Report. The Visible Property of the label is set to No manually. On the Detail Section Under the Description Column shows *** NIL REPORT ***. The period for which the Report is prepared is also shown to the right, above the Detail Section headings.

Report Period (DateFrom and DateTo) are normally entered into a Parameter Table and joined with the Report Source Table in a Query to use them for criteria and for displaying on the Report.

I have created two Text Controls (with the name Frm and To respectively) at the Report Header Section to the right of the Control name STAFFNAME to load the DateFrom and DateTo Values from the Report Parameter Table with DLookup() Function:

=DLookUp("DateFrom","Report_Param")

Second Control have the expression to read DateTo from the Report_Param Table and both values are used in the expression (="Period : " & [frm] & " To " & [To]) to format the values to show the output as in the second image given above.

These are all cosmetic changes for the Report. The major change is to create a temporary table with a single blank record, with the same structure of the Source Table or Query that is attached to the Report. If your Report is using a Table as Report Source Data then make a copy of the structure of the Table and add a tmp_ prefix to the table name like tmp_myReport. If it is a Query then create a Make-Table Query using the Report Source Query and create a temporary table. Add a blank record in the temporary table. If your Report Table has a Text Field that is displaying value on the Report then type *** NIL REPORT *** in that field. Keep all other fields Empty.

The trick is, when the Report is open by the User we will check whether the original Report Source Table or Query have any record in it or not. If not swap the Temporary Table with the Report Source Table or Query. The hidden Label's Visible Property will be set to Yes to display the comment *** CONGRATULATIONS *** NOTHING PENDING. Since the temporary table has one blank record in it already, the Summary Controls will not end up with errors.

We need a few lines of VBA Code in the Report_Open() Event Procedure to check and swap the Report Source Table or Query.

Private Sub Report_Open(Cancel As Integer)
Dim ii = DCount("*", "myReport")
If i = 0 Then
   Me.RecordSource = "tmp_MyReport"
   Me.lblMsg.Visible = True
End If
End Sub

Copy the above lines of Code in the Report's VBA Module and make changes to insert the correct Table/Query and tmp_myReport names.

Share:

2 comments:

  1. Private Sub Report_Open(Cancel As Integer)
    Dim ii = DCount("*", "ExpenseLog by Truck Licence No")
    If i = 0 Then
    Me.RecordSource = "tmp_ExpenseLog by Truck Licence No"
    Me.TE.Visible = True
    End If
    End Sub

    This is my code and I get an error as Syntax error. First Line is highlighted yellow. and second line is shown as syntax error. Help please

    ReplyDelete
  2. Hi,
    Split the second line as shown below:


    Dim i as Integer
    i = DCount("*","ExpenseLog by Truck Licence No")


    Check the mistake in Dim ii and you are testing If i = 0 then

    Regards,

    ReplyDelete

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads

blog.feedspot.com Microsoft Access Blogs

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Accesstips Menus and Toolbars MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery Array List Boxes Command Buttons Emails and Alerts Property Query Combo Boxes Custom Wizards DOS Commands Data Objects VBA ms-access functions msaccess functions msaccess graphs msaccess reporttricks Calculation Class Module Data Type msaccessprocess security advanced Access Security Custom Functions Macros Menus Object Reference Report Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Expression Field Type Fields Form Form Instances Join Methods Microsoft Numbering System Records Security Split SubForm Table Utility Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Controls Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Recordset Rich Text Sequence SetFocus Summary Tab-Page Tables Time Difference Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

Base Class and Derived Object Variants

Last week we have tried an example as how to pass a Base Class Object, through the Set Property Procedure,  to become part of the Object in...

Labels

Blog Archive

Recent Posts