[Last Call] Learn how to a build a cloud-first strategyRegister Now


MS Access 2013 Report

Posted on 2014-08-06
Medium Priority
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 40

Assisted Solution

PatHartman earned 500 total points
ID: 40244230
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

pdebaets earned 1000 total points
ID: 40244237
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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40244430
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

ID: 40244861
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

825 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