Make a text box border color change with conditional formatting

[*Glen*]

Hi everyone,

My intent is to make a text box not visible when the value is blank. I
tried to be creative and applied conditional formatting to the text box, but
only the font and border change to white (to make it "invisible"); the border
color remains blue. Is there a way to make the border color also change when
the text box meets a certain condition? Thanks for your time!

Glen

 

[Dirk Goldgar]

Hi everyone,

My intent is to make a text box not visible when the value is blank. I
tried to be creative and applied conditional formatting to the text box,
but
only the font and border change to white (to make it "invisible");

Do you mean ForeColor and BackColor?

the border
color remains blue. Is there a way to make the border color also change
when
the text box meets a certain condition? Thanks for your time!

I don't think you can do that with conditional formatting. If the form is
in single form view, not continuous, you can use VBA code in the form's
Current event and the text box's AfterUpdate event to change the properties
any way you want, including simply setting the control's Visible property.
But with continuous forms, that doesn't work.

One way to hide a control on a continuous form is to put another control on
top of it, and rig that control so that it completely hides the original
control. For example, you can create a text box with these properties:

Name: txtHider
Back Style: Transparent
Fore Color: -2147483633
Font Name: Terminal
Control Source: =IIf(<your condition is true>,String(100,"Û"),Null)

Make sure that txtHider is on top of the text box.

 

[*Glen*]

It is actually a report. In design view, the text box property has a border
style of "solid" and a border color of "#0066FF." When I view the report, I
want to have three text boxes. Two should be visible because they have data.
One should not be visible because it has null data (i.e. empty fields).

 

[Dirk Goldgar]

It is actually a report.

Oh, you should have said!

In design view, the text box property has a border
style of "solid" and a border color of "#0066FF." When I view the report,
I
want to have three text boxes. Two should be visible because they have
data.
One should not be visible because it has null data (i.e. empty fields).

On a report, you can forgo conditional formatting altogether and use code in
the detail section's Format event to set the text box's .Visible property
according to any conditions you like. For example,

'----- start of example code -----
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

With Me!txtYourTextbox
.Visible = Not IsNull(.Value)
End With

End Sub
'----- end of example code -----

 

[*Glen*]

The name of the text box is "Staff_Section"

In my table, some fields have text in the Staff_Section field while others
do not. I only want to show the Staff_Section data when the field has text
in it.

I would appreciate if you can be more specific with your code; VBA is not my
forte

Thanks!

 

[Dirk Goldgar]

The name of the text box is "Staff_Section"

In my table, some fields have text in the Staff_Section field while others
do not. I only want to show the Staff_Section data when the field has
text
in it.

I would appreciate if you can be more specific with your code; VBA is not
my
forte

C'mon, at least make an effort! Here is the revised code:

'----- start of code -----
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

With Me!Staff_Section
.Visible = Not IsNull(.Value)
End With

End Sub
'----- end of code -----

Do you know how to create this event procedure on the report?

 

[*Glen*]

I right clicked on the text box, clicked on properties, then format. I
pasted your code into format, but the text box still appears. ((((

 

[John W. Vinson]

I right clicked on the text box, clicked on properties, then format. I
pasted your code into format, but the text box still appears. ((((

Erase what you've done.

Open the Report in design view.

Select the Detail section. View its Properties.

Find the Format event on the Events tab.

Click the ... icon by it.

Choose "Code Builder".

Copy and paste Dirk's code in place of the Sub... End Sub pair that Access
will give you.

 

[Dirk Goldgar]

I right clicked on the text box, clicked on properties, then format. I
pasted your code into format, but the text box still appears. ((((

It doesn't sound like you did it right -- the code was for the Detail
section's Format event, not any event of the text box.. I have to go now,
so I'll post better instructions either late tonight or tomorrow afternoon,
if I get the chance.

 

[Dirk Goldgar]

Erase what you've done.

Open the Report in design view.

Select the Detail section. View its Properties.

Find the Format event on the Events tab.

Click the ... icon by it.

Choose "Code Builder".

Copy and paste Dirk's code in place of the Sub... End Sub pair that Access
will give you.

Thanks a bunch for jumping in, John. I had to go out and had no time to
give the instructions.

 

[*Glen*]

I'd hate to tell you this, but the code makes no difference. I inputted it
exactly as shown.

Maybe I forgot to mention something or was not clear. Here is a recap: I
have a table which contains a field named "Staff_Section." Some fields have
a staff section entered while some do not. On my report, I have a footer
that summs all the fields that belong to a particular staff section. Please
see below for an example:

Division "A" 2008 Total 2009 Total
Area 1 1 3
Area 2 1 2
Area 3 2 1
Divison Total 4 6

Division "B" 2008 Total 2009 Total
Area 1 1 1
Area 2 3 2
Area 3 1 1
Division Total 5 4

Staff Sec Tot 9 10


**********Staff Section "A"*******

Division "C" 2008 Total 2009 Total
Area 1 1 3
Area 2 1 2
Area 3 2 1
Divison Total 4 6

Division "D" 2008 Total 2009 Total
Area 1 1 1
Area 2 3 2
Area 3 1 1
Division Total 5 4

***Staff Sec Tot 9 10********

***Notice that in the first example, there is no Staff Section identified,
but in the second example, Divisions C & D are subordinates of Staff Section
A. There is a footer for the Staff Section which summs both divisions.

I do not want there to be a Staff Section footer in the first table because
there is not staff section associated with it. The staff section fields are
all empty.

I appreciate all of your help and I sincerely apologize for my ignorance
and/or stupidity. I hope you can shed some light and help me to resolve
this. Thank you so much. You guys are awesome!!!!

Glen

 

[Dirk Goldgar]

I'd hate to tell you this, but the code makes no difference. I inputted
it
exactly as shown.

Maybe I forgot to mention something or was not clear. Here is a recap: I
have a table which contains a field named "Staff_Section." Some fields
have
a staff section entered while some do not. On my report, I have a footer
that summs all the fields that belong to a particular staff section.
Please
see below for an example:

Division "A" 2008 Total 2009 Total
Area 1 1 3
Area 2 1 2
Area 3 2 1
Divison Total 4 6

Division "B" 2008 Total 2009 Total
Area 1 1 1
Area 2 3 2
Area 3 1 1
Division Total 5 4

Staff Sec Tot 9 10


**********Staff Section "A"*******

Division "C" 2008 Total 2009 Total
Area 1 1 3
Area 2 1 2
Area 3 2 1
Divison Total 4 6

Division "D" 2008 Total 2009 Total
Area 1 1 1
Area 2 3 2
Area 3 1 1
Division Total 5 4

***Staff Sec Tot 9 10********

***Notice that in the first example, there is no Staff Section identified,
but in the second example, Divisions C & D are subordinates of Staff
Section
A. There is a footer for the Staff Section which summs both divisions.

I do not want there to be a Staff Section footer in the first table
because
there is not staff section associated with it. The staff section fields
are
all empty.

Now you've given another vital bit of information. As I now understand it,
your report is grouped by Staff_Section, and you want to hide the entire
group footer if the Staff_Section field is Null. Is that correct?

If so, the easiest way to do that is to use the Format event of the group
footer to check if the Staff_Section is Null, and cancel the event if it is.
Code for that might look like this:

'------ start of code ------
Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)

If IsNull(Me.Staff_Section) Then
Cancel = True
End If

End Sub
'------ end of code ------

Now, I don't know for sure that Staff_Section is the first group on your
report, and so would be group 0, with its group footer named "GroupFooter0".
What you should do is open the report in design view, click on the footer
bar for that says Staff_Section Footer, open its property sheet, go to the
Event tab, set the On Format property to [Event Procedure], then click the
build button (caption "...") at the end of the line. That will create and
display the shell of the event procedure, and you can paste just the body of
the procedure above into it.

 

[*Glen*]

Dirk,

I sincerely appreciate your time and patience. I am still struggling with
this one. I pasted the code per your instructions, but the Staff_Section
footer (which is called GroupFooter1 in the properties box) continues to
appear. And yes...I edited your code to reflect the "GroupFooter1" name
rather than the "GroupFooter0" name you used.

Just to confirm: My table has a field named Staff_Section. For the records
in which this field is blank, I do not want the footer to appear. You are
correct with your last statement:

"As I now understand it, your report is grouped by Staff_Section, and you
want to hide the entire group footer if the Staff_Section field is Null. Is
that correct?"

I apologize for being such an idiot. I appreciate your help!

Thanks!

Glen

I'd hate to tell you this, but the code makes no difference. I inputted
it
exactly as shown.

Maybe I forgot to mention something or was not clear. Here is a recap: I
have a table which contains a field named "Staff_Section." Some fields
have
a staff section entered while some do not. On my report, I have a footer
that summs all the fields that belong to a particular staff section.
Please
see below for an example:

Division "A" 2008 Total 2009 Total
Area 1 1 3
Area 2 1 2
Area 3 2 1
Divison Total 4 6

Division "B" 2008 Total 2009 Total
Area 1 1 1
Area 2 3 2
Area 3 1 1
Division Total 5 4

Staff Sec Tot 9 10


**********Staff Section "A"*******

Division "C" 2008 Total 2009 Total
Area 1 1 3
Area 2 1 2
Area 3 2 1
Divison Total 4 6

Division "D" 2008 Total 2009 Total
Area 1 1 1
Area 2 3 2
Area 3 1 1
Division Total 5 4

***Staff Sec Tot 9 10********

***Notice that in the first example, there is no Staff Section identified,
but in the second example, Divisions C & D are subordinates of Staff
Section
A. There is a footer for the Staff Section which summs both divisions.

I do not want there to be a Staff Section footer in the first table
because
there is not staff section associated with it. The staff section fields
are
all empty.

Now you've given another vital bit of information. As I now understand it,
your report is grouped by Staff_Section, and you want to hide the entire
group footer if the Staff_Section field is Null. Is that correct?

If so, the easiest way to do that is to use the Format event of the group
footer to check if the Staff_Section is Null, and cancel the event if it is.
Code for that might look like this:

'------ start of code ------
Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)

If IsNull(Me.Staff_Section) Then
Cancel = True
End If

End Sub
'------ end of code ------

Now, I don't know for sure that Staff_Section is the first group on your
report, and so would be group 0, with its group footer named "GroupFooter0".
What you should do is open the report in design view, click on the footer
bar for that says Staff_Section Footer, open its property sheet, go to the
Event tab, set the On Format property to [Event Procedure], then click the
build button (caption "...") at the end of the line. That will create and
display the shell of the event procedure, and you can paste just the body of
the procedure above into it.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)