Creating status monitor

[Steven.Ashby82]

Hi All,

Again I don't know if this is possible and if so how simple it is.

I've got the "now" command setup to stamp the date when a record is created but I'm trying to find out two things...

1. Can I display on that record how long it has been "Open" for (I also have a combobox simply containing "Open" "Closed"
2. Can I set an image or change the colour after a certain amount of time of that record remaining open?

Thank You

 

[pr2-eugin]

Hello Steven, the answer to your questions is Yes and Yes.. You can show how long the Record has been opened for.. And you can change the color after a specific time.. This will use the Form_Timer event.. So in your Form there will be a 'Property' under Events tab called Timer Interval.. it is denoted by Milli seconds, so one second is represented as 1000.. So look at the sample, that will help you to get started...

 

[Steven.Ashby82]

Firstly thank you that is brilliant but it's not quite what I'm after.

I think I may have confused matters, When I said about the record being open I was refering to a ticket status type of thing, for example.

If a record is created and the "Status" cbo is set to "Open" then after 3 days I want to create some kind of visual notification representing the fact that the matter needs addressing.

Thank you so much for your continuing support. Did you manage to take another look at http://www.access-programmers.co.uk/forums/showthread.php?t=240126

 

[pr2-eugin]

Oops.. Sorry my bad.. Yes it is possible, You have to take the field that holds the date and check it in the Form_Current Method.. Something along the Lines of

Private Sub Form_Current()
    If ((Me.ticketCombo = "Open") And (Me.ticketOpenDate < = Date-3)) Then
        Me.Detail.BackColor = vbRed
    Else
        Me.Detail.BackColor = vbWhite
    End If
End Sub

I will respond to that thread in a minute..

 

[Steven.Ashby82]

Excellent thank you as always

 

[pr2-eugin]

Most welcome..

 

[Steven.Ashby82]

Thank you I have implemented this now and also added some code

Private Sub cboStatus_AfterUpdate()
 If ((Me.cboStatus = "Opened/ReOpened") And (Me.DateRaised <= Date - 3)) Then
        Me.boxStatus2.BackColor = vbYellow
    ElseIf ((Me.cboStatus = "Opened/ReOpened") And (Me.DateRaised <= Date - 7)) Then
        Me.boxStatus2.BackColor = vbRed
    Else
        Me.boxStatus2.BackColor = vbWhite
    End If

The Yellow works for three days old but it won't turn red for 7 days old?

 

[pr2-eugin]

Hmmm.. The problem is because the Date-3 would give three days less than today which is 13-Jan-2013, say if the Date raised is 01-Jan-2013, then it is obviously less than 13-Jan-2013, so by the basis of If-Else structure.. If the condition is true none of the following is executed.. So you have to have the highest condition first.. Like..

If ((Me.cboStatus = "Opened/ReOpened") And (Me.DateRaised <= Date - 7)) Then
    Me.boxStatus2.BackColor = vbRed
ElseIf ((Me.cboStatus = "Opened/ReOpened") And (Me.DateRaised <= Date - 3)) Then
    Me.boxStatus2.BackColor = vbYellow
Else
    Me.boxStatus2.BackColor = vbWhite
End If

Hope that helps..

BTW, I could not stop it noticing you have used the AfterUpdate of the ComboBox, which will show the color change while you are active in the record i.e. when you change the value.. Once you go out and come back to the same record this will be normal.. That is the reason I suggested FormCurrent.. Have I got this requirement wrong again?