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