If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Make a Control Dissapear with Conditional Formatting?
I have a report with 5 bound text boxes on it. They are [PartNumber],
[NumberOps], [Op1Weight], [Op2Weight] and [Op3Weight]. My user wants a report that shows the PartNumber and only the OpWeight that corresponds to the NumberOps. So, if the NumberOps is 2, only want OP2Weight displayed. I created 3 control, one for each OpWeight. For each respective one, I created a related conditional format. i.e. Op1 weight is black text on a white background UNLESS the field [NumberOps] is not equal to 1. (Then is is white Text on White background.) Op2 weight is black text on a white background UNLESS the field [NumberOps] is not equal to 2. (Then is is white Text on White background.) Op3 weight is black text on a white background UNLESS the field [NumberOps] is not equal to 3. (Then is is white Text on White background.) This works good, except that I cannot place the fields in the same location on the report, as they cover each other up. So, after I created the conditional format, I changed the background style for each of these controls to Transparent. This sort of works, except that the data in these fields is blurry, looks weird. Obviously this isn't the solution. Does anyone have any better suggestions? |
#2
|
|||
|
|||
Make a Control Dissapear with Conditional Formatting?
One method
Add a text box control Name: TextShowThis Control Source: = Choose([NumberOps],[Op1Weight],[Op2Weight],[Op3Weight]) Set the visible property of the three weight controls to No. OR Use the format event of the section to populate a single control with the correct value. The control should have a blank control source. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) Me.TextShowThis = Choose([NumberOps],[Op1Weight],[Op2Weight],[Op3Weight]) End Sub A problem with Choose is that NumberOps MUST have a number value. It cannot be null. You can handle that in the above expressions by using the NZ function Choose(Nz([NumberOps],0),[Op1Weight],[Op2Weight],[Op3Weight]) If NumberOps is not a number but is a text value containing a number character then you will need to convert the text to a number of you can use Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) Select Case NumberOps Case 1 ' Or "1" if text value Me.TextShowThis = Me.Op1Weight Case 2 Me.TextShowThis = Me.Op2Weight Case 3 Me.TextShowThis = Me.Op3Weight Case Else Me.TextShowThis = null End Select End Sub John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County tkosel wrote: I have a report with 5 bound text boxes on it. They are [PartNumber], [NumberOps], [Op1Weight], [Op2Weight] and [Op3Weight]. My user wants a report that shows the PartNumber and only the OpWeight that corresponds to the NumberOps. So, if the NumberOps is 2, only want OP2Weight displayed. I created 3 control, one for each OpWeight. For each respective one, I created a related conditional format. i.e. Op1 weight is black text on a white background UNLESS the field [NumberOps] is not equal to 1. (Then is is white Text on White background.) Op2 weight is black text on a white background UNLESS the field [NumberOps] is not equal to 2. (Then is is white Text on White background.) Op3 weight is black text on a white background UNLESS the field [NumberOps] is not equal to 3. (Then is is white Text on White background.) This works good, except that I cannot place the fields in the same location on the report, as they cover each other up. So, after I created the conditional format, I changed the background style for each of these controls to Transparent. This sort of works, except that the data in these fields is blurry, looks weird. Obviously this isn't the solution. Does anyone have any better suggestions? |
#3
|
|||
|
|||
Make a Control Dissapear with Conditional Formatting?
Mr. Spencer,
Thanks for your suggestions. I have never even heard of the "Choose" control source parameter. I cannot find any help about it in the Access Help. I would like to understand how it works. Can you refer me to a source for help? I did use the OnPrint event to hide controls and that seemed to work as well. See any problems with this code on the OnPrint Event? Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer) If Me.NumberOps = 1 Then Me.LastOfOP1_FinishedPartWeight.Visible = True Me.LastOfOP2_FinishedPartWeight.Visible = False Me.LastOfOP3_FinishedPartWeight.Visible = False ElseIf Me.NumberOps = 2 Then Me.LastOfOP1_FinishedPartWeight.Visible = False Me.LastOfOP2_FinishedPartWeight.Visible = True Me.LastOfOP3_FinishedPartWeight.Visible = False ElseIf Me.NumberOps = 3 Then Me.LastOfOP1_FinishedPartWeight.Visible = False Me.LastOfOP2_FinishedPartWeight.Visible = False Me.LastOfOP3_FinishedPartWeight.Visible = True End If End Sub "John Spencer" wrote: One method Add a text box control Name: TextShowThis Control Source: = Choose([NumberOps],[Op1Weight],[Op2Weight],[Op3Weight]) Set the visible property of the three weight controls to No. OR Use the format event of the section to populate a single control with the correct value. The control should have a blank control source. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) Me.TextShowThis = Choose([NumberOps],[Op1Weight],[Op2Weight],[Op3Weight]) End Sub A problem with Choose is that NumberOps MUST have a number value. It cannot be null. You can handle that in the above expressions by using the NZ function Choose(Nz([NumberOps],0),[Op1Weight],[Op2Weight],[Op3Weight]) If NumberOps is not a number but is a text value containing a number character then you will need to convert the text to a number of you can use Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) Select Case NumberOps Case 1 ' Or "1" if text value Me.TextShowThis = Me.Op1Weight Case 2 Me.TextShowThis = Me.Op2Weight Case 3 Me.TextShowThis = Me.Op3Weight Case Else Me.TextShowThis = null End Select End Sub John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County tkosel wrote: I have a report with 5 bound text boxes on it. They are [PartNumber], [NumberOps], [Op1Weight], [Op2Weight] and [Op3Weight]. My user wants a report that shows the PartNumber and only the OpWeight that corresponds to the NumberOps. So, if the NumberOps is 2, only want OP2Weight displayed. I created 3 control, one for each OpWeight. For each respective one, I created a related conditional format. i.e. Op1 weight is black text on a white background UNLESS the field [NumberOps] is not equal to 1. (Then is is white Text on White background.) Op2 weight is black text on a white background UNLESS the field [NumberOps] is not equal to 2. (Then is is white Text on White background.) Op3 weight is black text on a white background UNLESS the field [NumberOps] is not equal to 3. (Then is is white Text on White background.) This works good, except that I cannot place the fields in the same location on the report, as they cover each other up. So, after I created the conditional format, I changed the background style for each of these controls to Transparent. This sort of works, except that the data in these fields is blurry, looks weird. Obviously this isn't the solution. Does anyone have any better suggestions? |
#4
|
|||
|
|||
Make a Control Dissapear with Conditional Formatting?
Choose is a VBA function. It should be in the VBA help.
It is not a control source parameter. No the code looks fine - UNLESS NumberOps can be null or some value other than 1 to 3. I might set all three controls visibility to false and then use the If ElseIf structure to set the required control's visibility to true. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County tkosel wrote: Mr. Spencer, Thanks for your suggestions. I have never even heard of the "Choose" control source parameter. I cannot find any help about it in the Access Help. I would like to understand how it works. Can you refer me to a source for help? I did use the OnPrint event to hide controls and that seemed to work as well. See any problems with this code on the OnPrint Event? Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer) If Me.NumberOps = 1 Then Me.LastOfOP1_FinishedPartWeight.Visible = True Me.LastOfOP2_FinishedPartWeight.Visible = False Me.LastOfOP3_FinishedPartWeight.Visible = False ElseIf Me.NumberOps = 2 Then Me.LastOfOP1_FinishedPartWeight.Visible = False Me.LastOfOP2_FinishedPartWeight.Visible = True Me.LastOfOP3_FinishedPartWeight.Visible = False ElseIf Me.NumberOps = 3 Then Me.LastOfOP1_FinishedPartWeight.Visible = False Me.LastOfOP2_FinishedPartWeight.Visible = False Me.LastOfOP3_FinishedPartWeight.Visible = True End If End Sub "John Spencer" wrote: One method Add a text box control Name: TextShowThis Control Source: = Choose([NumberOps],[Op1Weight],[Op2Weight],[Op3Weight]) Set the visible property of the three weight controls to No. OR Use the format event of the section to populate a single control with the correct value. The control should have a blank control source. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) Me.TextShowThis = Choose([NumberOps],[Op1Weight],[Op2Weight],[Op3Weight]) End Sub A problem with Choose is that NumberOps MUST have a number value. It cannot be null. You can handle that in the above expressions by using the NZ function Choose(Nz([NumberOps],0),[Op1Weight],[Op2Weight],[Op3Weight]) If NumberOps is not a number but is a text value containing a number character then you will need to convert the text to a number of you can use Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) Select Case NumberOps Case 1 ' Or "1" if text value Me.TextShowThis = Me.Op1Weight Case 2 Me.TextShowThis = Me.Op2Weight Case 3 Me.TextShowThis = Me.Op3Weight Case Else Me.TextShowThis = null End Select End Sub John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County tkosel wrote: I have a report with 5 bound text boxes on it. They are [PartNumber], [NumberOps], [Op1Weight], [Op2Weight] and [Op3Weight]. My user wants a report that shows the PartNumber and only the OpWeight that corresponds to the NumberOps. So, if the NumberOps is 2, only want OP2Weight displayed. I created 3 control, one for each OpWeight. For each respective one, I created a related conditional format. i.e. Op1 weight is black text on a white background UNLESS the field [NumberOps] is not equal to 1. (Then is is white Text on White background.) Op2 weight is black text on a white background UNLESS the field [NumberOps] is not equal to 2. (Then is is white Text on White background.) Op3 weight is black text on a white background UNLESS the field [NumberOps] is not equal to 3. (Then is is white Text on White background.) This works good, except that I cannot place the fields in the same location on the report, as they cover each other up. So, after I created the conditional format, I changed the background style for each of these controls to Transparent. This sort of works, except that the data in these fields is blurry, looks weird. Obviously this isn't the solution. Does anyone have any better suggestions? |
Thread Tools | |
Display Modes | |
|
|