Help with backcolor, code enclosed 1

[Zeroanarchy]

Have a prob with a bit of code the idea is that if the field value is greater than one then change the back color to red else stay the same. Here is the code appreatiate any help on this one. The problem all [2] is changing to Red not matter what the value

Private Sub Form_Open(Cancel As Integer)

If [2] >= 1 Then
Me![2].BackColor = RGB(255, 0, 0)

End If
End Sub

 

[RegionsRob]

You need to put in an ELSE statement to set the background to the normal color.

The reason is because once you set the background to red, it is going to stay red, regardless of the value of [2], unless you tell it to change to another color.

I had the same kind of problem and spent a day of trial and error and pulling out hair until I stumbled on the answer myself.

 

[Zeroanarchy]

Thanks RegionsRob for your help on that, much appreatiated

 

[Zeroanarchy]

As you can see tried that and still have the same problem.
Anyone else have any clues.

Private Sub Form_Open(Cancel As Integer)

If [2] >= 1 Then
Me.[2].BackColor = RGB(255, 0, 0)
Else
Me.[2].BackColor = RGB(255, 255, 255)
End If
End Sub

 

[RegionsRob]

It could be that Access is seeing [2] as a number instead of a field name, and since 2 IS >= 1, all backgrounds are red. Try
[tab]IF [2].value >= 1 THEN ...

You might even have to say: Me![2].value

 

[Zeroanarchy]

Found out what the problem is related to but am unable to rectafy the situation, this form is continues, it has a vertical scroll bar, what the problem is, is that if the first entry is greater than one than all record go red.
hope that makes sence.

 

[Kobayashi]

Zeroanachy,

Not 100% sure, but try attaching the code to the 'OnCurrent' event instead of the 'form_OnOpen' event.

Good Luck

Kobayashi

 

[BoxHead]

Zeroanarchy,

I'm guessing that you're using Access '97. You can't change the properties for controls on individual records. Whatever record is current will determine the property setting for all the records.

Something that I've done in a continuous form is set an unbound textbox over a datefield textbox (DateLastAudit). I format the unbound textbox's forecolor to red and for it's control source I use

     =IIf(DateDiff("d",[DateLastAudit],Date())>30,[DateLastAudit],"")

What this does is set the value of the textbox to be the same as DateLastAudit if it is over 30 days old. Since the textbox is on top and the forecolor is red, it effectively displays all dates over 30 days old in red.

There is a similar procedure for creating the effect of a red background but I'll have to get home before I can give you the details.

Let me know if you're interested.

P.S. I've heard that Access 2000 has a feature called conditioonal formatting that does exactly what you're after.

HTH John

Use what you have,
Learn what you can,
Create what you need.

 

[Zeroanarchy]

Boxhead
sound like you are heading down the right road, if you have the code for this one I would appreatiate it.
Thanks. Zero

 

[petermeachem]

Take a look at thread181-31643. I think it is what you need. Peter Meachem
peter@accuflight.com

 

[BoxHead]

Zeroanarchy,

Peter's got it! Thanks for picking that up, Peter. What time zone are you guys in, anyway?

That thread ends with an open question regarding inputting information. My continuous form is not for input, just for selecting an item to open another form for audits.

You have to be sure that your unbound textbox is not a tab stop and that the GotFocus event switches the focus to the textbox you actually want edited:

[2].SetFocus

That way, no one is trying to edit it and it doesn't cover up the [2] textbox by having the focus.


HTH

John

Use what you have,
Learn what you can,
Create what you need.

 

[petermeachem]

I've used the technique in that thread in loads of Access programmes. It works a treat. You do need terminal font, or an equivalent. There is information in this forum on where to get a terminal-type font.
Peter Meachem
peter@accuflight.com

 

[Zeroanarchy]

Gentleman the code is great, infact it does exactly what my code at the top does, only one problem, this form is not a subform it is a contiunes form lot like the
"Customer Phone List" in the northwind database. Contiues under Details..
The code has the same error as mine, It refers to the first recond only as that appears in details and changes all records to the color acording to the value of the first record.
Time GMT +10hrs

Thanks Zero

 

[BoxHead]

Zeroanarchy,

Nobody is talking about subforms.

Continuous forms. That's what you said you had. That's what I have. That's what the other thread is all about.

On a continuous form in Access '97, you cannot change the property of a control for an individual record.

What Peter and I are suggesting is that you create an unbound textbox and use code to effect the value. Since this unbound textbox uses the terminal font in whatever color you choose, it will resemble a change to the backcolor.

Try changing the font to something recognizable and let us know what the text box says. Or, post your code. John

Use what you have,
Learn what you can,
Create what you need.

 

[Zeroanarchy]

Boxhead I tried it but must be a little lost here is what I have done and the problem is still the same it looks like the code only keep apllying to entry one and all entries after that are color accordingly.

Private Sub Form_Load()
Dim lngRed As Long

lngRed = RGB(255, 0, 0)
If test = 1 Then
Phone.BackColor = RGB(255, 0, 0)
Else
Phone.BackColor = RGB(0, 255, 0)
End If
End Sub

hmmm, what am I doing wrong..

 

[BoxHead]

Zeroanarchy,

When you refer to the backcolor,

    If test = 1 Then
      Phone.BackColor = RGB(255, 0, 0)

you are referring to a property, not a value.


The key to making this work is to not change properties at all....change the values. Let's take it from your original post:

     If [2] >= 1 Then
     Me![2].BackColor = RGB(255, 0, 0)

Put a new text box on your form.
Name it txtBackColor.
Set its forecolor to red.
backstyle to transparent
special effect to flat
border style to transparent
font to Terminal

Paste the following into the control source of txtBackColor. This assumes that the textbox you're checking is named [2] and that 'greater than one' is your test.

   =IIf([2]>1,"ÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛ","")

This will change the value of txtBackColor to the characters above whenever the value of [2] is greater than 1. These particular characters in the Terminal font are displayed as a solid block of red. If the value of [2] is less than one, then the value of txtBackColor will be a zero length string. Since all of the properties for this text box are set to transparent, you will not see it on the form when it is a zero length string.

I realize that this is a bit strange; changing values of one text box to simulate property changes on another text box. In fact, I guess, we're cheating. We're getting an Access 2000 feature out of Access '97. Good for us!

The fact is that you probably do not want to leave the properties of this text box transparent, but this is the most clear-cut example. You chould size this txtBackColor to be the same as [2] with the same background and border. Keep the font at Terminal and the forecolor set to Red. Set it right on top of [2] and then, from the menu, select Format and Send to Back. Click somewhere else on the form to get the focus off of txtBackColor and then select [2]. Set the border and backstyle to transparent for [2].

When you look at the form view, [2] should still look the same as before. Be sure to set txtBackColor's tab stop property to "No" and set it's OnGotFocus event to

 [2].SetFocus.

Let me know how it works.

John

Use what you have,
Learn what you can,
Create what you need.

 

[Zeroanarchy]

BoxHead thankyou very much for your time on this one, it worked atreat, I don't quite understand how it is actually works but I guess it does. So thanks again for you help.

Zero

 

[BoxHead]

Zero,

Glad it worked out.

It might help you to understand what's going on with this if you change the font from terminal to something else and watch the affect.

John

Use what you have,
Learn what you can,
Create what you need.