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

Comments are subject to moderation before publishing.

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Class Module Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array Queries msaccess reports External Links msaccess tips Accesstips Downloads Objects Property Menus and Toolbars Controls Event MsaccessLinks Process Controls Art Work Collection Object WithEvents msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Command Button Data Dictionary Object Emails and Alerts Form Query RaiseEvent Report Calculation Custom Wizards DOS Commands Data Type ms-access functions msaccess functions msaccess graphs msaccess reporttricks Item Object Reference msaccessprocess security advanced Access Security Add Custom Functions Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting 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 Remove Rich Text Sequence SetFocus Summary Tab-Page Tables Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

WithEvents and All Form Control Types

Introduction So far we have worked extensively with only one type of Control: the TextBoxes on Form, enabled with Events to Raise and captu...

Labels

Blog Archive

Recent Posts