Changing field backcolor based on another field

[skwilliams]

I have the following VBA code within a function that I'm running.

If ProdScore = 0 Then a.BackColor = 8454143 Else a.BackColor = -2147483633
If ProdScore = 0.5 Then b.BackColor = 8454143 And c.BackColor = 8454143 Else b.BackColor = -2147483633 And c.BackColor = -2147483633
If ProdScore = 1 Then e.BackColor = 8454143 And d.BackColor = 8454143 Else e.BackColor = -2147483633 And d.BackColor = -2147483633
If ProdScore = 1.5 Then g.BackColor = 8454143 And f.BackColor = 8454143 Else g.BackColor = -2147483633 And f.BackColor = -2147483633
If ProdScore = 2 Then i.BackColor = 8454143 And h.BackColor = 8454143 Else i.BackColor = -2147483633 And h.BackColor = -2147483633
If ProdScore = 2.5 Then k.BackColor = 8454143 And j.BackColor = 8454143 Else k.BackColor = -2147483633 And j.BackColor = -2147483633
If ProdScore = 3 Then m.BackColor = 8454143 And l.BackColor = 8454143 Else m.BackColor = -2147483633 And l.BackColor = -2147483633
If ProdScore = 3.5 Then o.BackColor = 8454143 And n.BackColor = 8454143 Else o.BackColor = -2147483633 And n.BackColor = -2147483633
If ProdScore = 4 Then p.BackColor = 8454143 Else p.BackColor = -2147483633

When I execute the code, it says calculating on the status bar and just sits there.

I've inserted a breakpoint in the code and execute it. When I check the code, it tells me the backcolor of m and l is -2147483633 even though it says the ProdScore equals 3.

Am I missing something here?

Thanks.

 

[misunders2d]

As far as i remember AND is logic operator in vb, which returns a boolean value. Try replace this by:

If ProdScore = 1 Then
e.BackColor = 8454143
d.BackColor = 8454143
Else
e.BackColor = -2147483633
d.BackColor = -2147483633
End If


and don't forget to use switch structure

 

[misunders2d]

Hope it will work

 

[skwilliams]

Thanks for the reply.

Part of it is working. The fields d and e now highlight when the ProdScore =1.
However, when I go to the next record and execute the function, those fields are still highlighted even if the ProdScore isn't equal to 1.

Any ideas??

Thanks!

 

[Rich]

Your Code's in the wrong event, move it to the Form_Current event, or is this a Continuous Form?

 

[skwilliams]

It's not a continuous form.

The code to calculate the ProdScore is trigger by exiting the MonMin field on the form. A figure is entered there.

Wouldn't the Form_Current event just trigger the code when the form is displayed? Or am I thinking of something else?

Thanks.

 

[Rich]

Again, the code belongs in the Form_Current event,

in the AfterUpdate event of MonMin put

Call Form_Current

 

[skwilliams]

I placed the function in the forms after update event. Now I can't go to the next record. The status bar says "Calculating". I click the "x" and it says "You can't save this record at this time."