VBA If..Then..Else to change text box properties

  • Thread starter gwplotts via AccessMonster.com
  • Start date
G

gwplotts via AccessMonster.com

I have a series of 7 text boxes and corresponding 7 check boxes. I want to
change the properties of the text boxes when the checkbox is not checked, and
also display an alert message. This is what I'm doing now

If Task10ADueDate < Now And Task10AComplete = False Then
Task10ADueDate.BorderColor = lngRed
Task10ADueDate.ForeColor = lngYellow
Task10ADueDate.BackColor = lngRed
Task10ADueDate.FontBold = True
lblOverdue10A.Visible = True
Else
Task10ADueDate.BorderColor = lngBlack
Task10ADueDate.ForeColor = lngBlack
Task10ADueDate.BackColor = lngWhite
Task10ADueDate.FontBold = False
lblOverdue10A.Visible = False
End If

I do this for each of the text boxes. The problem is that I get either the
alert box and only one of the text boxes to change/appear, or by placing all
of the End If's at the end of the code, I get multiple text boxe property
changes, but no alert box.

Any help would be appreciated. Thanks in Advance

revgwplotts@snetDOTnet
 
W

Wayne Morgan

Is lblOverdue10A your "alert box" or are you doing something else?

Assuming that the label is your "alert box", then the problem is that the
last If statement you run will determine the visible state of this label.
The others will also, but it will be so fast, you probably won't be able to
see it. The final state of the label will be set by the last If statement.

The easiest way to handle this may be a "flag" variable. You would set it in
each of the If statements, then check its value at the end to set the
label's visibility.

Example:
Dim bolFlag As Boolean
bolFlag = False
If Task10ADueDate < Now And Task10AComplete = False Then
Task10ADueDate.BorderColor = lngRed
Task10ADueDate.ForeColor = lngYellow
Task10ADueDate.BackColor = lngRed
Task10ADueDate.FontBold = True
bolFlag= True
Else
Task10ADueDate.BorderColor = lngBlack
Task10ADueDate.ForeColor = lngBlack
Task10ADueDate.BackColor = lngWhite
Task10ADueDate.FontBold = False
'You don't need the next line, the "flag" is False unless you set it to True
' lblOverdue10A.Visible = False
End If
'Other Ifs here, with the same changes as above.
lblOverdue10A.Visible = bolFlag

bolFlag will be False unless one if the If statements is True, then bolFlag
will also be True.
 
K

Klatuu

When you say alert box, do you mean lblOverdue10A? or is there a Message Box
later in the code?
If you have this same code for each text box one after the other, I don't
know why you would not see all the labels appear and the text boxes change
appearance.
Have you stepped through the code in debug mode to see where it is going?

Since you posted only part of the code, it is difficult to know if there is
a logic problem. Is it possible another event is being fired?
 
S

stefan hoffmann

hi,
I have a series of 7 text boxes and corresponding 7 check boxes. I want to
change the properties of the text boxes when the checkbox is not checked, and
also display an alert message.
Click to expand...
You like to do this if you are unchecking the checkbox?

Private Function SetTextBoxState(ACheckBox As Access.CheckBox, _
ALabel As Access.Label, _
ATextBox As Access.TextBox _
) As Boolean

If ATextBox.Value < Now And ACheckBox.Value = False Then
ATextBox.BorderColor = lngRed
ATextBox.ForeColor = lngYellow
ATextBox.BackColor = lngRed
ATextBox.FontBold = True
ALabel.Visible = True
Else
ATextBox.BorderColor = lngBlack
ATextBox.ForeColor = lngBlack
ATextBox.BackColor = lngWhite
ATextBox.FontBold = False
ALabel.Visible = True
End If

SetTextBoxState = True

End Sub

And type in each AfterUpdate event
=SetTextBoxState([yourChecboxName];[yourLableName];[yourTextboxName])
use the corresponding control names.
I do this for each of the text boxes. The problem is that I get either the
alert box and only one of the text boxes to change/appear, or by placing all
of the End If's at the end of the code, I get multiple text boxe property
changes, but no alert box.
Click to expand...
I don't see any alert box here.


mfG
--> stefan <--
 
K

Klatuu

Good solution, stefan. It is a better way. I do see what I think may be a
referencing problem; however. The way you say to call it with the names:
=SetTextBoxState([yourChecboxName];[yourLableName];[yourTextboxName])
may not work. You have the references declared in the function as specific
object types, so the calling code needs to pass object references rather than
names. I would think:
=SetTextBoxState(Me.ChecboxName, Me.LableName, Me.TextboxName)
would be the correct syntax.

stefan hoffmann said:
hi,
I have a series of 7 text boxes and corresponding 7 check boxes. I want to
change the properties of the text boxes when the checkbox is not checked, and
also display an alert message.
Click to expand...
You like to do this if you are unchecking the checkbox?

Private Function SetTextBoxState(ACheckBox As Access.CheckBox, _
ALabel As Access.Label, _
ATextBox As Access.TextBox _
) As Boolean

If ATextBox.Value < Now And ACheckBox.Value = False Then
ATextBox.BorderColor = lngRed
ATextBox.ForeColor = lngYellow
ATextBox.BackColor = lngRed
ATextBox.FontBold = True
ALabel.Visible = True
Else
ATextBox.BorderColor = lngBlack
ATextBox.ForeColor = lngBlack
ATextBox.BackColor = lngWhite
ATextBox.FontBold = False
ALabel.Visible = True
End If

SetTextBoxState = True

End Sub

And type in each AfterUpdate event
=SetTextBoxState([yourChecboxName];[yourLableName];[yourTextboxName])
use the corresponding control names.
I do this for each of the text boxes. The problem is that I get either the
alert box and only one of the text boxes to change/appear, or by placing all
of the End If's at the end of the code, I get multiple text boxe property
changes, but no alert box.
Click to expand...
I don't see any alert box here.


mfG
--> stefan <--
Click to expand...
 
G

gwplotts via AccessMonster.com

This is the entire code segment. I need the format changes to take place if
any date is overdue and the complete box is not checked, and the alert text
box to appear when any one or more of the overdue contitions exist.

Private Sub Form_Current()
Dim lngRed As Long, lngBlack As Long, lngWhite As Long, lngYellow As Long
lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)
lngWhite = RGB(255, 255, 255)
lngYellow = RGB(255, 255, 0)

'Section 10 A
If Task10ADueDate < Now And Task10AComplete = False Then
Task10ADueDate.BorderColor = lngRed
Task10ADueDate.ForeColor = lngYellow
Task10ADueDate.BackColor = lngRed
Task10ADueDate.FontBold = True
lblOverdue10A.Visible = True
Else
Task10ADueDate.BorderColor = lngBlack
Task10ADueDate.ForeColor = lngBlack
Task10ADueDate.BackColor = lngWhite
Task10ADueDate.FontBold = False
lblOverdue10A.Visible = False
End If
'Section 10 B
If Task1BDueDate < Now And Task1BComplete = False Then
Task1BDueDate.BorderColor = lngRed
Task1BDueDate.ForeColor = lngYellow
Task1BDueDate.BackColor = lngRed
Task1BDueDate.FontBold = True
lblOverdue10B.Visible = True
Else
Task1BDueDate.BorderColor = lngBlack
Task1BDueDate.ForeColor = lngBlack
Task1BDueDate.BackColor = lngWhite
Task1BDueDate.FontBold = False
lblOverdue10B.Visible = False
End If
If Task2BDueDate < Now And Task2BComplete = False Then
Task2BDueDate.BorderColor = lngRed
Task2BDueDate.ForeColor = lngYellow
Task2BDueDate.BackColor = lngRed
Task2BDueDate.FontBold = True
lblOverdue10B.Visible = True
Else
Task2BDueDate.BorderColor = lngBlack
Task2BDueDate.ForeColor = lngBlack
Task2BDueDate.BackColor = lngWhite
Task2BDueDate.FontBold = False
lblOverdue10B.Visible = False
End If

If Task3BDueDate < Now And Task3BComplete = False Then
Task3BDueDate.BorderColor = lngRed
Task3BDueDate.ForeColor = lngYellow
Task3BDueDate.BackColor = lngRed
Task3BDueDate.FontBold = True
lblOverdue10B.Visible = True
Else
Task3BDueDate.BorderColor = lngBlack
Task3BDueDate.ForeColor = lngBlack
Task3BDueDate.BackColor = lngWhite
Task3BDueDate.FontBold = False
lblOverdue10B.Visible = False
End If

If Task4BDueDate < Now And Task4BComplete = False Then
Task4BDueDate.BorderColor = lngRed
Task4BDueDate.ForeColor = lngYellow
Task4BDueDate.BackColor = lngRed
Task4BDueDate.FontBold = True
lblOverdue10B.Visible = True
Else
Task4BDueDate.BorderColor = lngBlack
Task4BDueDate.ForeColor = lngBlack
Task4BDueDate.BackColor = lngWhite
Task4BDueDate.FontBold = False
lblOverdue10B.Visible = False
End If
If Task5BDueDate < Now And Task5BComplete = False Then
Task5BDueDate.BorderColor = lngRed
Task5BDueDate.ForeColor = lngYellow
Task5BDueDate.BackColor = lngRed
Task5BDueDate.FontBold = True
lblOverdue10B.Visible = True
Else
Task5BDueDate.BorderColor = lngBlack
Task5BDueDate.ForeColor = lngBlack
Task5BDueDate.BackColor = lngWhite
Task5BDueDate.FontBold = False
lblOverdue10B.Visible = False
End If

'Section 10 C
If Task10CDueDate < Now And Task10CComplete = False Then
Task10CDueDate.BorderColor = lngRed
Task10CDueDate.ForeColor = lngYellow
Task10CDueDate.BackColor = lngRed
Task10CDueDate.FontBold = True
lblOverdue10C.Visible = True
Else
Task10CDueDate.BorderColor = lngBlack
Task10CDueDate.ForeColor = lngBlack
Task10CDueDate.BackColor = lngWhite
Task10CDueDate.FontBold = False
lblOverdue10C.Visible = False
End If



End Sub
 
G

gwplotts via AccessMonster.com

Wayne,
Your solution worked just the way I needed it to. Thanks for all your help
and quick response


Wayne said:
Is lblOverdue10A your "alert box" or are you doing something else?

Assuming that the label is your "alert box", then the problem is that the
last If statement you run will determine the visible state of this label.
The others will also, but it will be so fast, you probably won't be able to
see it. The final state of the label will be set by the last If statement.

The easiest way to handle this may be a "flag" variable. You would set it in
each of the If statements, then check its value at the end to set the
label's visibility.

Example:
Dim bolFlag As Boolean
bolFlag = False
If Task10ADueDate < Now And Task10AComplete = False Then
Task10ADueDate.BorderColor = lngRed
Task10ADueDate.ForeColor = lngYellow
Task10ADueDate.BackColor = lngRed
Task10ADueDate.FontBold = True
bolFlag= True
Else
Task10ADueDate.BorderColor = lngBlack
Task10ADueDate.ForeColor = lngBlack
Task10ADueDate.BackColor = lngWhite
Task10ADueDate.FontBold = False
'You don't need the next line, the "flag" is False unless you set it to True
' lblOverdue10A.Visible = False
End If
'Other Ifs here, with the same changes as above.
lblOverdue10A.Visible = bolFlag

bolFlag will be False unless one if the If statements is True, then bolFlag
will also be True.
I have a series of 7 text boxes and corresponding 7 check boxes. I want to
change the properties of the text boxes when the checkbox is not checked,
Click to expand...
[quoted text clipped - 24 lines]
revgwplotts@snetDOTnet
Click to expand...
Click to expand...
 
K

Klatuu

I see no code that presents an "alert" box. I think you mean Message Box.
That you would have to do by setting a flag that would present a message box
if any overdue conditions exist. I will use your existing code, but I think
you should take a look at stefan hoffmann's solution. It will save a bunch
of code. Below is an example using one of your conditionals as an example.
You would need to add it to each:
Dim blnFoundOverDue

'Put the following line once at the beginning of the sub
blnFoundOverDue = False
If Task10CDueDate < Now And Task10CComplete = False Then
Task10CDueDate.BorderColor = lngRed
Task10CDueDate.ForeColor = lngYellow
Task10CDueDate.BackColor = lngRed
Task10CDueDate.FontBold = True
lblOverdue10C.Visible = True
blnFoundOverDue = True
Else
Task10CDueDate.BorderColor = lngBlack
Task10CDueDate.ForeColor = lngBlack
Task10CDueDate.BackColor = lngWhite
Task10CDueDate.FontBold = False
lblOverdue10C.Visible = False
End If

If blnFoundOverDue Then
MsbBox "OverDue Items Found"
End If
 
S

stefan hoffmann

hi,
Good solution, stefan. It is a better way. I do see what I think may be a
referencing problem; however. The way you say to call it with the names:
=SetTextBoxState([yourChecboxName];[yourLableName];[yourTextboxName])
may not work. You have the references declared in the function as specific
object types, so the calling code needs to pass object references rather than
names. I would think:
=SetTextBoxState(Me.ChecboxName, Me.LableName, Me.TextboxName)
would be the correct syntax.
Click to expand...
Technically this is correct, but the references are passed, if i use the
simple method with [ControlName] only.


mfG
--> stefan <--
 
Top