Results 1 to 6 of 6
  1. #1
    Casey Sanders is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    37

    Where to paste code for subreport?? ...Format, Paint, Print, or Retreat??

    Ok, I have a main form with a subform. The main form has product info (Product Code, Description, Install Time, etc.). The subform has pricing for the different materials available for that particular product. There are 20 total available types of material, and only 18 will fit horizontally onto the form/subform... Luckily, each product only has about 5 or 6 available for it, so what I'm trying to do is eliminate the ones that are not available for that product...see my post here for screenshots: https://www.accessforums.net/reports...ink-31833.html

    I have figured out the code to use that basically says: if the control is null, then make the width = 0 and visible = false...this almost accomplishes what I'm trying to do...however, it eliminates it from ALL records and not just the one it is not available for...so where options A, B & C are available for Product 1, but only B & C are available for product 2, it will no longer show option A for Product 1 (or any other products that have that availability)...

    I played around with placing the code in Detail|Format and Detail|Print....when putting it under Detail|Paint, it actually changed the control settings in the subform and I had to re-open it as a form and make everything visible again. It seems to do exactly what I want it to do putting it under Detail|Print if I hit print preview immediately...if it runs for a minute before hitting print preview, then they all show up blank...

    Does anyone have any suggestions for where to place the code or something I may need to add so that it zeroes out the value and re-runs the code for each product??

    Here is the full code I'm using:

    Code:
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    
    
    If IsNull(Me.sbrpt_Product_Prices.Report.PP_SS_B_W) Then
    Me.sbrpt_Product_Prices.Report.PP_SS_B_W.Width = 0
    Me.sbrpt_Product_Prices.Report.PP_SS_B_W.Visible = False
    Me.sbrpt_Product_Prices.Report.lbl_PP_SS_B_W.Width = 0
    Me.sbrpt_Product_Prices.Report.lbl_PP_SS_B_W.Visible = False
    End If
    
    
    If IsNull(Me.sbrpt_Product_Prices.Report.PP_SS_CLR) Then
    Me.sbrpt_Product_Prices.Report.PP_SS_CLR.Width = 0
    Me.sbrpt_Product_Prices.Report.PP_SS_CLR.Visible = False
    Me.sbrpt_Product_Prices.Report.lbl_PP_SS_CLR.Width = 0
    Me.sbrpt_Product_Prices.Report.lbl_PP_SS_CLR.Visible = False
    End If
    
    
    If IsNull(Me.sbrpt_Product_Prices.Report.SS_SP_CLR) Then
    Me.sbrpt_Product_Prices.Report.SS_SP_CLR.Width = 0
    Me.sbrpt_Product_Prices.Report.SS_SP_CLR.Visible = False
    Me.sbrpt_Product_Prices.Report.lbl_SS_SP_CLR.Width = 0
    Me.sbrpt_Product_Prices.Report.lbl_SS_SP_CLR.Visible = False
    End If
    
    
    If IsNull(Me.sbrpt_Product_Prices.Report.PP_SP_CLR) Then
    Me.sbrpt_Product_Prices.Report.PP_SP_CLR.Width = 0
    Me.sbrpt_Product_Prices.Report.PP_SP_CLR.Visible = False
    Me.sbrpt_Product_Prices.Report.lbl_PP_SP_CLR.Width = 0
    Me.sbrpt_Product_Prices.Report.lbl_PP_SP_CLR.Visible = False
    End If
    
    
    If IsNull(Me.sbrpt_Product_Prices.Report.SF_SR) Then
    Me.sbrpt_Product_Prices.Report.SF_SR.Width = 0
    Me.sbrpt_Product_Prices.Report.SF_SR.Visible = False
    Me.sbrpt_Product_Prices.Report.lbl_SF_SR.Width = 0
    Me.sbrpt_Product_Prices.Report.lbl_SF_SR.Visible = False
    End If
    
    
    If IsNull(Me.sbrpt_Product_Prices.Report.ET_ES) Then
    Me.sbrpt_Product_Prices.Report.ET_ES.Width = 0
    Me.sbrpt_Product_Prices.Report.ET_ES.Visible = False
    Me.sbrpt_Product_Prices.Report.lbl_ET_ES.Width = 0
    Me.sbrpt_Product_Prices.Report.lbl_ET_ES.Visible = False
    End If
    
    
    If IsNull(Me.sbrpt_Product_Prices.Report.SG_TW_RP) Then
    Me.sbrpt_Product_Prices.Report.SG_TW_RP.Width = 0
    Me.sbrpt_Product_Prices.Report.SG_TW_RP.Visible = False
    Me.sbrpt_Product_Prices.Report.lbl_SG_TW_RP.Width = 0
    Me.sbrpt_Product_Prices.Report.lbl_SG_TW_RP.Visible = False
    End If
    
    
    If IsNull(Me.sbrpt_Product_Prices.Report.SG) Then
    Me.sbrpt_Product_Prices.Report.SG.Width = 0
    Me.sbrpt_Product_Prices.Report.SG.Visible = False
    Me.sbrpt_Product_Prices.Report.lbl_SG.Width = 0
    Me.sbrpt_Product_Prices.Report.lbl_SG.Visible = False
    End If
    
    
    If IsNull(Me.sbrpt_Product_Prices.Report.GR) Then
    Me.sbrpt_Product_Prices.Report.GR.Width = 0
    Me.sbrpt_Product_Prices.Report.GR.Visible = False
    Me.sbrpt_Product_Prices.Report.lbl_GR.Width = 0
    Me.sbrpt_Product_Prices.Report.lbl_GR.Visible = False
    End If
    
    
    If IsNull(Me.sbrpt_Product_Prices.Report.RO) Then
    Me.sbrpt_Product_Prices.Report.RO.Width = 0
    Me.sbrpt_Product_Prices.Report.RO.Visible = False
    Me.sbrpt_Product_Prices.Report.lbl_RO.Width = 0
    Me.sbrpt_Product_Prices.Report.lbl_RO.Visible = False
    End If
    
    
    If IsNull(Me.sbrpt_Product_Prices.Report.A5) Then
    Me.sbrpt_Product_Prices.Report.A5.Width = 0
    Me.sbrpt_Product_Prices.Report.A5.Visible = False
    Me.sbrpt_Product_Prices.Report.lbl_A5.Width = 0
    Me.sbrpt_Product_Prices.Report.lbl_A5.Visible = False
    End If
    
    
    If IsNull(Me.sbrpt_Product_Prices.Report.A5A) Then
    Me.sbrpt_Product_Prices.Report.A5A.Width = 0
    Me.sbrpt_Product_Prices.Report.A5A.Visible = False
    Me.sbrpt_Product_Prices.Report.lbl_A5A.Width = 0
    Me.sbrpt_Product_Prices.Report.lbl_A5A.Visible = False
    End If
    
    
    If IsNull(Me.sbrpt_Product_Prices.Report.CO_CG) Then
    Me.sbrpt_Product_Prices.Report.CO_CG.Width = 0
    Me.sbrpt_Product_Prices.Report.CO_CG.Visible = False
    Me.sbrpt_Product_Prices.Report.lbl_CO_CG.Width = 0
    Me.sbrpt_Product_Prices.Report.lbl_CO_CG.Visible = False
    End If
    
    
    If IsNull(Me.sbrpt_Product_Prices.Report.EV_BS) Then
    Me.sbrpt_Product_Prices.Report.EV_BS.Width = 0
    Me.sbrpt_Product_Prices.Report.EV_BS.Visible = False
    Me.sbrpt_Product_Prices.Report.lbl_EV_BS.Width = 0
    Me.sbrpt_Product_Prices.Report.lbl_EV_BS.Visible = False
    End If
    
    
    If IsNull(Me.sbrpt_Product_Prices.Report.CAB) Then
    Me.sbrpt_Product_Prices.Report.CAB.Width = 0
    Me.sbrpt_Product_Prices.Report.CAB.Visible = False
    Me.sbrpt_Product_Prices.Report.lbl_CAB.Width = 0
    Me.sbrpt_Product_Prices.Report.lbl_CAB.Visible = False
    End If
    
    
    If IsNull(Me.sbrpt_Product_Prices.Report.CR_B_W) Then
    Me.sbrpt_Product_Prices.Report.CR_B_W.Width = 0
    Me.sbrpt_Product_Prices.Report.CR_B_W.Visible = False
    Me.sbrpt_Product_Prices.Report.lbl_CR_B_W.Width = 0
    Me.sbrpt_Product_Prices.Report.lbl_CR_B_W.Visible = False
    End If
    
    
    If IsNull(Me.sbrpt_Product_Prices.Report.TF_JX_LM) Then
    Me.sbrpt_Product_Prices.Report.TF_JX_LM.Width = 0
    Me.sbrpt_Product_Prices.Report.TF_JX_LM.Visible = False
    Me.sbrpt_Product_Prices.Report.lbl_TF_JX_LM.Width = 0
    Me.sbrpt_Product_Prices.Report.lbl_TF_JX_LM.Visible = False
    End If
    
    
    If IsNull(Me.sbrpt_Product_Prices.Report.MC) Then
    Me.sbrpt_Product_Prices.Report.MC.Width = 0
    Me.sbrpt_Product_Prices.Report.MC.Visible = False
    Me.sbrpt_Product_Prices.Report.lbl_MC.Width = 0
    Me.sbrpt_Product_Prices.Report.lbl_MC.Visible = False
    End If
    
    
    If IsNull(Me.sbrpt_Product_Prices.Report.TWV) Then
    Me.sbrpt_Product_Prices.Report.TWV.Width = 0
    Me.sbrpt_Product_Prices.Report.TWV.Visible = False
    Me.sbrpt_Product_Prices.Report.lbl_TWV.Width = 0
    Me.sbrpt_Product_Prices.Report.lbl_TWV.Visible = False
    End If
    
    
    If IsNull(Me.sbrpt_Product_Prices.Report.LVNT_BK) Then
    Me.sbrpt_Product_Prices.Report.LVNT_BK.Width = 0
    Me.sbrpt_Product_Prices.Report.LVNT_BK.Visible = False
    Me.sbrpt_Product_Prices.Report.lbl_LVNT_BK.Width = 0
    Me.sbrpt_Product_Prices.Report.lbl_LVNT_BK.Visible = False
    End If
    
    
    If IsNull(Me.sbrpt_Product_Prices.Report.MISC) Then
    Me.sbrpt_Product_Prices.Report.MISC.Width = 0
    Me.sbrpt_Product_Prices.Report.MISC.Visible = False
    Me.sbrpt_Product_Prices.Report.lbl_MISC.Width = 0
    Me.sbrpt_Product_Prices.Report.lbl_MISC.Visible = False
    End If
    
    
    End Sub


  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Posts
    2,392
    Well the first comment as a full time db developer is that you are having to do all this coding because of the intent to put your main report fields side-by-side in fundamental conflict with how data is stored in a database that is correctly designed. It should be vertical i.e.:
    PP_SS_B_W
    - subreport data
    PP_SS_CLR
    - subreport data
    SS_SP_CLR
    - subreport data
    ETC.
    ETC.

    Having said that - in your code logic - in the most general terms: in a report once you modify a field - it remains modified for the remainder of the report - - meaning that once a field goes to 0 width (in example) you must explicitly change it back to normal width for the next set of data..... You should be using the OnFormat event - and I would explore the HasData method (look that up in the VBA help area).

  3. #3
    Casey Sanders is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    37
    I saw this on another post where you do something like control.visible = control.hasdata ...something like that, but simply making the field invisible does not remove the white space

    ...and the layout is for the company I work for...its a product catalog for convertible auto tops...and I have to fit a certain format in regards to how they do pricing...not just my company, but the industry standard (so if you're right, they all have it wrong, lol)....not to mention running the pricing vertical would basically equal one product per page and there are thousands and thousands of products, so I'm not quite sure you understand the specifics of this project....for one, the way you put the data:

    PP_SS_B_W
    - subreport data
    PP_SS_CLR
    - subreport data
    SS_SP_CLR
    - subreport data
    ETC.

    would be to say: Pinpoint Sailcloth- B&W is available on these tops:
    then have 1000 entries here
    then: Pinpoint Sailcloth- Color is available on these tops:
    then have 1000 more entries, mostly duplicate

    ....where the proper way we want the data displayed is like this:

    Chevrolet
    Camaro
    1990-2000
    **Has A,B,C options available, listing the prices of each option
    Corvette
    1990-2000
    **Has X,Y,Z options available, listing the prices of each (this is the subform data)
    ...ETC...

    .... I just started here a couple months ago so I'm in no position to change the industry standard AND keep my job...lol

  4. #4
    Casey Sanders is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    37
    like this....
    Attached Thumbnails Attached Thumbnails db_prices_spacing_fixed.jpg  

  5. #5
    Casey Sanders is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    37
    Plus the pricing will only be available in certain catalogs and needs to be able to be removed when needed, so essentially, they are not "the main report fields", but more like an addition/option to the main product data (which would be the product code, description, make/model designations, install time, bow height, whether it has EWP or SGR (which are checkboxes), etc.)

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,623
    Because the number of fields are different for each group, your only alternative might be VBA code to 'pivot' the records to display horizontally. This is something I do. Instead of label controls as column headers, would be textboxes bound to fields. Example from your image:
    Model Years Code Label1 Label2 Label3 ... Label20 Data1 Data2 Data3 ... Data20
    ? 1970-1971 WC 507 PP/SS CLR SF/Sr ST TW/RP 158 154 225
    Mini 2004-2008 WH 482 SF/SR SG TW/RP 365 410
    Smart 2004-2008 CO 483 SF/SR 88

    One approach involves a temp table. The temp table is actually a permanent table but data resides in it only for the duration of process. Example at http://forums.aspfree.com/microsoft-...ry-322123.html

    Another approach is to have a field that assigns a sequence number to each data group. Then build CROSSTABS that set sequence ID as the header. Review https://www.accessforums.net/queries...rts-31836.html

    Also, you might find this of interest https://www.accessforums.net/reports...lly-31912.html
    Last edited by June7; 01-30-2013 at 12:52 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. not print blank pages when a subreport is empty
    By fabiobarreto10 in forum Reports
    Replies: 3
    Last Post: 04-28-2012, 10:01 AM
  2. Copy and Paste Format From Excel (attached)
    By KrenzyRyan in forum Import/Export Data
    Replies: 2
    Last Post: 01-02-2012, 05:56 PM
  3. Code issue on Format of label
    By Gavroche in forum Reports
    Replies: 2
    Last Post: 09-14-2011, 06:19 AM
  4. Replies: 1
    Last Post: 01-10-2011, 01:08 PM
  5. code to print just first page of a report
    By tonyrice in forum Reports
    Replies: 1
    Last Post: 02-14-2007, 03:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums