Date of Pension auto (1 Viewer)

M

mohamedmatter

Registered User.
Local time
Yesterday, 17:24
Joined
Oct 25, 2015
Messages
112
i want to calculate the date of pension from (birth date + 50 years) auto when i open {Employee From} and Show Message box to count Employee reach date of pension
not
i attach simple database to example View attachment Employee DataBase.accdb
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 01:24
Joined
Sep 21, 2011
Messages
14,287
Go on, I'll bite :confused:
Code: 
dateadd("yyyy",50,YourDOB)

Use DateDiff() if you want to say 'xxx days until your pension'
 
M

mohamedmatter

Registered User.
Local time
Yesterday, 17:24
Joined
Oct 25, 2015
Messages
112
any help for me i'm beginner in access pls
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:24
Joined
May 7, 2009
Messages
19,237
add this code to your Form:

Private Sub Date_Of_Brith_AfterUpdate()
If IsDate(Me.Date_Of_Brith) Then _
Me.Date_Of_Pension = DateAdd("yyyy", 50, Me.Date_Of_Brith)
End Sub

Private Sub Form_Current()
If Trim(Me.Date_Of_Pension & "") = "" Then
Call Date_Of_Brith_AfterUpdate
Me.Dirty = False
End If
End Sub
 
M

mohamedmatter

Registered User.
Local time
Yesterday, 17:24
Joined
Oct 25, 2015
Messages
112
arnelgp said:
add this code to your Form:

Private Sub Date_Of_Brith_AfterUpdate()
If IsDate(Me.Date_Of_Brith) Then _
Me.Date_Of_Pension = DateAdd("yyyy", 50, Me.Date_Of_Brith)
End Sub

Private Sub Form_Current()
If Trim(Me.Date_Of_Pension & "") = "" Then
Call Date_Of_Brith_AfterUpdate
Me.Dirty = False
End If
End Sub
Click to expand...
thanks for you
and i hope on request two i want show message box tell me how many employee reach date of pension and create query this employees
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:24
Joined
May 7, 2009
Messages
19,237
First create this query and name it qryPension:
Code: 
SELECT Emplyee.ID, Emplyee.[Employee-Name], Emplyee.[Date Of Brith], Emplyee.[Date Of Pension]
FROM Emplyee
WHERE (((Emplyee.[Date Of Pension])<=Date()));
then edit your form.
On Property->Event->Timer Interval, set it to 10000. replace your code with this one:
Code: 
Private Sub Date_Of_Brith_AfterUpdate()
    If IsDate(Me.Date_Of_Brith) Then _
        Me.Date_Of_Pension = DateAdd("yyyy", 50, Me.Date_Of_Brith)
End Sub

Private Sub Form_Current()
    If Trim(Me.Date_Of_Pension & "") = "" Then
        Call Date_Of_Brith_AfterUpdate
        Me.Dirty = False
    End If
End Sub

Private Sub Form_Open(Cancel As Integer)
    CurrentDb.Execute "update Emplyee Set [Date Of Pension]=DateAdd('yyyy', 50, [Date Of Brith])"
End Sub

Private Sub Form_Timer()
    Dim lngCount As Long
    Me.TimerInterval = 0
    
    lngCount = DCount("*", "qryPension", "[Date Of Pension] <= Date()")
    If lngCount > 0 Then
        If MsgBox(lngCount & " of employees has reached their pension(s). " & _
            "Would you like to view them?", vbInformation + vbYesNo) = vbYes Then
            If SysCmd(acSysCmdGetObjectState, acQuery, "qryPension") <> 0 Then _
                DoCmd.Close acQuery, "qryPension"
            DoCmd.OpenQuery "qryPension"
        End If
    End If
        
End Sub
 
M

mohamedmatter

Registered User.
Local time
Yesterday, 17:24
Joined
Oct 25, 2015
Messages
112
arnelgp said:
First create this query and name it qryPension:
Code: 
SELECT Emplyee.ID, Emplyee.[Employee-Name], Emplyee.[Date Of Brith], Emplyee.[Date Of Pension]
FROM Emplyee
WHERE (((Emplyee.[Date Of Pension])<=Date()));
then edit your form.
On Property->Event->Timer Interval, set it to 10000. replace your code with this one:
Code: 
Private Sub Date_Of_Brith_AfterUpdate()
    If IsDate(Me.Date_Of_Brith) Then _
        Me.Date_Of_Pension = DateAdd("yyyy", 50, Me.Date_Of_Brith)
End Sub

Private Sub Form_Current()
    If Trim(Me.Date_Of_Pension & "") = "" Then
        Call Date_Of_Brith_AfterUpdate
        Me.Dirty = False
    End If
End Sub

Private Sub Form_Open(Cancel As Integer)
    CurrentDb.Execute "update Emplyee Set [Date Of Pension]=DateAdd('yyyy', 50, [Date Of Brith])"
End Sub

Private Sub Form_Timer()
    Dim lngCount As Long
    Me.TimerInterval = 0
    
    lngCount = DCount("*", "qryPension", "[Date Of Pension] <= Date()")
    If lngCount > 0 Then
        If MsgBox(lngCount & " of employees has reached their pension(s). " & _
            "Would you like to view them?", vbInformation + vbYesNo) = vbYes Then
            If SysCmd(acSysCmdGetObjectState, acQuery, "qryPension") <> 0 Then _
                DoCmd.Close acQuery, "qryPension"
            DoCmd.OpenQuery "qryPension"
        End If
    End If
        
End Sub
Click to expand...

this error display and query not work
because employee number 1 was born in 1/1/1910 +50 = 1/1/1960 not work in query
2.png

View attachment Employee DataBase.accdb
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:24
Joined
May 7, 2009
Messages
19,237
because the the on the first database you post, there is space on the field name, the one you later post don't have.
change the query to:
Code: 
SELECT Emplyee.ID, Emplyee.[Employee-Name], Emplyee.DateOfBrith, emplyee.dateofpension 
FROM Emplyee
WHERE (((Emplyee.DateOfPension)<=Date()));

and the code to:
Code: 
Private Sub DateOfBrith_AfterUpdate()
    If IsDate(Me.Date_Of_Brith) Then _
        Me.DateOfPension = DateAdd("yyyy", 50, Me.DateOfBrith)
End Sub

Private Sub Form_Current()
    If Trim(Me.Date_Of_Pension & "") = "" Then
        Call DateOfBrith_AfterUpdate
        Me.Dirty = False
    End If
End Sub

Private Sub Form_Open(Cancel As Integer)
    CurrentDb.Execute "update Emplyee Set [DateOfPension]=DateAdd('yyyy', 50, [DateOfBrith])"
End Sub

Private Sub Form_Timer()
    Dim lngCount As Long
    Me.TimerInterval = 0
    
    lngCount = DCount("*", "qryPension", "[DateOfPension] <= Date()")
    If lngCount > 0 Then
        If MsgBox(lngCount & " of employees has reached their pension(s). " & _
            "Would you like to view them?", vbInformation + vbYesNo) = vbYes Then
            If SysCmd(acSysCmdGetObjectState, acQuery, "qryPension") <> 0 Then _
                DoCmd.Close acQuery, "qryPension"
            DoCmd.OpenQuery "qryPension"
        End If
    End If
        
End Sub
 
Last edited:
M

mohamedmatter

Registered User.
Local time
Yesterday, 17:24
Joined
Oct 25, 2015
Messages
112
arnelgp said:
because the the on the first database you post, there is space on the field name, the one you later post don't have.
change the query to:
Code: 
SELECT Emplyee.ID, Emplyee.[Employee-Name], Emplyee.DateOfBrith
FROM Emplyee
WHERE (((Emplyee.DateOfPension)<=Date()));

and the code to:
Code: 
Private Sub DateOfBrith_AfterUpdate()
    If IsDate(Me.Date_Of_Brith) Then _
        Me.DateOfPension = DateAdd("yyyy", 50, Me.DateOfBrith)
End Sub


Private Sub Form_Current()
    If Trim(Me.Date_Of_Pension & "") = "" Then
        Call DateOfBrith_AfterUpdate
        Me.Dirty = False
    End If
End Sub

Private Sub Form_Open(Cancel As Integer)
    CurrentDb.Execute "update Emplyee Set [DateOfPension]=DateAdd('yyyy', 50, [DateOfBrith])"
End Sub

Private Sub Form_Timer()
    Dim lngCount As Long
    Me.TimerInterval = 0
    
    lngCount = DCount("*", "qryPension", "[DateOfPension] <= Date()")
    If lngCount > 0 Then
        If MsgBox(lngCount & " of employees has reached their pension(s). " & _
            "Would you like to view them?", vbInformation + vbYesNo) = vbYes Then
            If SysCmd(acSysCmdGetObjectState, acQuery, "qryPension") <> 0 Then _
                DoCmd.Close acQuery, "qryPension"
            DoCmd.OpenQuery "qryPension"
        End If
    End If
        
End Sub
Click to expand...

many thanks for you
last request can you change the query qrypension to form frmpension that is It better many thanks nice to meet you
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:24
Joined
May 7, 2009
Messages
19,237
i am away from computer right now and wont be back till tomorrow.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:24
Joined
May 7, 2009
Messages
19,237
create a Form out of qryPension.
click on the qryPension, (on Menu, Create->More Forms->Datasheet).
on its Property Sheet->Other->Pop Up: Yes.
save the form as frmPension.

change this code to:
Code: 
Private Sub Form_Timer()
    Dim lngCount As Long
    Me.TimerInterval = 0
    
    lngCount = DCount("*", "qryPension", "[Date Of Pension] <= Date()")
    If lngCount > 0 Then
        If MsgBox(lngCount & " of employees has reached their pension(s). " & _
            "Would you like to view them?", vbInformation + vbYesNo) = vbYes Then
            If SysCmd(acSysCmdGetObjectState, acForm, "frmPension") <> 0 Then _
                DoCmd.Close acForm, "frmPension"
            DoCmd.OpenForm "qryPension"
        End If
    End If
        
End Sub
 
You must log in or register to reply here.

Similar threads

T
Solved Form Auto Populated field
Replies
2
Views
483
MajP
MajP
B
Solved Data entry form how can I only show new record
Replies
2
Views
177
MajP
MajP
H
convert age to date of birth in three fields: year, month, and day.
Replies
3
Views
494
Pat Hartman
P
L
Solved Problem calculating correct age from birth to death
Replies
11
Views
349
GPGeorge
GPGeorge
U
Prevent duplicates in same month in access
Replies
13
Views
611
Usmantahir
U

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom