Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

No Data and Report Error

Introduction.

Report Source Query or Table can end up with no output records. In that case, some of the controls with the formula in 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 preparing. The controls to the right of the word TOTAL are Sub-Totals and Totals, 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 someone 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 show Zero Values, and the Report period is printed correctly.

I have made a 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 are 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.

The Report Period (DateFrom and DateTo) is 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.


Few Changes in the Report

Created two Text Controls (with the name From and To respectively) at the Report Header Section to the right of the Control name STAFFNAME to load the DataFrom and DateTo Values from the Report Parameter Table with DLookup() Function:

=DLookUp("DateFrom","Report_Param")

Second Control has 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 the cosmetic changes required in the Report.

Temporary Table for Report.

The major change is to create a temporary table with a single blank record, with the same structure as 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 the value on the Report then type *** NIL REPORT *** in that field. Keep all other fields Empty.

How the Trick Works

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.

Few Lines of VBA Code

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

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

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