Make a Control Dissapear with Conditional Formatting?

[tkosel]

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?

 

[John Spencer]

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]

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

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

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?

 

[John Spencer]

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

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

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

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?