back color - full answer

  • Thread starter Thread starter mark r
  • Start date Start date
M

mark r

The last person who answered this, J Boyce, wrote 2 words -
----- conditional formatting --- I guess more words were
to expensive. If you read below, you will see that I am
too novice to know what he meant. Do You?

I have a form with 200 fields
there are maybe 50 fields I consider critical for the user.
so I want those fields to have a red back color if they
are null.
Once they enter data into the field or click on it, the
back color will change to white.

I am using CHANGE() and CLICK() for each individual field.
3 questions:

1. Is there a more efficient way to code this rather than
100 individual private subs?

2. if the user moves to the field using tab, can I use
gotfocus() to change the back color to white.

3. If I the user wants a
commandbutton_change_anyredback_color_to_ white,
how could I code that?

thanks
..
 
The last person who answered this, J Boyce, wrote 2 words -
----- conditional formatting --- I guess more words were
to expensive.
Click to expand...

Perhaps he expected you to check in online help, or do some more research of
your own: eg. search for that term on the web?

Confucious said: "Far better to teach a man to cook, than to deliver a
completed comestible onto his plate..."

HTH,
TC
 
mark said:
The last person who answered this, J Boyce, wrote 2 words -
----- conditional formatting --- I guess more words were
to expensive. If you read below, you will see that I am
too novice to know what he meant. Do You?

I have a form with 200 fields
there are maybe 50 fields I consider critical for the user.
so I want those fields to have a red back color if they
are null.
Once they enter data into the field or click on it, the
back color will change to white.

I am using CHANGE() and CLICK() for each individual field.
3 questions:

1. Is there a more efficient way to code this rather than
100 individual private subs?

2. if the user moves to the field using tab, can I use
gotfocus() to change the back color to white.

3. If I the user wants a
commandbutton_change_anyredback_color_to_ white,
how could I code that?
Click to expand...



Well, Jeff's answer was a little brief, but it is a way to
achieve the result you want in your first question. If you
select one of your text boxes, then drop down the Format
menu, you'll see the Conditional Formatting item. I'd be a
little concerned about the performance on asking CF to check
50 controls though. When considering your other two
questions, I think you're going to need to use code in a
(Function) procedure to give you more flexibility.

You don't really need to have an event procedure for each
control. You can set the OnClick property to
=yourfunction() instead of [Event Procedure] so you only
need one function to do the job. Here's a guess at some air
code that I think might be what you want:

Function ChkRed(Optional bolForce As Boolean)
With Me.ActiveControl
If IsMissing(bolForce) Then
If IsNull(.Value) Then
.BackColor = vbRed
Else
.BackColor = vbWhite
End If
ElseIf bolForce <> 0 Then
.BackColor = vbRed
Else
.BackColor = vbWhite
End If
End With
End Function

Then you can set all the text box's GotFocus property to:

=ChkRed(False)

Or maybe you want to set the OnChange (don't forget the
Change event fires on every keystroke) property to:

=ChkRed()

If your form allows you to move from one record to another,
then you need to run some other code to check each of the 50
text boxes. This can be easier to do if you name the text
boxes something like txtRqd1, txtRqd2, ..., txtRqd50 and use
code like:
For k = 1 to 50
With Me("txtRqd" & k)
If IsNull(.Value) Then
.BackColor = vbRed
Else
.BackColor = vbWhite
End If
End With
Next k

Or, if changing the names of the controls is an issue, you
can set each text box's Tag property to something like
ChkRed and use this kind of code:

For Each ctl In Me.Controls
If ctl.Tag = "ChkRed " Then
If IsNull(ctl.Value) Then
ctl.BackColor = vbRed
Else
ctl.BackColor = vbWhite
End If
End If
Next k
 
check out MB's response................that's a real man's response......you know the kind...someone answering to actually be helpful, has pride of workmanship, and isn't trying to waste anyone's time.
 
well I have learned alot playing with all these approaches.
I got the first few working, but it turns out I need the
last one because my form does go to other records.

I don't think I understand a few basics, causing some bugs.
SUB NEWCOLOR
For Each ctl In Me.Controls
Click to expand...

can I literally type: ctl
If ctl.Tag = "ChkRed " Then
Click to expand...

do you use <> or the equal sign here?
on the tag line in properties do I type = "ChkREd" or
leave out the equal sign?
If IsNull(ctl.Value) Then
ctl.BackColor = vbRed
Else
ctl.BackColor = vbWhite
End If
End If
Next k
Click to expand...

I don't need a dim statement?
k doesn't have to have an expression assigned to it?
END SUB
--
Click to expand...


Afterupdate COMBOchangerecord
CALL NEWCOLOR


so when the user moves the form to the next record
the color is analyzed

well nothing is happening, I get no error and no color
change.






Marsh
MVP [MS Access]
.
Click to expand...
 
I am still working on your notion and AM MAKING PROGRESS.
Generally, it is working well. I have a hiccup:

--------------------------
Please read through some of my VB code below, to get an
idea of some of the functionality invovled with
my "scenario".


The whole "issue" began when I got an error message:
along the lines of "the database engine expects .ADDNEW
or .EDIT when Update or Cancel event is used."

When I did not include the .edit aspect of
the code in the sub routine, I got that error message.
I don't get an error now that I included this
rs=recordset and .Edit piece of code. But I am not
sure of what I am really doing and not sure if I am doing
something to my tables that I don't really want.

What I want is for certain critical fields on the form to
have a red backcolor if the field ISNULL, otherwise
white. So I am not sure why I need a .EDIT or .ADDNEW
method since I am not changing table values.

I am having my LOOKUPCOMBOBUTTON_AFTERUPDATE function
check the backcolors after it does eveerything else it
does so that the colors are all correct once I move
forward to a different record. That is part of the reason
I was getting that error. But this combobutton has
a .EDIT method. And I put the .EDIT into the called
subroutine to eliminate the error.

You can see I have only enough knowledge to be dangerous.

Can you clear the fog for me?



Sub NEWSCREENCHANGECTLCOLOR()
For Each ctl In Me.Controls
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
With rs

If ctl.Tag = "Checkred" Then
If IsNull(ctl.Value) Then
ctl.BackColor = vbRed
Else
ctl.BackColor = vbWhite
End If
End If
.Edit
'.Close
'Me.Bookmark = .Bookmark
End With
'rs.Close
Next ctl
End Sub

Function Chkcolor()
' critical fields are red background; this fnc changes
them to white once no longer NULL
With Me.ActiveControl
If IsNull(.Value) Then
.BackColor = vbRed
Else
.BackColor = vbWhite
End If
End With
End Function

Private Sub commnewscrn_Click()
On Error GoTo Err_commnewscrn_Click
For Each ctl In Me.Controls
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
With rs
if ctl.Tag = "Checkred" Then
If IsNull(ctl.Value) Then
ctl.BackColor = vbRed
Else
ctl.BackColor = vbWhite
End If
End If
.Edit
'.Close
'Me.Bookmark = .Bookmark
End With
'rs.Close
Next ctl
Exit_commnewscrn_Click:
Exit Sub

Err_commnewscrn_Click:
MsgBox Err.description
Resume Exit_commnewscrn_Click

End Sub

Private Sub combo39_AfterUpdate()
Rem Go TO Select records ONLY PATIENTS THAT Presented
Today
Rem Order: order presented in clinic today

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
With rs
.FindFirst "ID = " & Me.Combo39
.Edit 'Mode
!combodate = Date
.Update
Me.Bookmark = .Bookmark
End With
Me.Combo39.Requery
Me.Combo43.Requery
Me.List_todaysrecs.Requery
Me.List_HX_all.Requery
Me.Combo47.Requery
Call NEWSCREENCHANGECTLCOLOR
Rem Me.Comboselectdate.Requery
End Sub
 
Back
Top