Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    105
    Thanks
    25
    Thanked 2 Times in 2 Posts

    Hiding Blank Data Fields on a Report

    I have a Report that displays information about a Trading position that can be made up made up of at least 1, and up to 4 Orders. Each Trade Record within the database Table has fields for all 4 Orders and the details of each Order.

    At present the Report displays the details of all Orders for a Trade, regardless of when the Orders have been issued or not, as indicated by the presence, or otherwise, of an order number:

    Report - Report Mode - Detail.JPG

    Note that some fields have default values even when there is no Order Number.

    What I wish to achieve is to have Orders that have not been issued NOT displayed in my Report, and the rows of the Report Detail without Order details shrunk. All Order fields in the Report are Textboxes:

    Report - Design Mode - Detail Textboxes.JPG

    I've tried putting the code show below in these events, without success:
    Report.OnCurrent
    Report.OnLoad
    Report.Activate
    Report.OnGotFocus
    Report.OnOpen
    Detail.OnFormat
    Detail.OnPaint


    Code tried:

    Code:
    If IsNull(Me.Entry_Order_D) or Me.Entry_Order_D == "" then
    	Me!Entry_Order_D.Visible = False
    	Me!Trade_Entry_Price_D.Visible = False
    	Me!Entry_Fx_Conversion_Rate_D.visible = False
    Else
    	Me!Entry_Order_D.Visible = True
    	Me!Trade_Entry_Price_D.Visible = True
    	Me!Entry_Fx_Conversion_Rate_D.visible = True
    End If
    Any suggestions on how to achieve this will be appreciated.

    Thanks in anticipation

    Trevor

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    73
    Thanks
    7
    Thanked 2 Times in 2 Posts
    Hi Trevor,

    Is the report based on an Access query? If so, I would put a criteria in the query under the order number field as = not null.

    Regards,
    Maria
    Maria
    Simmo7
    Victoria, Australia

  4. #3
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    105
    Thanks
    25
    Thanked 2 Times in 2 Posts
    Hi Maria,

    Thanks for responding.

    The Report is based on a Query filtered using other criteria.

    However, I cannot use the query filtering approach because then the whole record for a Trade would not be displayed. The underlying Table, and hence the Query, has fields like:

    Trade_ID
    Order_A
    Order_B
    Order_C
    Order_D

    along with other details relevant to each Order for the Trade record (ie table row).

    As I see it, I need a means of controlling whether a Textbox is displayed, or not, by the presence of an Order Number. The will always be an Order A, but Orders B, C and D are optional.

    Cheers

    Trevor

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,820
    Thanks
    185
    Thanked 703 Times in 641 Posts
    Trevor,

    I don't think you can do what you want with your current table structure. If however you normalized your tables, i.e. create a separate table for the orders (in normalization you shouldn't have repeating fields in a table but rather in a separate table linked via a key field). You could then setup a subreport under the trade that would list only the orders that were entered or use grouping and sections (see example).

    Normalization of a database is not always an easy task and achieving full normalization can sometimes be virtually impossible. However, I would suggest you query "Database Normalization" for some useful hints. HTH

    accessrptdesign.JPG

    AccessRpt.JPG

    Note: This is actually a bad example as it only shows the desired type of output (multiple items under a heading). However, this actual report is not generated from a normalized DB... I have one of those but it is complicated and the data is confidential so I can't post it but the concept of the post is valid.
    Last edited by RetiredGeek; 2014-01-24 at 08:43.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. The Following User Says Thank You to RetiredGeek For This Useful Post:

    BygAuldByrd (2014-01-24)

  7. #5
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    105
    Thanks
    25
    Thanked 2 Times in 2 Posts
    Hi RG,

    I suspect you are right because my database is certainly not fully Normalized. The main table in my database has grown like topsy to about 90 fields as I've self taught myself how to use Access. So it looks like it's time for me to get into a major overhaul of my database and its multiplicity of forms and reports and heaps of VBA attached to them. Time for more learning of good database design and manipulation practice.

    One thing I've not worked out is how to quickly and easily print out Table and Query Designs and VBA listings from Access, aside from copy and paste into Word or Notebook. Is there an easier way? With printouts its easier to study and determine the required Normalized structures and changes necessary to my Forms, Reports and VBA Code.

    Thanks again for your always excellent advice.

    Cheers

    Trevor

Posting Permissions

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