Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2009
    Posts
    223

    Red face Unanswered: Printing a Report that has no data

    Yes, I want to print a Report even if it has no data.

    I have researched the internet and I think I have found the answer but I need some help with the syntax. The answer is:-


    in each report's Design view, add a Label control, i'll call it "Label6".
    you'll have to type something in it so it will "stick" - it doesn't matter
    what. in the Properties box, set the label's Visible property to No.
    in each report's NoData event procedure, remove the "Cancel = True" code.
    replace it with the following, as

    Dim ctrl As Control

    For Each ctrl In Me.Controls
    If ctrl.Name = "Label6" Then
    ctrl.Caption = Me.Caption & " has no data."
    ctrl.Visible = True
    Else
    ctrl.Visible = False
    End If
    Next ctrl

    if there's no data, all controls in the report will be hidden, and the
    Label6 control will be unhidden. the report will print (or preview)
    normally.


    I copy and paste the above but get the following error message:-

    "The expression you entered contains invalid syntax. You may have entered an operand without an operator."

    Would anybody be able to help me?

    Thanks,

    Paul

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In which procedure (sub or function) did you paste the code (it should normally be in the Report_NoData event handler) and what's the line that causes the error?
    Have a nice day!

  3. #3
    Join Date
    Nov 2009
    Posts
    223
    checking.........

  4. #4
    Join Date
    Nov 2009
    Posts
    223
    There must have been a technical malfunction...or I got it wrong, coz the error messge is no longer there?

    Now the report prints but without the Label text.

    Oops, now the report doesn;t print - what did I do??

    Still trying.....

  5. #5
    Join Date
    Nov 2009
    Posts
    223
    Now the report prints again but still without the label.

    is the coding correct for showing the label??

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Sinndho View Post
    In which procedure (sub or function) did you paste the code (it should normally be in the Report_NoData event handler) and what's the line that causes the error?
    Without knowing what the code precisely is and where it is located, it's hard to help you.
    Have a nice day!

  7. #7
    Join Date
    Nov 2009
    Posts
    223
    I am still trying with this report message. Although I am beginning to think it is more trouble than it is worth??

    I put the following code into the Report/OnNoData Event handler.


    Dim ctrl As Control

    For Each ctrl In Me.Controls
    If ctrl.Name = "LabelNoRecords" Then
    ctrl.Caption = Me.Caption & " has no data."
    ctrl.Visible = True
    Else
    ctrl.Visible = False
    End If
    Next ctrl

    Now I have 2 problems.

    1. The Label (which I renamed LabelNoData) does not show when ther is no records returned.

    2. I have TextBox29 in the Page Header which I need to keep/show. But this now says "#Name?.
    The code in the TextBox29 is
    ="from " & Format([Forms]![Report 1 Form]![Date From],"Medium Date") & " to " & Format([Forms]![Report 1 Form]![Date To],"Medium Date")

    Should I continue??

  8. #8
    Join Date
    Nov 2009
    Posts
    223
    Ooops, I realised why that the #Name? error is because I didn;t have the corresponding Form open at the time. Sorry about that.

    I swear I have not changed anything since my last post, but I can get the Report (without the visible Label) but when I try to run a Report which will not return any records, I get the following error message:-

    Can't find the macro for 'Dim ctrl As Control For Each ctrl In Me

    ???

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. According to the documentation of Access 2003 (MSDN) : "The NoData event occurs after Microsoft Access formats a report for printing that has no data (the report is bound to an empty recordset)" which means that it is probably too late to change the contents or the visible property of a control (the report is already formatted). The code should probably be executed on the Open event (i.e. before the document is formatted).

    2. From the moment you know the name of the control, you don't need to loop in the controls collection to retrieve a reference to it:
    Code:
    Me.Controls("LabelNoRecords").Visible ...
    or
    Code:
    Me.("LabelNoRecords").Visible ...
    or
    Code:
    Me.LabelNoRecords.Visible ...
    or
    Code:
    Set ctl = Me.LabelNoRecords
    ctl.Visible ...
    will work too.

    3. About TextBox29, I would use a label (it does not need to be a textbox as it is not bound to any field of the recordset) and I would assign it's caption property, also in the Open event of the report:
    Code:
    Private Sub Report_Open(Cancel As Integer)
    
        ' Label29 formerly was Text29
        '
        Me.Label29.Caption = "from " & Format(Forms![Report 1 Form]![Date From],"Medium Date") & " to " & Format(Forms![Report 1 Form]![Date To],"Medium Date")
        
    End Sub
    Have a nice day!

  10. #10
    Join Date
    Nov 2009
    Posts
    223
    Sinndho,

    Thanks for the explanation(s).

    I think I will be able to resolve the problem after your help.

    Cheers,

    Red Devil

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  12. #12
    Join Date
    Nov 2009
    Posts
    223
    Greetings,

    I have played around with this but still have the following questions:-

    1. Where does the Me.LabelNoRecords.Visible coding go??

    2. I cannot get the Label to show the "To" and "From" dates?? I have put the following coding in the Label/Caption field:-

    = "from " & Format(Forms![Report 1 Form]![Date From],"Medium Date") & " to " & Format(Forms![Report 1 Form]![Date To],"Medium Date")


    In fact, And I have tried putting different combinations of this coding:-

    Me.Label29.Caption = "from " & Format(Forms![Report 1 Form]![Date From],"Medium Date") & " to " & Format(Forms![Report 1 Form]![Date To],"Medium Date")

    but all the Label shows is the "above coding" and not the "Date" which I want? However, a Text Box does calculate this correctly?

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As stated before:
    Quote Originally Posted by Sinndho View Post
    ... The code should probably be executed on the Open event (i.e. before the document is formatted).
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •