Creating a Holiday / Vacation Record for Staff

[Viscount]

Afternoon....

I would be grateful if I could have some help in creating a simple DB to record leave taken by members of staff.

The principle every member of staff is entitled to a number of days holiday and a number of study days.

I have created 2 tables

Table 1 - called Employee and has the following fields:

Employee ID (Autonumber) + Key
First Name
Surname
Title
Annual Leave
Special Leave
AL Remaining
SL Remaining

Table 2 - called Holidays has the following fields:

Holiday ID (Autonumber) + Key
Employee ID - linked by a one-many relationship to table 1
Start Date
End Date
Holiday Type
Duration

I have created a form to view the information. It has the information contained in Table 1 on display. In addition it has a sub-table linked to table 2 and displays the holiday dates relating the that employee.

The Duration field in the subtable has a control source from a module which looks like...

Function Work_Days(BegDate As Variant, Enddate As Variant) As Integer
' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

    BegDate = DateValue(BegDate)

    Enddate = DateValue(Enddate)
                      
        WholeWeeks = DateDiff("w", BegDate, Enddate)
        DateCnt = DateAdd("ww", WholeWeeks, BegDate)
        EndDays = 0
        Do While DateCnt < Enddate
            If Format(DateCnt, &quot;ddd&quot;) <> &quot;Sun&quot; And _
                          Format(DateCnt, &quot;ddd&quot;) <> &quot;Sat&quot; Then
                    EndDays = EndDays + 1
            End If
            DateCnt = DateAdd(&quot;d&quot;, 1, DateCnt)
        Loop
        Work_Days = WholeWeeks * 5 + EndDays
End Function

Although the calculation is being completed in the subtable - it is not passing the information back to table 2 - I have created a query that updates the information - but I have not got it running automatically when changes are made.

I am also trying to use the DSUM Function to keep complete the AL and SL Remaining Fields in Table 1 - having a few problems..!

I am using the follow line in the control source...

=(DSum("Duration","Holiday","[Employee]![Employee ID]=[Holiday]![Employee ID]) AND [Holiday]![Holiday Type]='Annual Leave' &quot,0)

Can anyone spend a little bit of time and give me a few pointers. I am not very clever when it comes to Access so - please be gentle..!

Thanks for your help.

Vis.

 

[Viscount]

I forgot to mention that I am using Access 2000

 

[bocker]

Try this!

http://www.referee-assistant.com/calcontrol.htm

 

[Viscount]

Thanks Bocker - it is a good start