Need Help with ForeColor Change

[bugman]

What I'm wanting to do should be very simple, but for the love of all things, I can't figure it out.

I have 3 textbox's on my MainForm:
txtTotalCharged
txtTotalPaid
txtAmountDue

The first 2 textbox's are calculations from Sub1 and Sub2 and Sub3.

What I am wanting to do is when txtAmountDue is > 0 then make txtAmountDue ForeColor be red. And then when it's < 0 make the ForeColor black.

As I Click on Each row in Sub1 to see what the txtAmountDue is on the MainForm, I want the txtAmountDue to reflect the right ForeColor. I have tried this using the If...Then Statement and Select Case. I don't know whether to place the code on MainForms OnCurrent Event or on Sub1 OnCurrent Event or on one of the textbox GotFocus Event within Sub1 or if I am writing the correct code at all.

I would prefer not to use the Conditional Formating button.

If someone could show me how to do this thru Code or point me towards a sample db, I would greatly appreciate it.

Thanks,
Tom

 

[MarkK]

You can set this in the Format property of the control, which'll be way simpler.
In Access 2002 - VBA Help you can find a good article by searching on "format property - number and currency data types"
Here's a part of that article...

Custom number formats can have one to four sections with semicolons as the list separator. Each section contains the format specification for a different type of number.

Section Description
First: The format for positive numbers.
Second: The format for negative numbers.
Third: The format for zero values.
Fourth: The format for Null values.


For example, you could use the following custom Currency format:

$#,##0.00[Green];($#,##0.00)[Red];"Zero";"Null"

 

[missinglinq]

Simplest way, assuming Access 2000 or later, is to use Conditional Formatting from the Menu.

Goto Format - Conditional Formatting

From the drop downs select

Field Value Is

then

Greater Than

then enter zero

0

Now click on the ForeColor icon and set it to red.

You're done.

 

[bugman]

You can set this in the Format property of the control, which'll be way simpler.
In Access 2002 - VBA Help you can find a good article by searching on "format property - number and currency data types"
Here's a part of that article...

Thanks for your help with the conditioning ForeColor property. It works perfect.

I modifed it just a bit and here is the result.

$#,##0.00" Amount Owed"[Red];$#,##0.00" Amount Of Credit"[Black];"Zero Balance";"Null"

And once again I appreciate you taking time to READ my post.

Have a great weekend.

Tom

P.S. I still would love to know how to do it using VBA.

 

[MarkK]

Tom. Thanks for posting back with your appreciation and success.
In VBA, simply set the Format property of the control to an appropriate string...

Private Sub SomeEvent_Handler()
  [COLOR="Green"]'assigns ctrl format, replacing ~ with "[/COLOR]
  me.ctrl.format = strings.replace( _
    "$#,##0.00~ Amount Owed~[Red];" & _
    "$#,##0.00~ Amount Of Credit~[Black];" & _
    "~Zero Balance~;" & _
    "~Null~", _
    "~", chr(34))
End Sub

Mark