Forms With data that changes color

[Cozzy]

Afternoon People,

I am creating a database but i am having a problem in that I have a Service Contract form that I want to color code the dates depending on some expressions,

I.E I have a "ACplanned Date" (text box) and a "ACComleted Date" (text box), what I want to do is when a date is entered into the "acplanned" the backcolor to change to yellow, and if the completed date in entered then it to change to Green?? I would like also to check the data in the acplanned and if it is less than 2 weeks away the color to turn red and alert the user??

thanks

 

[vbaInet]

We probably need a bit more info. What would the colour be if data already exists? What about for a newly created record?

 

[Simon_MT]

Here is a script as a Function you paly around with: I changes colors and Form image (which is also color coded).

Private Function OriginalsFeatures()

    With CodeContextObject
        If Forms![Menu]![Company Region] = "L" Then
            If Not IsNull(.[Orig 3rd Party]) Or .[Orig Supplier Vat Code] <> 0 Then
                .[Orig Sell Net].BackColor = 8388608
                .[Orig Sell Net].ForeColor = 16777215
                .[Orig Sell].BackColor = 16777215
                .[Orig Sell].ForeColor = 0
            ElseIf (IsNull(.[Orig 3rd Party ]) Or .[Orig Supplier Vat Code] = 0) Then
                .[Orig Sell].BackColor = 8388608
                .[Orig Sell].ForeColor = 16777215
                .[Orig Sell Net].BackColor = 16777215
                .[Orig Sell Net].ForeColor = 0
            End If
        End If
    
        If .[Orig Stock Status] = "P" Then
            .Picture = "C:\Databases\Livery\Background_OriginalsStatusPurch.jpg"
        ElseIf .[Orig Stock Status] = "H" Then
            .Picture = "C:\Databases\Livery\Background_OriginalsStatusHistory.jpg"
        Else
            .Picture = "C:\Databases\Livery\Background_Originals.jpg"
        End If
    End With

End Function

Simon

 

[Cozzy]

Thanks Simon, will dive in and see what I can get going!!

here some more info vbainet

Basically I am creating a brand new database from a existing one that was created, the service contract never worked on the old one so they were working of a excel sheet, so all the data will be put in manually from scratch, I have a table called "service contract" which has relationships with a "Customer" table and a "Orders" table which seems to be working fine as when a service contract comes in it goes out as a job order for a technician. I want an easy indication for the admin staff to know what customer need service contracts to be done as it seems quite a important thing for them to remember.

 

[Cozzy]

Hi there hopefully people are still looking at this post??
I think I have sorted the change color by date issue, but in doing that I've stubbled upon another problem, the form that shows this data and is set as a continuous form, problem being is that whatever the first record is, it changes the whole form not the individual records?? any ideas?

is this a Array problem or record set?

 

[Simon_MT]

You will have to use Conditional formatting.

Simon