Due-date Red Alert!

[XelaIrodavlas]

I have a form containing qualification expiry dates for workers, is there any way to make it so the date will turn red if the due date is before the present date?(or just stand out somehow, if turning red isn't possible, I'm not fussy...)

I don't need a notification like an email, because there wont necessarily be any action taken once a qualification expires, I just want it to be obvious to anyone who looks at the form.

Thanks!

 

[pr2-eugin]

Try the FormCurrent method.. Check the date of expiry if less than today if so display the color as red.. Something like..

Private Sub Form_Current()
    If Me.[[COLOR=Blue][B]controlNameThatHoldsDate[/B][/COLOR]] < Date() Then
        Me.[[COLOR=Blue][B]controlNameThatHoldsDate[/B][/COLOR]].SetFocus
        Me.[[COLOR=Blue][B]controlNameThatHoldsDate[/B][/COLOR]].ForeColor = vbRed
        Me.[[COLOR=Blue][B]controlNameThatHoldsDate[/B][/COLOR]].BackColor = vbYellow
    End If
End Sub

 

[XelaIrodavlas]

That works perfectly! thank you very much

 

[pr2-eugin]

You are most welcome...

 

[XelaIrodavlas]

Actually, maybe not perfectly... for some reason it only turns red after clicking on it, and then turns everything else red regardless of whether they're < today's date or not :\

Any ideas? (I'm at a loss as usual)

 

[pr2-eugin]

Did you copy the code exactly as shown? You did put it inside the Form Current right? Try an else as well.. something like..

Private Sub Form_Current()
    If Me.[controlNameThatHoldsDate] < Date() Then
        Me.[controlNameThatHoldsDate].SetFocus
        Me.[controlNameThatHoldsDate].ForeColor = vbRed
        Me.[controlNameThatHoldsDate].BackColor = vbYellow
    Else
        Me.[controlNameThatHoldsDate].ForeColor = 4210752
        Me.[controlNameThatHoldsDate].BackColor = 16777215        
    End If
End Sub

 

[XelaIrodavlas]

Hmm Its still only working after I click... this is exactly what was in the code after the first try:

Option Compare Database
______________
Private Sub Form_Current()
If Me.[E-T QualificationExpiryDate] < Date Then
Me.[E-T QualificationExpiryDate].SetFocus
Me.[E-T QualificationExpiryDate].ForeColor = vbRed
End If
End Sub


does that look right? thanks

 

[pr2-eugin]

I cannot find anything different in the code.. Strange.. Is there any code associated with OnClick?? Please also make sure that the control names are right.. not the field names in the table.. as the Fore color and back color property applies to the Form's controls.. If unsure view the code in design view and click on the control you will have its name..

 

[XelaIrodavlas]

Strange indeed... there's no code with the OnClick, or anything else in the event tab, and the control source is 'E-T QualificationExpiryDate' as it should be... I will try remaking the form and go again. (Edit: that produced the same result )

I don't suppose you know anywhere I can find some examples do you??

thanks again

 

[pr2-eugin]

As mentioned.. The ForeColor and BackColor are associated with the Form's control.. Please make sure the control name is used..

 

[XelaIrodavlas]

ok.... i'm being a newbie again... what is the control name if not the control source?? is it the part at the very top of the property sheet? (the dropdown box with all the other parts of the form in it)

 

[XelaIrodavlas]

apologies i didnt see your image (i mistook it for another ad!) the name and control source are both the same in my db. see image

 

[pr2-eugin]

In the design view click on the control that holds the Expiry date.. and in the property sheet under Other tab, you will have Name.. see what is the name given there.. that is called the Control Name.. Sometime they might be same.. Sometime they are totally different..

 

[pr2-eugin]

Okay seems like it is a sub form.. the Form Current method works only when the Form has the focus so you have to refer to the subForm control.. something like..

Private Sub Form_Current()
    If Me![B][COLOR=Blue]Subform1[/COLOR][/B].Form![E-T QualificationExpiryDate] < Date Then
        Me![B][COLOR=Blue]Subform1[/COLOR][/B].Form![E-T QualificationExpiryDate].SetFocus
        Me![B][COLOR=Blue]Subform1[/COLOR][/B].Form![E-T QualificationExpiryDate].ForeColor = vbRed
    End If
End Sub

Please change the name Subform1 to match your Sub Form name..

 

[XelaIrodavlas]

Im trying now, but when i put in my subform name I get an error 'compile error: expected: Then or GoTo.

I have assumed you want me to leave the .form! part in.

Thanks for being so patient btw

 

[XelaIrodavlas]

heres my code so far;

Private Sub Form_Current()
If Me!sFRM Employees-Training.Form![E-T QualificationExpiryDate] < Date Then
Me!sFRM Employees-Training.Form![E-T QualificationExpiryDate].SetFocus
Me!sFRM Employees-Training.Form![E-T QualificationExpiryDate].ForeColor = vbRed
End If
End Sub



but it doesn't like it... it keeps 'expecting' an = in employee-training, so i tried joining it all in []'s but no success there either.

 

[pr2-eugin]

Try..

Private Sub Form_Current()
    If Me![sFRM Employees-Training].Form![E-T QualificationExpiryDate] < Date Then
        Me![sFRM Employees-Training].Form![E-T QualificationExpiryDate].SetFocus
        Me![sFRM Employees-Training].Form![E-T QualificationExpiryDate].ForeColor = vbRed
    End If
End Sub

 

[XelaIrodavlas]

Now its saying can't find the field 'sFRM Employees-Training'... Does it think this is meant to be a field rather than a form? Unless I've got the name wrong... name seems fine :\

 

[pr2-eugin]

I am sorry I could not be of great help.. But look here for finding information on accessing controls in SubForms..

 

[XelaIrodavlas]

it's ok you have been very helpful, and far more patient than I could have expected from anyone I'll figure it out eventually