Code Help

[Branagan]

Hi,

Heres what i'm trying to do :

if "actual fitted date" is 6months ago or older
then change form back colour to red


please help !!!

 

[Bat17]

use the forms on current event to check the date and change the colour appropriatly

 

[Branagan]

sorry could you not be any more specific than that, what will the code be...

is this ok below, or is it totally wrong ??

If [Actual Fitted Date] Is DateAdd("m", -6, Date) Then
FormHeader.BackColor = 15

End If

 

[GaryPanic]

couple of ways of doing this

you could do a weekly update qry
if x. then y then background color = blue/red whatever
or on your form have on the on current a bit of coding
if date field = > then ..........

I prefer the upqry option as its a qry done on request where as the on current runs every time the form is open
other users have preferences the on current does mean that it is "Live" ie 1 day pass the date critia then it works whereas the qry option only works after the update and would involve an extra fvield on your table

 

[Bat17]

what you have is close
You need to code the 'Else' as well to tun it back to the right colour when newer than 6 months, try

Private Sub Form_Current()
If Me.[Actual Fitted Date] < DateAdd("m", -6, Date) Then
    Me.FormHeader.BackColor = 15
Else
    Me.FormHeader.BackColor = 123
End If
End Sub

HTH

Peter

 

[Branagan]

what you have is close
You need to code the 'Else' as well to tun it back to the right colour when newer than 6 months, try

Private Sub Form_Current()
If Me.[Actual Fitted Date] < DateAdd("m", -6, Date) Then
    Me.FormHeader.BackColor = 15
Else
    Me.FormHeader.BackColor = 123
End If
End Sub

HTH

Peter

thanks for the reply and the help, but it doesn't seem to work, the formheader colour stays at else backcolour all the time, no matter what date i enter in "actual fitted date" field

 

[Bat17]

Just check the events property "on current" for the form is set to [Event Procedure]

if it is add a msgbox to check that the code is running and what the value of the field is.

msgbox "The date is " & me.[Actual Fitted Date]

HTH

Peter

 

[Branagan]

its still not doing it for me, i have attached the database, could you please have a look at it for me

many thanks

 

[Bat17]

It's a bit ilusive
You have used 'Date' as a field name in the table and on the form. Date is a reserved word in Access and it is getting confussed by your field. I did not have time to track it down on your form, but if you change
If Me.[Actual Fitted Date] < DateAdd("m", -6, Date) Then
To
If Me.[Actual Fitted Date] < DateAdd("m", -6, Now) Then

It should work.

Peter

 

[Branagan]

thats it, its working !!

your a genius !!

Thank you very much !