Change textbox/combobox colors with a procedure

[Robert5833]

Hi All;
I’m using Access 2007, Vista Business, 1 Gb ram

I hope there is a simple solution to my issue. I have a form with 20+ text
and combo boxes. I want their fore and back colors to change based on their
value. As it is now, I'm using a Case Select statement for each of them in
the form's Current event. But that seems too busy for what must be a global
procedure solution.

Code now (for one combo box control, others same):
Select Case Me.cboMonthLimitID
Case Is <= 0
Me.cboMonthLimitID.ForeColor = lngWhite
Me.cboMonthLimitID.BackColor = lngWhite
Case Is >= 1
Me.cboMonthLimitID.ForeColor = lngBlack
Me.cboMonthLimitID.BackColor = lngYellow
Case Else
Me.cboMonthLimitID.ForeColor = lngWhite
Me.cboMonthLimitID.BackColor = lngWhite
End Select

The above code works okay, although I have struggled with some fields
remaining highlighted after update (when I want them to revert to white
fore/back color when not = to a stated value).

I would prefer to use a global function or sub that would allow me to call
it from each named control in the form's Current event procedure. But I don't
know how or what to declare (Dim) in the global procedure or the calling form.

I have built and used other global or public functions and subs, but only
ones that included the named controls within.

Can a global procedure be called for a named control, without having to
declare each named control in the global procedure? (i.e., I would like to
have something that lets me call the global procedure to without having to
declare each control by name so I can use the routine for all of my forms and
reports.)

Also, what is the syntax used to call the global procedure at the control in
the form’s Current event procedure? (i.e., DoCmd.RunCommand.Me![control name]
gGetColor (as the global procedure name.)

I did find in another post this snippet from Marshall B., but I’m not clear
on how to modify it to my case:

“If the function is in a standard module, then use:â€
=yourfunction(Form)
“And code the function this way:â€
Public Function yourfunction(frm As Form)
If somecondition Then
frm.Section(0).BackColor = vbRed
Else …

I’m confused on the If / Then argument. I know what the condition will be
for all text and combo boxes on my form; either <=0 or >=1, etc., but it
seems for the If Then argument above I would have to declare the text or
combo box within that argument.

Can I Dim a reference for the global procedure such as Dim CTRL As Control,
and include that reference in the If Then argument?

I’m also unsure of the frm.Section(0) statements in the above snippet. I get
the part about frm as a variable or object identified in the procedures
declaration (yourfunction(frm As Form), but how do I tie it to the actual
object (my form) ?

Also, in this case I’m not sure what is being referenced in “Section(0).†Is
that a section on a form?

Simply stated what I need is a procedure that takes a constant value or
condition (condition A; <=0, or condition B; >=1), and returns either vbaRed
or vbaWhite accordingly. Then for each text or combo box on any of my forms I
want to call that procedure to set the fore and back color to red or white
based on that control’s condition.

Could someone please help me out of my ignorance on this? There are half a
dozen other instances where I feel a global procedure will simplify my code,
but I'm struggling with the constructs at the most basic level; when and
where to Dim/declare variables, and where and how to call the procedure for a
control at the form level.

I hope these questions make sense…I don’t even know enough to know what I
don’t know…and that’s never a good thing.

Best regards and thank you in advance for your help and patience.

RL

 

[Dale Fye]

Have you looked at conditional formatting?

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Hi All;
I’m using Access 2007, Vista Business, 1 Gb ram

I hope there is a simple solution to my issue. I have a form with 20+ text
and combo boxes. I want their fore and back colors to change based on their
value. As it is now, I'm using a Case Select statement for each of them in
the form's Current event. But that seems too busy for what must be a global
procedure solution.

Code now (for one combo box control, others same):
Select Case Me.cboMonthLimitID
Case Is <= 0
Me.cboMonthLimitID.ForeColor = lngWhite
Me.cboMonthLimitID.BackColor = lngWhite
Case Is >= 1
Me.cboMonthLimitID.ForeColor = lngBlack
Me.cboMonthLimitID.BackColor = lngYellow
Case Else
Me.cboMonthLimitID.ForeColor = lngWhite
Me.cboMonthLimitID.BackColor = lngWhite
End Select

The above code works okay, although I have struggled with some fields
remaining highlighted after update (when I want them to revert to white
fore/back color when not = to a stated value).

I would prefer to use a global function or sub that would allow me to call
it from each named control in the form's Current event procedure. But I don't
know how or what to declare (Dim) in the global procedure or the calling form.

I have built and used other global or public functions and subs, but only
ones that included the named controls within.

Can a global procedure be called for a named control, without having to
declare each named control in the global procedure? (i.e., I would like to
have something that lets me call the global procedure to without having to
declare each control by name so I can use the routine for all of my forms and
reports.)

Also, what is the syntax used to call the global procedure at the control in
the form’s Current event procedure? (i.e., DoCmd.RunCommand.Me![control name]
gGetColor (as the global procedure name.)

I did find in another post this snippet from Marshall B., but I’m not clear
on how to modify it to my case:

“If the function is in a standard module, then use:â€
=yourfunction(Form)
“And code the function this way:â€
Public Function yourfunction(frm As Form)
If somecondition Then
frm.Section(0).BackColor = vbRed
Else …

I’m confused on the If / Then argument. I know what the condition will be
for all text and combo boxes on my form; either <=0 or >=1, etc., but it
seems for the If Then argument above I would have to declare the text or
combo box within that argument.

Can I Dim a reference for the global procedure such as Dim CTRL As Control,
and include that reference in the If Then argument?

I’m also unsure of the frm.Section(0) statements in the above snippet. I get
the part about frm as a variable or object identified in the procedures
declaration (yourfunction(frm As Form), but how do I tie it to the actual
object (my form) ?

Also, in this case I’m not sure what is being referenced in “Section(0).†Is
that a section on a form?

Simply stated what I need is a procedure that takes a constant value or
condition (condition A; <=0, or condition B; >=1), and returns either vbaRed
or vbaWhite accordingly. Then for each text or combo box on any of my forms I
want to call that procedure to set the fore and back color to red or white
based on that control’s condition.

Could someone please help me out of my ignorance on this? There are half a
dozen other instances where I feel a global procedure will simplify my code,
but I'm struggling with the constructs at the most basic level; when and
where to Dim/declare variables, and where and how to call the procedure for a
control at the form level.

I hope these questions make sense…I don’t even know enough to know what I
don’t know…and that’s never a good thing.

Best regards and thank you in advance for your help and patience.

RL

 

[Robert5833]

Hi Dale,
I have used Conditional Formatting but it's a lot of work at the form level,
and if I change the condition variable I would rather do that from a global
and have it fed to the variety of forms in my db. Conditional Formatting also
seems to load up the form and slow it down. I still use that feature, but not
very often.

Thanks for the quick reply though!

RL

Have you looked at conditional formatting?

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Hi All;
I’m using Access 2007, Vista Business, 1 Gb ram

I hope there is a simple solution to my issue. I have a form with 20+ text
and combo boxes. I want their fore and back colors to change based on their
value. As it is now, I'm using a Case Select statement for each of them in
the form's Current event. But that seems too busy for what must be a global
procedure solution.

Code now (for one combo box control, others same):
Select Case Me.cboMonthLimitID
Case Is <= 0
Me.cboMonthLimitID.ForeColor = lngWhite
Me.cboMonthLimitID.BackColor = lngWhite
Case Is >= 1
Me.cboMonthLimitID.ForeColor = lngBlack
Me.cboMonthLimitID.BackColor = lngYellow
Case Else
Me.cboMonthLimitID.ForeColor = lngWhite
Me.cboMonthLimitID.BackColor = lngWhite
End Select

The above code works okay, although I have struggled with some fields
remaining highlighted after update (when I want them to revert to white
fore/back color when not = to a stated value).

I would prefer to use a global function or sub that would allow me to call
it from each named control in the form's Current event procedure. But I don't
know how or what to declare (Dim) in the global procedure or the calling form.

I have built and used other global or public functions and subs, but only
ones that included the named controls within.

Can a global procedure be called for a named control, without having to
declare each named control in the global procedure? (i.e., I would like to
have something that lets me call the global procedure to without having to
declare each control by name so I can use the routine for all of my forms and
reports.)

Also, what is the syntax used to call the global procedure at the control in
the form’s Current event procedure? (i.e., DoCmd.RunCommand.Me![control name]
gGetColor (as the global procedure name.)

I did find in another post this snippet from Marshall B., but I’m not clear
on how to modify it to my case:

“If the function is in a standard module, then use:â€
=yourfunction(Form)
“And code the function this way:â€
Public Function yourfunction(frm As Form)
If somecondition Then
frm.Section(0).BackColor = vbRed
Else …

I’m confused on the If / Then argument. I know what the condition will be
for all text and combo boxes on my form; either <=0 or >=1, etc., but it
seems for the If Then argument above I would have to declare the text or
combo box within that argument.

Can I Dim a reference for the global procedure such as Dim CTRL As Control,
and include that reference in the If Then argument?

I’m also unsure of the frm.Section(0) statements in the above snippet. I get
the part about frm as a variable or object identified in the procedures
declaration (yourfunction(frm As Form), but how do I tie it to the actual
object (my form) ?

Also, in this case I’m not sure what is being referenced in “Section(0).†Is
that a section on a form?

Simply stated what I need is a procedure that takes a constant value or
condition (condition A; <=0, or condition B; >=1), and returns either vbaRed
or vbaWhite accordingly. Then for each text or combo box on any of my forms I
want to call that procedure to set the fore and back color to red or white
based on that control’s condition.

Could someone please help me out of my ignorance on this? There are half a
dozen other instances where I feel a global procedure will simplify my code,
but I'm struggling with the constructs at the most basic level; when and
where to Dim/declare variables, and where and how to call the procedure for a
control at the form level.

I hope these questions make sense…I don’t even know enough to know what I
don’t know…and that’s never a good thing.

Best regards and thank you in advance for your help and patience.

RL

 

[Dale Fye]

Robert,

You could create a subroutine that you call from the Forms Current event,
that loops through all of the controls on the form, checks to see if they
are supposed to have this formatting (I'd set the value of each controls Tag
property to some preset value), and if so, implement the code. It might
look something like:

Private Sub subCondFormatting

Dim ctrl as control

For each ctrl in me.controls
if ctrl.Tag = "Conditional" then Call FormatControl(ctrl)
Next
End sub
Private Sub FormatControl(ctrl as control)

if ctrl.Value >= 1 then
ctrl.forecolor = lngBlack
else
ctrl.Forecolor = lngWhite
endif
end sub

Then, you will also need to call the FormatControl subroutine from the
AfterUpdate event of each of the controls, something like:

Private Sub txt_Text1_AfterUpdate

Call FormatControl(me.txt_Text1)

End Sub

But now that I think about it, why would you want the controls forecolor and
backcolor to both be white?

HTH
Dale

Hi Dale,
I have used Conditional Formatting but it's a lot of work at the form
level,
and if I change the condition variable I would rather do that from a
global
and have it fed to the variety of forms in my db. Conditional Formatting
also
seems to load up the form and slow it down. I still use that feature, but
not
very often.

Thanks for the quick reply though!

RL

Have you looked at conditional formatting?

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Hi All;
I'm using Access 2007, Vista Business, 1 Gb ram

I hope there is a simple solution to my issue. I have a form with 20+
text
and combo boxes. I want their fore and back colors to change based on
their
value. As it is now, I'm using a Case Select statement for each of them
in
the form's Current event. But that seems too busy for what must be a
global
procedure solution.

Code now (for one combo box control, others same):
Select Case Me.cboMonthLimitID
Case Is <= 0
Me.cboMonthLimitID.ForeColor = lngWhite
Me.cboMonthLimitID.BackColor = lngWhite
Case Is >= 1
Me.cboMonthLimitID.ForeColor = lngBlack
Me.cboMonthLimitID.BackColor = lngYellow
Case Else
Me.cboMonthLimitID.ForeColor = lngWhite
Me.cboMonthLimitID.BackColor = lngWhite
End Select

The above code works okay, although I have struggled with some fields
remaining highlighted after update (when I want them to revert to white
fore/back color when not = to a stated value).

I would prefer to use a global function or sub that would allow me to
call
it from each named control in the form's Current event procedure. But I
don't
know how or what to declare (Dim) in the global procedure or the
calling form.

I have built and used other global or public functions and subs, but
only
ones that included the named controls within.

Can a global procedure be called for a named control, without having to
declare each named control in the global procedure? (i.e., I would like
to
have something that lets me call the global procedure to without having
to
declare each control by name so I can use the routine for all of my
forms and
reports.)

Also, what is the syntax used to call the global procedure at the
control in
the form's Current event procedure? (i.e., DoCmd.RunCommand.Me![control
name]
gGetColor (as the global procedure name.)

I did find in another post this snippet from Marshall B., but I'm not
clear
on how to modify it to my case:

"If the function is in a standard module, then use:"
=yourfunction(Form)
"And code the function this way:"
Public Function yourfunction(frm As Form)
If somecondition Then
frm.Section(0).BackColor = vbRed
Else .

I'm confused on the If / Then argument. I know what the condition will
be
for all text and combo boxes on my form; either <=0 or >=1, etc., but
it
seems for the If Then argument above I would have to declare the text
or
combo box within that argument.

Can I Dim a reference for the global procedure such as Dim CTRL As
Control,
and include that reference in the If Then argument?

I'm also unsure of the frm.Section(0) statements in the above snippet.
I get
the part about frm as a variable or object identified in the procedures
declaration (yourfunction(frm As Form), but how do I tie it to the
actual
object (my form) ?

Also, in this case I'm not sure what is being referenced in
"Section(0)." Is
that a section on a form?

Simply stated what I need is a procedure that takes a constant value or
condition (condition A; <=0, or condition B; >=1), and returns either
vbaRed
or vbaWhite accordingly. Then for each text or combo box on any of my
forms I
want to call that procedure to set the fore and back color to red or
white
based on that control's condition.

Could someone please help me out of my ignorance on this? There are
half a
dozen other instances where I feel a global procedure will simplify my
code,
but I'm struggling with the constructs at the most basic level; when
and
where to Dim/declare variables, and where and how to call the procedure
for a
control at the form level.

I hope these questions make sense.I don't even know enough to know what
I
don't know.and that's never a good thing.

Best regards and thank you in advance for your help and patience.

RL

 

[Robert5833]

Hi Dale,
Thank you! This looks like exactly what I need! I'll try it out and reply
again.

BTW the simple answer for setting back and fore color to white is that I
don’t know what I’m doing…yet. I have been using that function to hide text
when it doesn't meet either condition. That scheme may change now with this
new approach, but the idea is that some controls will be empty on purpose.

I'm using the text and combo boxes to display measurement points, and then
doing some math to show an item's status as it accrues the measurement type
against a prescribed limit.

Example

1) A measurement value in hours, cycles, months, or years (as a multiplier)
2) A running total to date
3) A running or remaining "balance" against the defined limit (each 100
hours, each 100 cycles, each 6-mos, etc.)

On my form I show all the measurement categories and type all of the time.
In filtered view though, where I may be looking at the Hours measurements,
that same item may not also be measured in Cycles. When it is not, its
measurement parameters are either Null or Empty. To avoid a returned #Error
on Null Propagation I’ve set them up with Nz functions, which causes them to
return a nonsensical value (a negative number). I don’t get the #Error that
way, but then I hide the displayed value when it has no bearing.

Thank you again for offering up a solution for me! I’m anxious to try it out
and report back.

(Also, I'm trying to post this reply so the discussion string stays in order
and without <snipping> your input...but I don't know if I'm doing it
correctly.)

Best regards,
RL

Robert,

You could create a subroutine that you call from the Forms Current event,
that loops through all of the controls on the form, checks to see if they
are supposed to have this formatting (I'd set the value of each controls Tag
property to some preset value), and if so, implement the code. It might
look something like:

Private Sub subCondFormatting

Dim ctrl as control

For each ctrl in me.controls
if ctrl.Tag = "Conditional" then Call FormatControl(ctrl)
Next
End sub
Private Sub FormatControl(ctrl as control)

if ctrl.Value >= 1 then
ctrl.forecolor = lngBlack
else
ctrl.Forecolor = lngWhite
endif
end sub

Then, you will also need to call the FormatControl subroutine from the
AfterUpdate event of each of the controls, something like:

Private Sub txt_Text1_AfterUpdate

Call FormatControl(me.txt_Text1)

End Sub

But now that I think about it, why would you want the controls forecolor and
backcolor to both be white?

HTH
Dale

Hi Dale,
I have used Conditional Formatting but it's a lot of work at the form
level,
and if I change the condition variable I would rather do that from a
global
and have it fed to the variety of forms in my db. Conditional Formatting
also
seems to load up the form and slow it down. I still use that feature, but
not
very often.

Thanks for the quick reply though!

RL

Have you looked at conditional formatting?

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

Hi All;
I'm using Access 2007, Vista Business, 1 Gb ram

I hope there is a simple solution to my issue. I have a form with 20+
text
and combo boxes. I want their fore and back colors to change based on
their
value. As it is now, I'm using a Case Select statement for each of them
in
the form's Current event. But that seems too busy for what must be a
global
procedure solution.

Code now (for one combo box control, others same):
Select Case Me.cboMonthLimitID
Case Is <= 0
Me.cboMonthLimitID.ForeColor = lngWhite
Me.cboMonthLimitID.BackColor = lngWhite
Case Is >= 1
Me.cboMonthLimitID.ForeColor = lngBlack
Me.cboMonthLimitID.BackColor = lngYellow
Case Else
Me.cboMonthLimitID.ForeColor = lngWhite
Me.cboMonthLimitID.BackColor = lngWhite
End Select

The above code works okay, although I have struggled with some fields
remaining highlighted after update (when I want them to revert to white
fore/back color when not = to a stated value).

I would prefer to use a global function or sub that would allow me to
call
it from each named control in the form's Current event procedure. But I
don't
know how or what to declare (Dim) in the global procedure or the
calling form.

I have built and used other global or public functions and subs, but
only
ones that included the named controls within.

Can a global procedure be called for a named control, without having to
declare each named control in the global procedure? (i.e., I would like
to
have something that lets me call the global procedure to without having
to
declare each control by name so I can use the routine for all of my
forms and
reports.)

Also, what is the syntax used to call the global procedure at the
control in
the form's Current event procedure? (i.e., DoCmd.RunCommand.Me![control
name]
gGetColor (as the global procedure name.)

I did find in another post this snippet from Marshall B., but I'm not
clear
on how to modify it to my case:

"If the function is in a standard module, then use:"
=yourfunction(Form)
"And code the function this way:"
Public Function yourfunction(frm As Form)
If somecondition Then
frm.Section(0).BackColor = vbRed
Else .

I'm confused on the If / Then argument. I know what the condition will
be
for all text and combo boxes on my form; either <=0 or >=1, etc., but
it
seems for the If Then argument above I would have to declare the text
or
combo box within that argument.

Can I Dim a reference for the global procedure such as Dim CTRL As
Control,
and include that reference in the If Then argument?

I'm also unsure of the frm.Section(0) statements in the above snippet.
I get
the part about frm as a variable or object identified in the procedures
declaration (yourfunction(frm As Form), but how do I tie it to the
actual
object (my form) ?

Also, in this case I'm not sure what is being referenced in
"Section(0)." Is
that a section on a form?

Simply stated what I need is a procedure that takes a constant value or
condition (condition A; <=0, or condition B; >=1), and returns either
vbaRed
or vbaWhite accordingly. Then for each text or combo box on any of my
forms I
want to call that procedure to set the fore and back color to red or
white
based on that control's condition.

Could someone please help me out of my ignorance on this? There are
half a
dozen other instances where I feel a global procedure will simplify my
code,
but I'm struggling with the constructs at the most basic level; when
and
where to Dim/declare variables, and where and how to call the procedure
for a
control at the form level.

I hope these questions make sense.I don't even know enough to know what
I
don't know.and that's never a good thing.

Best regards and thank you in advance for your help and patience.

RL

 

[Dale Fye]

Robert.

You might want to consider just hiding the control (setting it's visible
property to False), or locking the control as well (for the white on white).
Just because the text is the same color as the background does not mean that
the user cannot type in that control.

Rather than display an non-sensical value, why not just display "N/A", and
leave it at that?

Dale

Hi Dale,
Thank you! This looks like exactly what I need! I'll try it out and reply
again.

BTW the simple answer for setting back and fore color to white is that I
don't know what I'm doing.yet. I have been using that function to hide
text
when it doesn't meet either condition. That scheme may change now with
this
new approach, but the idea is that some controls will be empty on purpose.

I'm using the text and combo boxes to display measurement points, and then
doing some math to show an item's status as it accrues the measurement
type
against a prescribed limit.

Example

1) A measurement value in hours, cycles, months, or years (as a
multiplier)
2) A running total to date
3) A running or remaining "balance" against the defined limit (each 100
hours, each 100 cycles, each 6-mos, etc.)

On my form I show all the measurement categories and type all of the time.
In filtered view though, where I may be looking at the Hours measurements,
that same item may not also be measured in Cycles. When it is not, its
measurement parameters are either Null or Empty. To avoid a returned
#Error
on Null Propagation I've set them up with Nz functions, which causes them
to
return a nonsensical value (a negative number). I don't get the #Error
that
way, but then I hide the displayed value when it has no bearing.

Thank you again for offering up a solution for me! I'm anxious to try it
out
and report back.

(Also, I'm trying to post this reply so the discussion string stays in
order
and without <snipping> your input...but I don't know if I'm doing it
correctly.)

Best regards,
RL

Robert,

You could create a subroutine that you call from the Forms Current event,
that loops through all of the controls on the form, checks to see if they
are supposed to have this formatting (I'd set the value of each controls
Tag
property to some preset value), and if so, implement the code. It might
look something like:

Private Sub subCondFormatting

Dim ctrl as control

For each ctrl in me.controls
if ctrl.Tag = "Conditional" then Call FormatControl(ctrl)
Next
End sub
Private Sub FormatControl(ctrl as control)

if ctrl.Value >= 1 then
ctrl.forecolor = lngBlack
else
ctrl.Forecolor = lngWhite
endif
end sub

Then, you will also need to call the FormatControl subroutine from the
AfterUpdate event of each of the controls, something like:

Private Sub txt_Text1_AfterUpdate

Call FormatControl(me.txt_Text1)

End Sub

But now that I think about it, why would you want the controls forecolor
and
backcolor to both be white?

HTH
Dale

Hi Dale,
I have used Conditional Formatting but it's a lot of work at the form
level,
and if I change the condition variable I would rather do that from a
global
and have it fed to the variety of forms in my db. Conditional
Formatting
also
seems to load up the form and slow it down. I still use that feature,
but
not
very often.

Thanks for the quick reply though!

RL

:

Have you looked at conditional formatting?

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

Hi All;
I'm using Access 2007, Vista Business, 1 Gb ram

I hope there is a simple solution to my issue. I have a form with
20+
text
and combo boxes. I want their fore and back colors to change based
on
their
value. As it is now, I'm using a Case Select statement for each of
them
in
the form's Current event. But that seems too busy for what must be a
global
procedure solution.

Code now (for one combo box control, others same):
Select Case Me.cboMonthLimitID
Case Is <= 0
Me.cboMonthLimitID.ForeColor = lngWhite
Me.cboMonthLimitID.BackColor = lngWhite
Case Is >= 1
Me.cboMonthLimitID.ForeColor = lngBlack
Me.cboMonthLimitID.BackColor = lngYellow
Case Else
Me.cboMonthLimitID.ForeColor = lngWhite
Me.cboMonthLimitID.BackColor = lngWhite
End Select

The above code works okay, although I have struggled with some
fields
remaining highlighted after update (when I want them to revert to
white
fore/back color when not = to a stated value).

I would prefer to use a global function or sub that would allow me
to
call
it from each named control in the form's Current event procedure.
But I
don't
know how or what to declare (Dim) in the global procedure or the
calling form.

I have built and used other global or public functions and subs, but
only
ones that included the named controls within.

Can a global procedure be called for a named control, without having
to
declare each named control in the global procedure? (i.e., I would
like
to
have something that lets me call the global procedure to without
having
to
declare each control by name so I can use the routine for all of my
forms and
reports.)

Also, what is the syntax used to call the global procedure at the
control in
the form's Current event procedure? (i.e.,
DoCmd.RunCommand.Me![control
name]
gGetColor (as the global procedure name.)

I did find in another post this snippet from Marshall B., but I'm
not
clear
on how to modify it to my case:

"If the function is in a standard module, then use:"
=yourfunction(Form)
"And code the function this way:"
Public Function yourfunction(frm As Form)
If somecondition Then
frm.Section(0).BackColor = vbRed
Else .

I'm confused on the If / Then argument. I know what the condition
will
be
for all text and combo boxes on my form; either <=0 or >=1, etc.,
but
it
seems for the If Then argument above I would have to declare the
text
or
combo box within that argument.

Can I Dim a reference for the global procedure such as Dim CTRL As
Control,
and include that reference in the If Then argument?

I'm also unsure of the frm.Section(0) statements in the above
snippet.
I get
the part about frm as a variable or object identified in the
procedures
declaration (yourfunction(frm As Form), but how do I tie it to the
actual
object (my form) ?

Also, in this case I'm not sure what is being referenced in
"Section(0)." Is
that a section on a form?

Simply stated what I need is a procedure that takes a constant value
or
condition (condition A; <=0, or condition B; >=1), and returns
either
vbaRed
or vbaWhite accordingly. Then for each text or combo box on any of
my
forms I
want to call that procedure to set the fore and back color to red or
white
based on that control's condition.

Could someone please help me out of my ignorance on this? There are
half a
dozen other instances where I feel a global procedure will simplify
my
code,
but I'm struggling with the constructs at the most basic level; when
and
where to Dim/declare variables, and where and how to call the
procedure
for a
control at the form level.

I hope these questions make sense.I don't even know enough to know
what
I
don't know.and that's never a good thing.

Best regards and thank you in advance for your help and patience.

RL

 

[Robert5833]

Hi Dale,
That's an excellent approach! I hadn't consdiered that the field would still
be editable, and I don't want that. I'll make those changes.

As of now, I'm working with just one combo box to get the procedures to
work, and I've got it to where it will highlight in red, but only after I
change the controls value (as I have a procedure on the control's After
Update event).

I can't get it to change backcolor back to white though, but that's probably
because I haven't incorporated the <=0 as the second condition (I couldn't
see how to write the logic statement; If Then, ElseIf, etc.) in the subs
logic.

When I incorporated the subs and tried to call the procedure by inserting
the procedure name in the form object module, I get an error message:

Compile Error - Argument Not Optional.

The Form's Current event procedure title is: Private Sub Form_Current()

In that sub I have included: Call CondFormat (but I get the compile error
unless commented out, and only the After Update sub fires).

Here are the three procedures:

Private Sub CondFormat(frm As Form)

Dim CTRL As Control

For Each CTRL In frm.Controls
If CTRL.Tag = "Conditional" Then Call FormatControl(CTRL)

Next
End Sub
Note: in the above, I added the frm As Form def, because the procedure
wouldn't find the "In Me.Controls" statement.


Private Sub FormatControl(CTRL As Control)

Dim lngBlack As Long, lngRed As Long, lngYellow As Long, lngWhite As Long
'Dim CTRL As Control (commented out for a duplicate in scope)

lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)

If CTRL.Value >= 1 Then
CTRL.ForeColor = lngBlack
CTRL.BackColor = lngRed

Else
CTRL.foreclor = lngWhite
CTRL.BackColor = lngWhite

End If

End Sub


Private Sub cboHourLimitID_AfterUpdate()
Call FormatControl(Me.cboHourLimitID)

End Sub


Note: I have entered the text; Conditional in the Tag for each of the
controls but I don't know if it needed any operators or special handling.

I've probably misinterpreted or misapplied what you suggested for the
procedures. I also noticed that some procedures I've seen have declarations
have Dim something As Access.Control, vs. Dim something As Control. I'm not
sure what the difference is, but perhaps you could educate me on when it is
appropriate to use one or the other.

I appreciate your guidance and your patience in helping me with this "small"
problem.

Best regards,

RL

Robert.

You might want to consider just hiding the control (setting it's visible
property to False), or locking the control as well (for the white on white).
Just because the text is the same color as the background does not mean that
the user cannot type in that control.

Rather than display an non-sensical value, why not just display "N/A", and
leave it at that?

Dale

Hi Dale,
Thank you! This looks like exactly what I need! I'll try it out and reply
again.

BTW the simple answer for setting back and fore color to white is that I
don't know what I'm doing.yet. I have been using that function to hide
text
when it doesn't meet either condition. That scheme may change now with
this
new approach, but the idea is that some controls will be empty on purpose.

I'm using the text and combo boxes to display measurement points, and then
doing some math to show an item's status as it accrues the measurement
type
against a prescribed limit.

Example

1) A measurement value in hours, cycles, months, or years (as a
multiplier)
2) A running total to date
3) A running or remaining "balance" against the defined limit (each 100
hours, each 100 cycles, each 6-mos, etc.)

On my form I show all the measurement categories and type all of the time.
In filtered view though, where I may be looking at the Hours measurements,
that same item may not also be measured in Cycles. When it is not, its
measurement parameters are either Null or Empty. To avoid a returned
#Error
on Null Propagation I've set them up with Nz functions, which causes them
to
return a nonsensical value (a negative number). I don't get the #Error
that
way, but then I hide the displayed value when it has no bearing.

Thank you again for offering up a solution for me! I'm anxious to try it
out
and report back.

(Also, I'm trying to post this reply so the discussion string stays in
order
and without <snipping> your input...but I don't know if I'm doing it
correctly.)

Best regards,
RL

Robert,

You could create a subroutine that you call from the Forms Current event,
that loops through all of the controls on the form, checks to see if they
are supposed to have this formatting (I'd set the value of each controls
Tag
property to some preset value), and if so, implement the code. It might
look something like:

Private Sub subCondFormatting

Dim ctrl as control

For each ctrl in me.controls
if ctrl.Tag = "Conditional" then Call FormatControl(ctrl)
Next
End sub
Private Sub FormatControl(ctrl as control)

if ctrl.Value >= 1 then
ctrl.forecolor = lngBlack
else
ctrl.Forecolor = lngWhite
endif
end sub

Then, you will also need to call the FormatControl subroutine from the
AfterUpdate event of each of the controls, something like:

Private Sub txt_Text1_AfterUpdate

Call FormatControl(me.txt_Text1)

End Sub

But now that I think about it, why would you want the controls forecolor
and
backcolor to both be white?

HTH
Dale

Hi Dale,
I have used Conditional Formatting but it's a lot of work at the form
level,
and if I change the condition variable I would rather do that from a
global
and have it fed to the variety of forms in my db. Conditional
Formatting
also
seems to load up the form and slow it down. I still use that feature,
but
not
very often.

Thanks for the quick reply though!

RL

:

Have you looked at conditional formatting?

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

Hi All;
I'm using Access 2007, Vista Business, 1 Gb ram

I hope there is a simple solution to my issue. I have a form with
20+
text
and combo boxes. I want their fore and back colors to change based
on
their
value. As it is now, I'm using a Case Select statement for each of
them
in
the form's Current event. But that seems too busy for what must be a
global
procedure solution.

Code now (for one combo box control, others same):
Select Case Me.cboMonthLimitID
Case Is <= 0
Me.cboMonthLimitID.ForeColor = lngWhite
Me.cboMonthLimitID.BackColor = lngWhite
Case Is >= 1
Me.cboMonthLimitID.ForeColor = lngBlack
Me.cboMonthLimitID.BackColor = lngYellow
Case Else
Me.cboMonthLimitID.ForeColor = lngWhite
Me.cboMonthLimitID.BackColor = lngWhite
End Select

The above code works okay, although I have struggled with some
fields
remaining highlighted after update (when I want them to revert to
white
fore/back color when not = to a stated value).

I would prefer to use a global function or sub that would allow me
to
call
it from each named control in the form's Current event procedure.
But I
don't
know how or what to declare (Dim) in the global procedure or the
calling form.

I have built and used other global or public functions and subs, but
only
ones that included the named controls within.

Can a global procedure be called for a named control, without having
to
declare each named control in the global procedure? (i.e., I would
like
to
have something that lets me call the global procedure to without
having
to
declare each control by name so I can use the routine for all of my
forms and
reports.)

Also, what is the syntax used to call the global procedure at the
control in
the form's Current event procedure? (i.e.,
DoCmd.RunCommand.Me![control
name]
gGetColor (as the global procedure name.)

I did find in another post this snippet from Marshall B., but I'm
not
clear
on how to modify it to my case:

"If the function is in a standard module, then use:"
=yourfunction(Form)
"And code the function this way:"
Public Function yourfunction(frm As Form)
If somecondition Then
frm.Section(0).BackColor = vbRed
Else .

I'm confused on the If / Then argument. I know what the condition
will
be
for all text and combo boxes on my form; either <=0 or >=1, etc.,
but
it
seems for the If Then argument above I would have to declare the
text
or
combo box within that argument.

Can I Dim a reference for the global procedure such as Dim CTRL As
Control,
and include that reference in the If Then argument?

I'm also unsure of the frm.Section(0) statements in the above
snippet.
I get
the part about frm as a variable or object identified in the
procedures
declaration (yourfunction(frm As Form), but how do I tie it to the
actual
object (my form) ?

Also, in this case I'm not sure what is being referenced in
"Section(0)." Is
that a section on a form?

Simply stated what I need is a procedure that takes a constant value
or
condition (condition A; <=0, or condition B; >=1), and returns
either
vbaRed
or vbaWhite accordingly. Then for each text or combo box on any of
my
forms I
want to call that procedure to set the fore and back color to red or
white
based on that control's condition.

Could someone please help me out of my ignorance on this? There are
half a
dozen other instances where I feel a global procedure will simplify
my
code,
but I'm struggling with the constructs at the most basic level; when
and
where to Dim/declare variables, and where and how to call the
procedure
for a
control at the form level.

I hope these questions make sense.I don't even know enough to know
what
I
don't know.and that's never a good thing.

Best regards and thank you in advance for your help and patience.

RL

 

[Dale Fye]

In your sub CondFormat, remove the (frm as Form) declaration.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Hi Dale,
That's an excellent approach! I hadn't consdiered that the field would still
be editable, and I don't want that. I'll make those changes.

As of now, I'm working with just one combo box to get the procedures to
work, and I've got it to where it will highlight in red, but only after I
change the controls value (as I have a procedure on the control's After
Update event).

I can't get it to change backcolor back to white though, but that's probably
because I haven't incorporated the <=0 as the second condition (I couldn't
see how to write the logic statement; If Then, ElseIf, etc.) in the subs
logic.

When I incorporated the subs and tried to call the procedure by inserting
the procedure name in the form object module, I get an error message:

Compile Error - Argument Not Optional.

The Form's Current event procedure title is: Private Sub Form_Current()

In that sub I have included: Call CondFormat (but I get the compile error
unless commented out, and only the After Update sub fires).

Here are the three procedures:

Private Sub CondFormat(frm As Form)

Dim CTRL As Control

For Each CTRL In frm.Controls
If CTRL.Tag = "Conditional" Then Call FormatControl(CTRL)

Next
End Sub
Note: in the above, I added the frm As Form def, because the procedure
wouldn't find the "In Me.Controls" statement.


Private Sub FormatControl(CTRL As Control)

Dim lngBlack As Long, lngRed As Long, lngYellow As Long, lngWhite As Long
'Dim CTRL As Control (commented out for a duplicate in scope)

lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)

If CTRL.Value >= 1 Then
CTRL.ForeColor = lngBlack
CTRL.BackColor = lngRed

Else
CTRL.foreclor = lngWhite
CTRL.BackColor = lngWhite

End If

End Sub


Private Sub cboHourLimitID_AfterUpdate()
Call FormatControl(Me.cboHourLimitID)

End Sub


Note: I have entered the text; Conditional in the Tag for each of the
controls but I don't know if it needed any operators or special handling.

I've probably misinterpreted or misapplied what you suggested for the
procedures. I also noticed that some procedures I've seen have declarations
have Dim something As Access.Control, vs. Dim something As Control. I'm not
sure what the difference is, but perhaps you could educate me on when it is
appropriate to use one or the other.

I appreciate your guidance and your patience in helping me with this "small"
problem.

Best regards,

RL

Robert.

You might want to consider just hiding the control (setting it's visible
property to False), or locking the control as well (for the white on white).
Just because the text is the same color as the background does not mean that
the user cannot type in that control.

Rather than display an non-sensical value, why not just display "N/A", and
leave it at that?

Dale

Hi Dale,
Thank you! This looks like exactly what I need! I'll try it out and reply
again.

BTW the simple answer for setting back and fore color to white is that I
don't know what I'm doing.yet. I have been using that function to hide
text
when it doesn't meet either condition. That scheme may change now with
this
new approach, but the idea is that some controls will be empty on purpose.

I'm using the text and combo boxes to display measurement points, and then
doing some math to show an item's status as it accrues the measurement
type
against a prescribed limit.

Example

1) A measurement value in hours, cycles, months, or years (as a
multiplier)
2) A running total to date
3) A running or remaining "balance" against the defined limit (each 100
hours, each 100 cycles, each 6-mos, etc.)

On my form I show all the measurement categories and type all of the time.
In filtered view though, where I may be looking at the Hours measurements,
that same item may not also be measured in Cycles. When it is not, its
measurement parameters are either Null or Empty. To avoid a returned
#Error
on Null Propagation I've set them up with Nz functions, which causes them
to
return a nonsensical value (a negative number). I don't get the #Error
that
way, but then I hide the displayed value when it has no bearing.

Thank you again for offering up a solution for me! I'm anxious to try it
out
and report back.

(Also, I'm trying to post this reply so the discussion string stays in
order
and without <snipping> your input...but I don't know if I'm doing it
correctly.)

Best regards,
RL


:

Robert,

You could create a subroutine that you call from the Forms Current event,
that loops through all of the controls on the form, checks to see if they
are supposed to have this formatting (I'd set the value of each controls
Tag
property to some preset value), and if so, implement the code. It might
look something like:

Private Sub subCondFormatting

Dim ctrl as control

For each ctrl in me.controls
if ctrl.Tag = "Conditional" then Call FormatControl(ctrl)
Next
End sub
Private Sub FormatControl(ctrl as control)

if ctrl.Value >= 1 then
ctrl.forecolor = lngBlack
else
ctrl.Forecolor = lngWhite
endif
end sub

Then, you will also need to call the FormatControl subroutine from the
AfterUpdate event of each of the controls, something like:

Private Sub txt_Text1_AfterUpdate

Call FormatControl(me.txt_Text1)

End Sub

But now that I think about it, why would you want the controls forecolor
and
backcolor to both be white?

HTH
Dale

Hi Dale,
I have used Conditional Formatting but it's a lot of work at the form
level,
and if I change the condition variable I would rather do that from a
global
and have it fed to the variety of forms in my db. Conditional
Formatting
also
seems to load up the form and slow it down. I still use that feature,
but
not
very often.

Thanks for the quick reply though!

RL

:

Have you looked at conditional formatting?

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

Hi All;
I'm using Access 2007, Vista Business, 1 Gb ram

I hope there is a simple solution to my issue. I have a form with
20+
text
and combo boxes. I want their fore and back colors to change based
on
their
value. As it is now, I'm using a Case Select statement for each of
them
in
the form's Current event. But that seems too busy for what must be a
global
procedure solution.

Code now (for one combo box control, others same):
Select Case Me.cboMonthLimitID
Case Is <= 0
Me.cboMonthLimitID.ForeColor = lngWhite
Me.cboMonthLimitID.BackColor = lngWhite
Case Is >= 1
Me.cboMonthLimitID.ForeColor = lngBlack
Me.cboMonthLimitID.BackColor = lngYellow
Case Else
Me.cboMonthLimitID.ForeColor = lngWhite
Me.cboMonthLimitID.BackColor = lngWhite
End Select

The above code works okay, although I have struggled with some
fields
remaining highlighted after update (when I want them to revert to
white
fore/back color when not = to a stated value).

I would prefer to use a global function or sub that would allow me
to
call
it from each named control in the form's Current event procedure.
But I
don't
know how or what to declare (Dim) in the global procedure or the
calling form.

I have built and used other global or public functions and subs, but
only
ones that included the named controls within.

Can a global procedure be called for a named control, without having
to
declare each named control in the global procedure? (i.e., I would
like
to
have something that lets me call the global procedure to without
having
to
declare each control by name so I can use the routine for all of my
forms and
reports.)

Also, what is the syntax used to call the global procedure at the
control in
the form's Current event procedure? (i.e.,
DoCmd.RunCommand.Me![control
name]
gGetColor (as the global procedure name.)

I did find in another post this snippet from Marshall B., but I'm
not
clear

 

[Robert5833]

Hi Dale,
Thank you for pointing out my error there. I had been going back and forth
trying to manage various errors, and didn't take it back to the code you had
originally provided.

I've done that now, and it works perfectly!

In a previous post I mentioned that I had some persistant backcolor issues,
where the controls weren't reverting back to white. I've found the problem in
my logic statements, and those are now fixed.

I also noted that, as one would expect, only those controls which have the
Tag statement "Conditional" are affected by the procedures. That is very
handy, as I have more that a few variables depending on the measurement type.
With that little bit of knowledge, I can build and run a couple different
procedure variations and change all the procedure outcomes from a single
point.

Very helpful indeed!

Thanks again, and I appreciate your patience with me as I learn and grow in
my knowledge.

These discussion groups have been a tremendous help to me; and I always
scour previous posts to see if an answer has already been given by the
gracious participants and supporters here.

I hope I can look forward to your support, and the continued support of this
group.

Best regards,
Robert

 

[Dale Fye]

You're welcome.
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.