Eliminate Space from Hidden Text Box using CanShrink

[Casey Sanders]

I have looked everywhere and can find little information to get this done. Basically, I am setting up a database for a convertible top manufacturer where there are thousands of product codes. I have everything on the tables and forms pretty much working the way they need to and now I'm trying to set up a report to print a catalog. I group them by make, then model, then sort them by year (displaying all the items available for that make/model within that particular year range). This all looks great. The problem is that altogether there are about 30 different types of materials that each top can be made from, but only 5 or 6 for each make/model. For example, a top for a Ford Mustang may have options A, B, C, D, E...A Chevy Camaro may have options F, G, H, I, J....then something like a Mini Cooper may have options B, D, F, H, I, T.... so I have them all laid out in a row with the prices for that material below (in design view) like this:

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
1 2 1 4 3 5 3 4 1 2 4 5 1 3 2 2 1 2 3 4 1 2 4 2 2 3

I have it set (using CanShrink) so that all of the options that are not available for that make/model do not show, but it leaves the blank space where it would normally be...Needless to say, this is WAY too wide for a single page print and ends up looking something like this for the Mini Cooper example above:

B D F H I T
2 4 5 4 1 4

...and this is what I would like it to look like:

B D F H I T
2 4 5 4 1 4

...eliminating the space between and consolidating to fit on one line on the same page..

***Please see the attachment for an actual screenshot***

I found this article from Microsoft that describes pretty much what I'm trying to do, but it is a generalization and goes into VB coding (which I'm not very educated on):
http://support.microsoft.com/kb/299011



Any help is greatly appreciated!!!!

[Casey Sanders]

I photoshopped this screenshot to show exactly how I'd like it to look....hope this helps:

[Casey Sanders]

...here is another screenshot I took using some dummy values just to show how the data runs off of the page....

[June7]

The article concerns vertical white space, not horizontal. Managing horizontal white space is at best very difficult and at worst impossible. Will be interested if you find a way.

Related/duplicate threads
https://www.accessforums.net/reports...int-31954.html
https://www.accessforums.net/reports...0-a-31936.html

[Casey Sanders]

I was able to put all of the price values into a subreport and place them in the same spot as the images above.... then I included some code to check if the control is null...and if null to set width to 0 and visible to false...it runs great at first using the code below, but once a particular control is set to 0 width and false visibility, it doesn't show up on any records after that...and if refreshed disappears from the first pages as well....so what I need to figure out is how to get the subreport to keep different values (which ones are visible) for each product.... Perhaps there is a way to create a query or something to do this??

...here is the code I'm using to accomplish what I described:

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