vb code in access form

[aman909]

Im trying to use some vb code in a form which simply changes font colours. It is for a stock control system. The stock control system includes three items: colour, stock and minimum stock.

The code is:

Private Sub Form_Current()
If Me.Colour = "Red" And Me.Minimum_Stock > Me.Stock Then
Me.Minimum_Stock.ForeColor = vbRed
ElseIf Me.Colour = "Green" And Me.Minimum_Stock > Me.Stock Then
Me.Minimum_Stock.ForeColor = vbGreen
ElseIf Me.Colour = "Blue" And Me.Minimum_Stock > Me.Stock Then
Me.Minimum_Stock.ForeColor = vbBlue
ElseIf Me.Colour = "Black" And Me.Minimum_Stock > Me.Stock Then
Me.Minimum_Stock.ForeColor = vbBlack
ElseIf Me.Colour = "Yellow" And Me.Minimum_Stock > Me.Stock Then
Me.Minimum_Stock.ForeColor = vbYellow
End If
End Sub

But im having a few problems, what happens is for example, if the colour black stock is greater then its minimum stock that stays black which it should but then if the blue minimum stock is greater then stock it goes blue which it should. The problem is now when i go back to the colour black it has turned blue. For some reason it is picking up the colour blue.

Does anyone have any ideas why and how to stop this?

 

[Dennisk]

this is a well known aspect (bug?) of access and it rears its ugly head on continious forms with unbound controls. In reality you have one control repeated. One round this is to use conditional formatting although it is very limited. The other way is to create a column either in the table or try creating a column in the recordsource query.

 

[aman909]

What would go in the conditional formatting?

 

[Dennisk]

Aman909,
Select the control, then from the format menu select conditional formatting, and a form will pop up. This is where you can enter the condition and the colour associated with that condition. it is very restrictive as you can only use 3 different conditions so that only gives you 4 different colours.