• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 533
  • Last Modified:

Make textbox value red

I am trying to make the value in a date textbox turn red if certain criteria exists.  Here is my code but it is not turning red.  I have this in the onformet event of the detail section of the report.

    If Me.txtFinApprovalDueDate < Now() And Me.txtFinApprovalRecdDate = "" Then
        Me.txtFinApprovalDueDate.ForeColor = vbRed
    End If

What is wrong with this?
0
SteveL13
Asked:
SteveL13
  • 5
  • 2
  • 2
  • +1
1 Solution
 
GRayLCommented:
Both criteria have to be met at the same time for the If statement to execute.  Normally, a textbox, formatted as a date, can never be an empty string.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
try:

    If Me.txtFinApprovalDueDate < Now() And Nz(Me.txtFinApprovalRecdDate,"") = "" Then
        Me.txtFinApprovalDueDate.ForeColor = vbRed
    End If

0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Me.txtFinApprovalRecdDate if missing gives you Null not ""

The above code schecks for both.

Mike
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Dezzar82Commented:
re: the usage of "now()" which refers to this exact point in time (Date & Time).  If you just want to compare dates then use "Date" instead

Try this...

Dim AppRcvd As Double
AppRcvd = Nz(Me.txtFinApprovalRecdDate,0)

If Me.txtFinApprovalDueDate < Date() And AppRcvd = 0 Then
       Me.txtFinApprovalDueDate.ForeColor = vbRed
End If

Open in new window


0
 
GRayLCommented:
Strange how I just noticed the two text boxes are different fields - thanks Mike.  Ding!!!!
0
 
lee555J5Commented:
Why not use conditional formatting?

Expression is   Me.txtFinApprovalDueDate < Now() And Nz(Me.txtFinApprovalRecdDate, 0) = 0

and set the Font Color to red.
If the date textboxes are bound, I would use the field value rather than txtFinApprovalDueDate.Value and txtFinApprovalRecdDate.Value. If they are unbound, you don't have a choice but to do it the way you did.

Expression is   FinApprovalDueDate < Now() And Nz(FinApprovalRecdDate, 0) = 0

and set the Font Color to red.
I like Dezzar82's idea to use Date() instead of Now().

Lee
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
re:> I like Dezzar82's idea to use Date() instead of Now().

I agree with you unless SteveL13 wants to compare time of delivery also.

Mike
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Hi SteveL13,

The race flag of EE means the first correct answere gets the points. My solution is posted before the also good solution from Dezzar82.

Where is my points? If you liked something about his post, you have the option of spilitting the points not ignoring my solution.

Regards,

Mike
0
 
lee555J5Commented:
The reason I suggested conditional formatting (CF) is it always handles both True and False. No code suggestion so far resets the font color back to default. The attached modified code should fix this, but CF is much simpler and clearer. Also, CF works with the default color and the conditional color, rather than hard-coding both colors. CF is especially useful when using continuous forms.

Lee
Dim AppRcvd As Double
AppRcvd = Nz(Me.txtFinApprovalRecdDate,0)

If Me.txtFinApprovalDueDate < Date() And AppRcvd = 0 Then
    Me.txtFinApprovalDueDate.ForeColor = vbRed
Else
    Me.txtFinApprovalDueDate.ForeColor = vbBlack
End If

Open in new window

0
 
Mike EghtebasDatabase and Application DeveloperCommented:
lee555J5,

You are correct. Then again, after having  Nz(Me.txtFinApprovalRecdDate,"") = "" the code could be easily could be changed to:

If Me.txtFinApprovalDueDate < Now() And Nz(Me.txtFinApprovalRecdDate,"") = "" Then
        Me.txtFinApprovalDueDate.ForeColor = vbRed

else
       Me.txtFinApprovalDueDate.ForeColor =...
    End If

This is where splitting point comes to play.

Mike
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 5
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now