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

[Casey Sanders]

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:

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

[NTC]

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).

[Casey Sanders]

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

[Casey Sanders]

like this....

[Casey Sanders]

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.)

[June7]

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