BackColor Code!

B

Bob Vance

Im tring to get a text box to change its back color if these conditions are
meet
ckbEmailWarn = True
tbAmountOwe = More than 1 Or less than 1
tbEmailState = 30 days old

If Me.ckbEmailWarn = 0 And Me.tbAmountOwe <1 Or >1 And Me.tbEmailState =
Date() <30
Or
Me.ckbEmailWarn = 0 And Me.tbAmountOwe <1 Or >1 And Me.tbEmailState = Null
Then

Me.tbWarning.BackColor = ED1C24
Else
Me.tbWarning.BackColor = FFFFFF
 
J

Jeanette Cunningham

Hi Bob,
you can convert the color to a long instead of letters.

You can do a conversion.
lngColor = CLng(&HED1C24)

You can do it in the immediate window
?CLng(&HED1C24)

Use the long in your code for the back color instead of the letters.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
D

Dirk Goldgar

Bob Vance said:
Im tring to get a text box to change its back color if these conditions
are meet
ckbEmailWarn = True
tbAmountOwe = More than 1 Or less than 1
Click to expand...

Isn't that the same as "tbAmountOwe not equal to 1"?
tbEmailState = 30 days old

If Me.ckbEmailWarn = 0 And Me.tbAmountOwe <1 Or >1 And Me.tbEmailState =
Date() <30
Or
Me.ckbEmailWarn = 0 And Me.tbAmountOwe <1 Or >1 And Me.tbEmailState = Null
Then

Me.tbWarning.BackColor = ED1C24
Else
Me.tbWarning.BackColor = FFFFFF
Click to expand...

Your code, even allowing for improper syntax in places, doesn't seem to
correspond to the conditions you you stated. For example, you're checking
"ckbEmailWarn = 0", but that's equivalent to ckbEmailWarn = False, not True.
And you didn't say anything about tbEmailState being Null, but you have that
as an alternative in your code.

If you really want the specific conditions you stated, and only those, then
your code might be:

If (Me.ckbEmailWarn) _
And Me.tbAmountOwe <> 1 _
And DateDiff("d", Me.tbEmailState, Date()) >= 30 _
Then
Me.tbWarning.BackColor = ED1C24
Else
Me.tbWarning.BackColor = FFFFFF
End If

That is assuming that records should still meet your criteria if
tbEmailState is *more* than 30 days ago, not just if it's exactly 30 days
ago.

If you want to allow for Me.tbEmailState to be Null as well, you could amend
the code like this:

If (Me.ckbEmailWarn) _
And Me.tbAmountOwe <> 1 _
And ( _
DateDiff("d", Me.tbEmailState, Date()) >= 30 _
Or IsNull(Me.tbEmailState)) _
Then
Me.tbWarning.BackColor = ED1C24
Else
Me.tbWarning.BackColor = FFFFFF
End If

I don't know in what event(s) you intend to execute this code. Most lkely
would be the AfterUpdate events of any of the controls involved in the If
statement, and in the Current event of the form.
 
G

Gina Whipp

Bob,

Your code is a little confusing...

1. 0 = False, if you want True then use -1 or True.
2. More than 1 or less than 1 might be better as <>0 (not equal to zero)
3. 30 days old, 30 days old from todays date or some other date... where is
the date it's 30 days old from? What you wrote was Date less then 30? That
might need a little explaining because I'm not understanding what you mean.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
D

Dirk Goldgar

Dirk Goldgar said:
Your code, even allowing for improper syntax in places, doesn't seem to
correspond to the conditions you you stated. For example, you're checking
"ckbEmailWarn = 0", but that's equivalent to ckbEmailWarn = False, not
True. And you didn't say anything about tbEmailState being Null, but you
have that as an alternative in your code.

If you really want the specific conditions you stated, and only those,
then your code might be:

If (Me.ckbEmailWarn) _
And Me.tbAmountOwe <> 1 _
And DateDiff("d", Me.tbEmailState, Date()) >= 30 _
Then
Me.tbWarning.BackColor = ED1C24
Else
Me.tbWarning.BackColor = FFFFFF
End If

That is assuming that records should still meet your criteria if
tbEmailState is *more* than 30 days ago, not just if it's exactly 30 days
ago.

If you want to allow for Me.tbEmailState to be Null as well, you could
amend the code like this:

If (Me.ckbEmailWarn) _
And Me.tbAmountOwe <> 1 _
And ( _
DateDiff("d", Me.tbEmailState, Date()) >= 30 _
Or IsNull(Me.tbEmailState)) _
Then
Me.tbWarning.BackColor = ED1C24
Else
Me.tbWarning.BackColor = FFFFFF
End If

I don't know in what event(s) you intend to execute this code. Most lkely
would be the AfterUpdate events of any of the controls involved in the If
statement, and in the Current event of the form.
Click to expand...


And I didn't even notice what Jeanette pointed out: that your color values
are specified improperly. D'oh! If you want to specify them in
hexadecimal, as you did in your original post, you can prefix the values
with "&H":

Then
Me.tbWarning.BackColor = &HED1C24
Else
Me.tbWarning.BackColor = &HFFFFFF
End If
 
B

Bob Vance

Dirk Goldgar said:
And I didn't even notice what Jeanette pointed out: that your color
values are specified improperly. D'oh! If you want to specify them in
hexadecimal, as you did in your original post, you can prefix the values
with "&H":

Then
Me.tbWarning.BackColor = &HED1C24
Else
Me.tbWarning.BackColor = &HFFFFFF
End If



--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Oops Sorry this is on a continuios form so I dont think its going to
work..regards Bob
 
B

Bob Vance

Bob Vance said:
Oops Sorry this is on a continuios form so I dont think its going to
work..regards Bob
Click to expand...
I am getting it to work on a single form but not on a continuos form, code
below...Thanks Bob

If (Me.ckbachInvoice.value) = -1 And Me.tbAmountOwe.value <> 0 And
(DateDiff("d", Me.tbEmailState, Date) >= 30 Or IsNull(Me.tbEmailState) And
(Me.ckbachInvoice.value) = -1 And Me.tbAmountOwe.value <> O) Then
Me.tbWarning.BackColor = vbRed
Else
Me.tbWarning.BackColor = vbWhite
End If
 
D

Dirk Goldgar

Bob Vance said:
I am getting it to work on a single form but not on a continuos form, code
below...Thanks Bob

If (Me.ckbachInvoice.value) = -1 And Me.tbAmountOwe.value <> 0 And
(DateDiff("d", Me.tbEmailState, Date) >= 30 Or IsNull(Me.tbEmailState) And
(Me.ckbachInvoice.value) = -1 And Me.tbAmountOwe.value <> O) Then
Me.tbWarning.BackColor = vbRed
Else
Me.tbWarning.BackColor = vbWhite
End If
Click to expand...


That can't be the actual code, because that code will *not* work -- you have
the letter "O" in one place where you mean to have a zero.. However, you
are right that this whole approach won't work on a continuous form.
Instead, you must use conditional formatting to set the BackColor to red,
specifying "Expression Is" and an expression like this:

([ckbEmailWarn] <> 0) And ([tbAmountOwe] <> 0) And ((DateDiff("d",
[tbEmailState], Date()) >= 30) Or IsNull([tbEmailState]))

Please note that the above expression should all be entered on one line,
even though it will have been broken onto two lines by the newsreader.
 
B

Bob Vance

BRILLIANT Dirk, Thanks for you help ....Bob

Dirk Goldgar said:
Bob Vance said:
I am getting it to work on a single form but not on a continuos form,
code below...Thanks Bob

If (Me.ckbachInvoice.value) = -1 And Me.tbAmountOwe.value <> 0 And
(DateDiff("d", Me.tbEmailState, Date) >= 30 Or IsNull(Me.tbEmailState)
And (Me.ckbachInvoice.value) = -1 And Me.tbAmountOwe.value <> O) Then
Me.tbWarning.BackColor = vbRed
Else
Me.tbWarning.BackColor = vbWhite
End If
Click to expand...


That can't be the actual code, because that code will *not* work -- you
have the letter "O" in one place where you mean to have a zero.. However,
you are right that this whole approach won't work on a continuous form.
Instead, you must use conditional formatting to set the BackColor to red,
specifying "Expression Is" and an expression like this:

([ckbEmailWarn] <> 0) And ([tbAmountOwe] <> 0) And ((DateDiff("d",
[tbEmailState], Date()) >= 30) Or IsNull([tbEmailState]))

Please note that the above expression should all be entered on one line,
even though it will have been broken onto two lines by the newsreader.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
B

Bob Vance

Actually Dirk there is one problem , because
Bob Vance said:
BRILLIANT Dirk, Thanks for you help ....Bob

Dirk Goldgar said:
Bob Vance said:
Oops Sorry this is on a continuios form so I dont think its going to
work..regards Bob

I am getting it to work on a single form but not on a continuos form,
code below...Thanks Bob

If (Me.ckbachInvoice.value) = -1 And Me.tbAmountOwe.value <> 0 And
(DateDiff("d", Me.tbEmailState, Date) >= 30 Or IsNull(Me.tbEmailState)
And (Me.ckbachInvoice.value) = -1 And Me.tbAmountOwe.value <> O) Then
Me.tbWarning.BackColor = vbRed
Else
Me.tbWarning.BackColor = vbWhite
End If
Click to expand...


That can't be the actual code, because that code will *not* work -- you
have the letter "O" in one place where you mean to have a zero..
However, you are right that this whole approach won't work on a
continuous form. Instead, you must use conditional formatting to set the
BackColor to red, specifying "Expression Is" and an expression like this:

([ckbEmailWarn] <> 0) And ([tbAmountOwe] <> 0) And ((DateDiff("d",
[tbEmailState], Date()) >= 30) Or IsNull([tbEmailState]))

Please note that the above expression should all be entered on one line,
even though it will have been broken onto two lines by the newsreader.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Click to expand...
Actually Dirk there is one problem , because tbAmountOwe can show $0.00
sometimes it is 0.0067 , Clients are sometimes splitting acconts by 1/3 and
there always seems to be not 0000 , thats why I needed <-1 or 1 so anything
in between wont set of the warning,....Regards Bob
 
D

Dirk Goldgar

Bob Vance said:
Actually Dirk there is one problem , because tbAmountOwe can show $0.00
sometimes it is 0.0067 , Clients are sometimes splitting acconts by 1/3
and there always seems to be not 0000 , thats why I needed <-1 or 1 so
anything in between wont set of the warning,....Regards Bob
Click to expand...


So you want to ignore it if tbAmountOwe is less than 1 and greater than -1?
That includes amounts with cents but no dollars? If that's what you want,
the change is fairly simple:

([ckbEmailWarn] <> 0) And (([tbAmountOwe] <= -1) Or ([tbAmountOwe] >=
1)) And ((DateDiff("d", [tbEmailState], Date()) >= 30) Or
IsNull([tbEmailState]))

Again, please note that the above expression should all be entered on one
line, even though it will have been broken onto multiple lines by the
newsreader.

I'm not entirely sure that's what you had in mind, but if it isn't, it
should be easy to modify to achieve the effect you want.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Conditional Formatting 0
Conditional Formatting Problem 0
format code not working properly 3
Filters not responding 2
Conditional Formatting 1
HELP!: VBA code for Datasheet Form for background colors 0
Time formatting and query assistance 0
Format cells based on two conditions 4

Top