Passing variables from form to module

[sammylou]

I have the following module which correctly calculates the number of working days in a given month and accounts for Bank holidays by referencing a table within my DB. this all works fine when you run it direct from the module.

However I am struggling to change the module so that it can be referenced from any form in the DB. I want to pass the BegDate value in from a field on the form. I tried substituting the green lines below and putting "=Work_Days([Form],[Control])" in the On load Event of the form I am working with but it doesn't like it!

The module code is :
Function Work_Days()
'Public Function Work_Days(frm As Form, VarCtlName As Control)
Dim BegDate As Variant, EndDate As Variant
' Note that this function does account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer
Dim bhol As Variant
Dim mydb As Database

Set mydb = DBEngine.Workspaces(0).Databases(0)
'BegDate = frm(VarCtlName)
BegDate = DateValue("01/05/04")
EndDate = DateAdd("m", 1, BegDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt < EndDate
bhol = DLookup("Date2", "BankHolidays", "[Date2] =#" & Format(DateCnt, "Long Date") & "#")
'MsgBox (bhol)

If Format(DateCnt, "ddd") <> "Sun" And Format(DateCnt, "ddd") <> "Sat" And bhol = " " Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
MsgBox (Work_Days)
End Function

Can anyone advise me how to do this, variables are where my VBA coding gets weak! Thanks

 

[Mile-O]

You might be better served with one of my bank holiday/working day functions - no need, then, for a table at all. It calculates it all for you. Do a search on bank holidays.

 

[sammylou]

Thanks, I will reference that but I still need to know how to pass the variable controls accross as this always happens when writing vba code and I really need to know how to do it!

 

[sammylou]

I have referenced your Bank holiday code but
a) I don't really understand what it is doing (and I like to understand the code and

b) I have put the code:
Public Enum Dates
vbNewYear = 1
vbChristmas = 2
vbBoxingDay = 3
End enum
into the general declarations of a new module with the rest of the code underneath and it doesn't like the above code??

c) how do I run it to get the number of working days in a month exluding bank holidays?

d) I still need to pass my variable month/date into it???

 

[Mile-O]

Scrub the code you've taken. I think you've chose one from the past couple of days which was for a different circumstance.

Open a new Class Module and copy and paste this into it:

Option Compare Database
Option Explicit

Const vbNewYear = "01/01/"
Const vbChristmasDay = "25/12/"
Const vbBoxingDay = "26/12/"


Public Function DaysInMonth(ByVal dteMonth As Date) As Integer
    Dim dteStart As Date
    Dim dteEnd As Date
    
    dteStart = DateSerial(Year(dteMonth), Month(dteMonth), 1)
    dteEnd = DateAdd("m", 1, dteStart) - 1
    
    DaysInMonth = CalculateDays(dteStart, dteEnd, True)
    
End Function

Public Function CalculateDays(ByVal dteStart As Date, _
    ByVal dteEnd As Date, Optional Include As Boolean) As Long

    Dim dteTemp As Date
    Dim lngDays As Long
    dteTemp = dteStart
    
    Do While dteTemp <> dteEnd
        If Not IsHoliday(dteTemp) Then
            Select Case Weekday(dteTemp)
                Case vbMonday, vbTuesday, vbWednesday, vbThursday, vbFriday
                    lngDays = lngDays + 1
            End Select
        End If
        dteTemp = DateAdd("d", 1, dteTemp)
    Loop

    If Include = True Then lngDays = lngDays + 1
    
    CalculateDays = lngDays

End Function

Public Function IsHoliday(ByVal dteTemp As Date) As Boolean

    On Error GoTo Err_IsHoliday
    
    Dim intYear As Integer
    
    intYear = Year(dteTemp)
    
    If DCount("HolidayID", "tblHolidays", "HolidayDate = #" & dteTemp & "#") > 0 Then
        IsHoliday = True
        Exit Function
    Else
        Select Case dteTemp
            Case Is = CDate(vbNewYear & intYear)
                IsHoliday = True
                Exit Function
            Case Is = GoodFriday(intYear)
                IsHoliday = True
                Exit Function
            Case Is = EasterMonday(intYear)
                IsHoliday = True
                Exit Function
            Case Is = EarlySpringBankHoliday(intYear)
                IsHoliday = True
                Exit Function
            Case Is = LateSpringBankHoliday(intYear)
                IsHoliday = True
                Exit Function
            Case Is = SummerBankHoliday(intYear)
                IsHoliday = True
                Exit Function
            Case Is = CDate(vbChristmasDay & intYear)
                IsHoliday = True
                Exit Function
            Case Is = CDate(vbBoxingDay & intYear)
                IsHoliday = True
                Exit Function
            Case Else
                IsHoliday = False
        End Select
    End If
    
Exit_IsHoliday:
    Exit Function
Err_IsHoliday:
    IsHoliday = False
    Resume Exit_IsHoliday
End Function



Private Function EarlySpringBankHoliday(ByVal intYear As Integer) As Date
    Dim dteStart As Date, intWeekDay As Integer
    dteStart = DateSerial(intYear, 5, 1)
    intWeekDay = Weekday(dteStart)
    dteStart = DateAdd("d", IIf(2 < intWeekDay, 7 - intWeekDay + 2, 2 - intWeekDay), dteStart)
    EarlySpringBankHoliday = dteStart
End Function

Private Function LateSpringBankHoliday(ByVal intYear As Integer) As Date
    Dim dteStart As Date, intWeekDay As Integer
    dteStart = DateSerial(intYear, 6, 1)
    intWeekDay = Weekday(dteStart)
    dteStart = DateAdd("d", IIf(2 < intWeekDay, 7 - intWeekDay + 2, 2 - intWeekDay), dteStart) - 7
    LateSpringBankHoliday = dteStart
End Function

Private Function GoodFriday(ByVal intYear As Integer) As Date
    GoodFriday = DateAdd("d", -2, Easter(intYear))
End Function

Private Function Easter(ByVal intYear As Integer) As Date
    Dim intDominical As Integer, intEpact As Integer, intQuote As Integer
    intDominical = 225 - (11 * (intYear Mod 19))
    If intDominical > 50 Then
        While intDominical > 50
            intDominical = intDominical - 30
        Wend
    End If
    If intDominical > 48 Then intDominical = intDominical - 1
    intEpact = (intYear + Int(intYear / 4) + intDominical + 1) Mod 7
    intQuote = intDominical + 7 - intEpact
    If intQuote > 31 Then
        Easter = DateSerial(intYear, 4, intQuote - 31)
    Else
        Easter = DateSerial(intYear, 3, intQuote)
    End If
End Function

Private Function EasterMonday(ByVal intYear As Integer) As Date
    EasterMonday = DateAdd("d", -2, Easter(intYear))
End Function

Private Function SummerBankHoliday(ByVal intYear As Integer) As Date
    Dim dteStart As Date, intWeekDay As Integer
    dteStart = DateSerial(intYear, 9, 1)
    intWeekDay = Weekday(dteStart)
    dteStart = DateAdd("d", IIf(2 < intWeekDay, 7 - intWeekDay + 2, 2 - intWeekDay), dteStart) - 7
    SummerBankHoliday = dteStart
End Function

Save it as clsHolidays

Now, when you want to calculate the days in a month, you can use this code:

Dim c As New clsHolidays
Me.MyTextbox = c.DaysInMonth(Date)
Set c = Nothing

replace Me.MyTextbox with whatever you are wanting to assign the days in month to. Replace Date with any date in the month you wish to return a value for.

 

[sammylou]

thanks for your help. Have created a public module called clsHolidays and in my on click event of a button on my form I have added:

Dim c As New clsHolidays
Me.DaysinMonth = c.DaysinMonth(Me.DISStart)
MsgBox (DaysinMonth)
Set c = Nothing

where DaysinMonth is the control I want to pass the number of days to
and DISStart is the start date of the relevant month (i.e. 01/06/04)

when I compile the form code I get an error saying "A module is not a valid type", I have even tried declaring "Dim c As New clsHolidays" within the clsHolidays module but that doesn't work. I think I am being DIM now!! Please advise

 

[Mile-O]

I said Class Module and not a module.

To create a class module, you have to go to Insert -> Class Module

 

[sammylou]

Thanks, got it working but it doesn't work for May 2004 as it says there were 21 working days but there were only 19, May Day on 3 and spring Bank holiday on 31. Also December 2004 says 23 days instead of 21 Are these Bank holidays not included?

 

[Mile-O]

Strange, this example works. I did a slight tweek so that I could demonstrate it with a query.

 

[sammylou]

thanks but I can't read the DB it says "unrecognised Database format" and I can't import objects from it either. We're a bit behind here and still using Access 97 until we upgrade next week!! any chance of getting a 97 version of it

 

[Mile-O]

No problem. Both versions are included.

 

[sammylou]

Not sure why but when I import all your coding, tables query etc, I get one less than your database every time. i.e 21 days for Feb instead of 20. Also why do you need the empty tblHoliday????

 

[Mile-O]

Not sure why but when I import all your coding, tables query etc, I get one less than your database every time. i.e 21 days for Feb instead of 20.

Change the function:

Public Function DaysInMonth(ByVal dteMonth As Date) As Integer
    Dim dteStart As Date
    Dim dteEnd As Date
    
    dteStart = DateSerial(Year(dteMonth), Month(dteMonth), 1)
    dteEnd = DateAdd("m", 1, dteStart) - 1
    
    DaysInMonth = CalculateDays(dteStart, dteEnd, [b]True[/b])
    
End Function

to

Public Function DaysInMonth(ByVal dteMonth As Date) As Integer
    Dim dteStart As Date
    Dim dteEnd As Date
    
    dteStart = DateSerial(Year(dteMonth), Month(dteMonth), 1)
    dteEnd = DateAdd("m", 1, dteStart) - 1
    
    DaysInMonth = CalculateDays(dteStart, dteEnd, [b]False[/b])
    
End Function

Also why do you need the empty tblHoliday?

Any other holidays that can't be calculated or are specific to your business, etc.

 

[sammylou]

thanks very much for all your help. I have it all working fine now using a combination of your code and my table with bank holidays in. One thing to note though is that the reason I had to use my bank holiday table is that when christmas day and boxing day falls on Sat/Sun i.e in 2004, your code doesn't account for 27th and 28th December being the Bank holiday.

 

[Mile-O]

I'll add that condition to my list. There are other occasions - such as the Jubilee a few years ago that would make use of the table. I can't remember if I left the code with the extra Scottish holiday of 02-Jan in the code. If it is, and you are not in Scotland, then be careful.

 

[sammylou]

I can't remember if I left the code with the extra Scottish holiday of 02-Jan in the code. If it is, and you are not in Scotland, then be careful.

Don't worry, I commented the Scottish code out!