Using VBA to change background color of a field on a form

[spiderq]

I have set up a simple form which has a textbox to get criteria to run
an update query, I have put a subform on it which displays the stock
table and used a macro to requery the table(created from a select
query) and show that the updated stock in the table ( using continuous
forms).

I want to change the background colour of the stock field if the the
value has changed.

I am not very good with VBA so please bear with me!

I have created a module called StockLevel which I think will hold the
initial value of the Stock field as a Global variable before I run
the update query( so available to the rest of my code after the
requery)
**********************************************************************
Option Compare Database

Public currentLevel As Integer
**********************************************************************

I have then added this code to the subform:

*************************************************************************
Private Sub Form_Current()
currentLevel = [Stock].Value
End Sub



Private Sub Form_AfterUpdate()
Dim bWhite As Long
Dim bRed As Long


bRed = RGB(255, 0, 0)
bWhite = RGB(255, 255, 255)

If [Stock].Value > StockLevel Then
[Stock].BackColor = bRed
Else
[Stock].BackColor = bWhite
End If
End Sub

**************************************************************
What have I done wrong here? The rest of my simple process works fine.

 

[Steve Schapel]

Spider,

Shouldn't this line of code:
If [Stock].Value > StockLevel Then
... . be like this:
If [Stock].Value > CurrentLevel Then

Or, as would more commenly be written:
If Me.Stock > CurrentLevel Then

Also I am wondering about this code being on the AfterUpdate event of
the form. I am not 100% sure what you are doing, but I would have
thought this code to change the colour would also be on the Current event.

 

[spiderq]

Spider,

Shouldn't this line of code:
        If [Stock].Value > StockLevel Then
.. . be like this:
        If [Stock].Value > CurrentLevel Then

Or, as would more commenly be written:
        If Me.Stock > CurrentLevel Then

Also I am wondering about this code being on the AfterUpdate event of
the form.  I am not 100% sure what you are doing, but I would have
thought this code to change the colour would also be on the Current event..

--
Steve Schapel, Microsoft Access MVP

I have set up a simple form which has a textbox to get criteria to run
an update query, I have put a subform on it which displays the stock
table and used a macro to requery the table(created from a select
query) and show that the updated stock in the table ( using continuous
forms).

I want to change the background colour of the stock field if the the
value has changed.

I am not very good with VBA so please bear with me!

I have created a module called StockLevel which I think will hold the
initial value of the Stock field  as a Global variable before I run
the update query( so available to the rest of my code after the
requery)
**********************************************************************
Option Compare Database

Public currentLevel As Integer
**********************************************************************

I have then added this code to the subform:

*************************************************************************
Private Sub Form_Current()
currentLevel = [Stock].Value
End Sub

Private Sub Form_AfterUpdate()
    Dim bWhite As Long
    Dim bRed As Long

    bRed = RGB(255, 0, 0)
    bWhite = RGB(255, 255, 255)

    If [Stock].Value > StockLevel Then
    [Stock].BackColor = bRed
    Else
    [Stock].BackColor = bWhite
    End If
End Sub

**************************************************************
What have I done wrong here? The rest of my simple process works fine.-Hide quoted text -

- Show quoted text -

Yes, I had missed that! But having looked at it again I need to store
a range of values for current stock level as I have several items of
stock which means I would need some sort of array (?) and would have
to loop through each one to store it. I think its just more
complicated than I need this to be - but thanks for your help.