MS Access 2013 Report

Posted on 2014-08-06
Last Modified: 2014-08-06
I have a report which is basically a pic-sheet for Customer Orders. In the detail section you have a QtyOrdered, QtyShipped, and a line to X the product off, followed by a Description.

If the description contains a certain value... let's say "Bananas" for instance, I am trying to get JUST that line to be hidden/invisible.

I have this in the ON LOAD event - but it does nothing

Private Sub Report_Load()
If Me.Description.Value = "Bananas" Then
Me.QtyOrdered.Visible = False And Me.QtyOrdered.Visible = False And Me.Description.Visible = False
End If

End Sub

Appreciate any help or suggestions
Question by:dawber39
    LVL 33

    Assisted Solution

    The on load event runs only once when the report/form is first loaded and is used to populate the RecordSource.  Your best solution based on what you requested is to use a query as the RecordSource and in the query use selection criteria that ignores the items you do not want to print.

    If you want to do it in the report itself, you'll need code.   I've never tried to do this, I always use criteria in the query to control what records are selected but you should be able to use the Print event to control this.  So in the Print event, add whatever code you need to identify that this is a record you do not want to print and when you do, cancel the Print event -

    If some condition Then
        Cancel = True
        Exit Sub
    End If
    LVL 12

    Accepted Solution

    You could include this in the recordsource of your report

    ... WHERE Instr(1, [Description], [Enter record exclusion text]) = 0

    If your report is a name of a table or query, change it to

    Select * from <table or query name> WHERE Instr(1, [Description], [Enter record exclusion text]) = 0;

    This will cause a parameter input box to appear when the report runs. Enter "Bananas", and the records with "Bananas" in the description will be excluded.
    LVL 84

    Assisted Solution

    by:Scott McDaniel (Microsoft Access MVP - EE MVE )
    You could also use Conditional Formatting to show/hide a row based on a certain value - but I would agree with Pat and Peter that you would be much better off excluding those items from the Report's data source.

    Author Closing Comment

    Thank you everyone for the help - I did wind up using the record source, and it works fine. Thank you again - you people are awesome

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now