Text Box Conditions

[balistikb]

I need help. I will try to explain this the best way I can. I have a form, on this form I got many text boxes. I would like to change the color of the information inside a text box (TargetResolutionDate) to red if the date in that box is past todays date. But I only want that to happen if what is in a combo box (StatusID) is = to Open. In other words, make the target resolution date red if the issue is open and past due.
Here is what I had started and I was trying to fumble through it but it seems to stay red if it is past due even of I comment out all this code.
Private Sub Form_Open(Cancel As Integer)

If Me!StatusID = 1 Then 'Check textbox for Resolution date
If TargetResolutionDate < Now() Then
frm_Main_Issues!TargetResolutionDate.ForeColor = 255
ElseIf TargetResolutionDate > Now() Then
frm_Main_Issues!TargetResolutionDate.ForeColor = 0

End If
End If


End Sub

 

[ajdesalvo]

What is the RowSource of StatusID? Is ther only one column in the ComboBox? (1 for Open, 2 for Closed, etc.)

Assuming that there is only one column, try the following:

If Me!StatusID = 1 Then 'Check textbox for Resolution date
If TargetResolutionDate < Date() Then
frm_Main_Issues!TargetResolutionDate.ForeColor = 255
ElseIf TargetResolutionDate > Date() Then
frm_Main_Issues!TargetResolutionDate.ForeColor = 0
End If
Else
frm_Main_Issues!TargetResolutionDate.ForeColor = 0
End If

Hope this helped!

Anthony J. DeSalvo
President - ScottTech Software
&quot;Integrating Technology with Business&quot;

 

[balistikb]

Row source is a query and the bound column is 2. There is a name and then a number. I will try that code. But also, I commented out all the code to change the color but in my form, the color still changes to red. Why is that?

 

[balistikb]

I tried the code but it is giving me an object required error.

 

[ajdesalvo]

In the control's property for Forecolor, is that set to 0?

Now that I know what columnof the ComboBox is the bound column try:

If Me![StatusID].Column(1) = 1 Then
If frm_Main_Issues![TargetResolutionDate] < Date() Then
frm_Main_Issues![TargetResolutionDate].ForeColor = 255
ElseIf frm_Main_Issues![TargetResolutionDate] > Date() Then
frm_Main_Issues![TargetResolutionDate].ForeColor = 0
End If
Else
frm_Main_Issues![TargetResolutionDate].ForeColor = 0
End If

Good luck!


Anthony J. DeSalvo
President - ScottTech Software
&quot;Integrating Technology with Business&quot;

 

[balistikb]

Still gives me object required. Then it highlights the last line.
frm_Main_Issues![TargetResolutionDate].ForeColor = 0

Am I missing something?

 

[ajdesalvo]

Okay, replace all frm_Main_Issues![TargetResolutionDate] with Forms![frm_Main_Issues]![TargetResolutionDate]

If Me![StatusID].Column(1) = 1 Then
If Forms![frm_Main_Issues]![TargetResolutionDate] < Date() Then
Forms![frm_Main_Issues]![TargetResolutionDate].ForeColor = 255
ElseIf Forms![frm_Main_Issues]![TargetResolutionDate] > Date() Then
Forms![frm_Main_Issues]![TargetResolutionDate].ForeColor = 0
End If
Else
Forms![frm_Main_Issues]![TargetResolutionDate].ForeColor = 0
End If

Hope that helps!

Anthony J. DeSalvo
President - ScottTech Software
&quot;Integrating Technology with Business&quot;

 

[balistikb]

I have that code but it doesn't seem to execute the second If statement. No error, it just doesn't follow what I am asking it to do.

 

[ajdesalvo]

Have you plaed a break on the second If statement line? Once it stops there, highlight all of Forms![frm_Main_Issues]![TargetResolutionDate] and place your cursor over it to see what the value is.

You could try to dimension a variable for the value of Forms![frm_Main_Issues]![TargetResolutionDate] and use that in the If statement.

Dim TargDate as Date
TargDate = Forms![frm_Main_Issues]![TargetResolutionDate]

If Me![StatusID].Column(1) = 1 Then
If TargDate < Date() Then
Forms![frm_Main_Issues]![TargetResolutionDate].ForeColor = 255
ElseIf TargDate > Date() Then
Forms![frm_Main_Issues]![TargetResolutionDate].ForeColor = 0
End If
Else
Forms![frm_Main_Issues]![TargetResolutionDate].ForeColor = 0
End If



Anthony J. DeSalvo
President - ScottTech Software
&quot;Integrating Technology with Business&quot;

 

[balistikb]

I have two values in the drop down [StatusId] Open = 1
Closed = 2 and so on. How do I what waht column to include in the code?

 

[ajdesalvo]

If the first column is the word open or closed, and it is the bound column, then you could use that:


Dim TargDate as Date
TargDate = Forms![frm_Main_Issues]![TargetResolutionDate]

If Me![StatusID] = &quot;Open&quot; Then
If TargDate < Date() Then
Forms![frm_Main_Issues]![TargetResolutionDate].ForeColor = 255
ElseIf TargDate > Date() Then
Forms![frm_Main_Issues]![TargetResolutionDate].ForeColor = 0
End If
Else
Forms![frm_Main_Issues]![TargetResolutionDate].ForeColor = 0
End If


Anthony J. DeSalvo
President - ScottTech Software
&quot;Integrating Technology with Business&quot;

 

[balistikb]

Ok, it is still not working for me. I have copied the code exactly, placed it in the form open event and it is still not working. What could I be doing wrong? What else should I try? The code works with the first If statement but doesn't go into the second If statement that reads whether or not the targdate is less than today.
Also my bound column is column 1 and has a value of 1 or 2.

 

[ajdesalvo]

Place a break point on the line below:

If TargDate < Date() Then

then open the form. When the code stops, place the cursor over the TargDate variable. What is the value?


Anthony J. DeSalvo
President - ScottTech Software
&quot;Integrating Technology with Business&quot;

 

[balistikb]

Ok if I place the breakpoint on line &quot;If TargDate < Date() Then&quot; it doesn't stop. I could just flip through the records as normal.

 

[ajdesalvo]

Move the code to the Form's &quot;On Current&quot; event. You need to test every time the record changes in the form.

Anthony J. DeSalvo
President - ScottTech Software
&quot;Integrating Technology with Business&quot;

 

[CosmoKramer]

balistikb,

Try placing your code in your form's On Current event instead of On Open......



A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
Douglas Adams

 

[balistikb]

That seems to have done it. I had it in the wrong event.
THANK YOU!

 

[ajdesalvo]

No problem sorry it took so long!

Good luck!

Anthony J. DeSalvo
President - ScottTech Software
&quot;Integrating Technology with Business&quot;