Message box

[Sharon Hague]

Hi All

I have a form showing various employee information. One of the fields is employment date for when the employee started.

I want a message box to be displayed to remind me when any of our employees have served 10 years, 15 years, 20 years & so on from their employment date, when the form is opened.

Is this possible?

I'd appreciate anybody's help on this.

Regards

Sharon

 

[Mile-O]

In the Current() event:

Select Case CDate(Me.txtDateStarted)
    Case Is = DateAdd("yyyy", -5, Date())
        MsgBox "5 Years served.", vbInformation
    Case Is = DateAdd("yyyy", -10, Date())
        MsgBox "10 Years served.", vbInformation
    Case Is = DateAdd("yyyy", -15, Date())
        MsgBox "15 Years served.", vbInformation
    Case Is = DateAdd("yyyy", -20, Date())
        MsgBox "20 Years served.", vbInformation
End Select

 

[Sharon Hague]

Mile-O-Phile

Thanks for your reply.

However, I forgot to mention that I wanted the message box to appear, lets say 1 month prior to the 10 or 15 year period.

Is this possible.

Sorry to mess you around

 

[Sharon Hague]

Morning Mile-O-Phile

You havn't by any chance had time to look at the above have you?

 

[Mile-O]

Here you go:

Select Case DateAdd("m", -1, CDate(Me.txtDateStarted))
    Case Is = DateAdd("yyyy", -5, Date())
        MsgBox "5 Years served.", vbInformation
    Case Is = DateAdd("yyyy", -10, Date())
        MsgBox "10 Years served.", vbInformation
    Case Is = DateAdd("yyyy", -15, Date())
        MsgBox "15 Years served.", vbInformation
    Case Is = DateAdd("yyyy", -20, Date())
        MsgBox "20 Years served.", vbInformation
End Select

 

[Mile-O]

Actually, this is better:

Select Case CDate(Me.txtDateStarted)
    Case Is = DateAdd("m", -1, DateAdd("yyyy", -5, Date()))
        MsgBox "5 years about to be served.", vbInformation
    Case Is = DateAdd("m", -1, DateAdd("yyyy", -10, Date()))
        MsgBox "10 years about to be served.", vbInformation
    Case Is = DateAdd("m", -1, DateAdd("yyyy", -15, Date()))
        MsgBox "15 years about to be served.", vbInformation
    Case Is = DateAdd("m", -1, DateAdd("yyyy", -20, Date()))
        MsgBox "20 years about to be served.", vbInformation
End Select

 

[Sharon Hague]

Mile-o-Phile

Thanks for your reply.

However, I have entered your code into the On Current Event properties of my form, changed your part of code Me.txtDateStarted to Me.EmploymentDate but I can't get the message box to appear.

For one of our employees I have entered a date of 12.02.94 then closed my form and re-opened it but the message box does not appear.

I have checked the code and have noticed that at the end of all 4 lines starting with Case Is that after Date there is only )) instead of ())). I have tries changing this to the same as yours but it automatically takes them out but can't think this would be the problme anyway.

Do I have to change any other part of the code for it to work with my field, if not any suggestions?

Thanks

 

[Mile-O]

Was having an off morning. Here you go:

Private Sub Form_Current()
    Dim intCounter As Integer
    For intCounter = 5 To 20 Step 5
        If DateAdd("yyyy", intCounter, Me.txtStartDate) = DateAdd("m", 1, Date) Then
            MsgBox intCounter & " years served in one month."
            Exit Sub
        End If
    Next intCounter
End Sub

 

[Mile-O]

In fact, the only time I can see it not working is if people start on 29th February.

 

[Oldsoftboss]

Or if you are having the morning off.

 

[Mile-O]

I've had too many of them to get another one so soon.

 

[Oldsoftboss]

Lucky You...

 

[Sharon Hague]

Thanks Mile-O-Phile

It's worked a treat.

 

[R6Flyer]

Sharon,

Have you considered the scenario if an 'anniversary' falls on a Sunday? I don't know if your office is open over the weekend but if it isn't you could easily miss out on being reminded about the above.

Could be as well to amend the code to:

Private Sub Form_Current()
Dim intCounter As Integer
For intCounter = 5 To 20 Step 5
If DateAdd("yyyy", intCounter, Me.txtStartDate) = DateAdd("m", 1, Date) or DateAdd("m", 1, Date()-1) or DateAdd("m", 1, Date()-2) Then
MsgBox intCounter & " years served next month."
Exit Sub
End If
Next intCounter
End Sub

Cheers

Flyer

 

[Sharon Hague]

R6Flyer

Thanks for your suggestion

I have just had a similar thought but not for the weekend. I was thinking if I was off sick or on holiday or something and the form does not get opened.

I think I will have to add something extra to the code for these reasons.

However in the mean time I will change my code to your porposal.

Cheers.

 

[Sharon Hague]

R6Flyer

I now have entered your code into my on current event of my form.

When I open the form the message box appears for every record on any employment date.

Any idea what the problem could be?

Regards

Sharon