Attendance Calendar questions Continuing thread702-1723375 2

[stevief46]

I have run into an issue with the frm_YearCalendar month of Decembember When I went back and checked the original db I found that it also has this problem.

When I click on a date to enter an absence I get a Run-time error '13': Type mismatch. When I click on debug the following (image below) comes up. It is only the month of December. Can you tell me what I should be looking for to fix it?

Thanks in advance for your help

 

[dhookom]

Check out faq705-7148 to determine the value of strMonth.

Duane
Hook'D on Access
MS Access MVP

 

[stevief46]

Thanks Duane, great for future reference. Would that it was that simple. MajP - can you or Oxicottin help?

 

[dhookom]

What is the value of strMonth? It is supposed to be as listed in the comments of the function.

Ideally the function would have some type of error handling for strMonth values that are not valid.

Duane
Hook'D on Access
MS Access MVP

 

[stevief46]

Sorry - It is in reference to the Yearly Attendance form in thread 702-1723375 I haven't touched Access in the past 5+years (or any coding for that matter) it has been a real jolt to start thinking VBA again. The database is large and detailed. I have skimmed through most of the code trying to re-acclimate myself. MajP and Oxicottin were the two major architects of the code and the finished product is almost exactly what I need. This wrinkle showed up today and it is probably a simple fix, but I have been away from it for too long. The final link at the bottom of the thread contains the db I am adapting. I am taking your posts to heart as it is really good info - and I will use it. But right now I need an answer to the problem. Thanks for your help!

 

[dhookom]

You need to find and change this line of code in the Form Load event of frm_YearCalendar from :

 sFrmDec.LblMonth.Caption = "Decembember"

to

 sFrmDec.LblMonth.Caption = "December"

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

There is a similar error in the form rpt_YearView.

Duane
Hook'D on Access
MS Access MVP

 

[stevief46]

Thank you! I had already changed one of them, hadn't found those other two instances yet. Will work on it in the am when I get back to work. Happy Thanksgiving to you and yours! Stef

 

[MajP]

To understand the issue.
The form and the report have 12 subforms which are the months. The twelve subforms are actually the same subform repeated 12 times. The code then "personalizes" each of the subforms. One thing it does it creates a label for each subform so december shows december, january shows january. Then when you click on the subform you can determine which month it represents by the lable.

In the load event of the form and also the report, there is some code to do the personalization. Unfortunately there is a spelling error.

sFrmDec.LblMonth.Caption = "Decemember"

Then when you click on a subform calendar it runs the function called "gridClick" .
There is a line in that function
IntMonth = getIntMonthFromString(sFrm.LblMonth.Caption)

Then function getIntMonthFromString looks like

Public Function getIntMonthFromString(strMonth As String) As Integer
'Assume Jan, Feb..Dec
    getIntMonthFromString = Month("1/" & strMonth & "/2013")
End Function

This function bombs out because December was spelled wrong in the original code, and there is no date such as
1/Decemember/2013

And to get the hyperlink you need to remove the space between the word thread and the number.
thread702-1723375
But the good news if you had closely read all 201 replies you would have seen we caught this issue already.

 

[stevief46]

Only 201 replies? LOL You seemed to have worked it all out. But thank you for the explanation. I am going to be changing it a bit to fit other needs, but am learning so much from what is there - plus memory is being tweaked so I feel more comfortable. Happy Thanksgiving to you also.

 

[stevief46]

MajP - here is the link I am talking about. It's in the 1st thread. It's One Drive - which used to be Sky Drive. Can't get it here at work. May be able to grab it at home though on Google, but def. have Dropbox. Thanks.

 

[MajP]

Okay, got it. That was Oxicotin's link not mine. The version I posted, was close to the last version he had.

 

[stevief46]

I have run into another snag. The frm_YearCalendar appears to be working just fine - until I noticed that the dates/days are not in sync with 2014 - and instead match up to those of 2013. The date in the cbo box is correct. I again went back to the original download db and it is the same in that one so it wasn't something I did. :-(

I am going through and re-read the 201 posts to see if I can find any direction on it, but if you know off hand where I should look I would appreciate it. Thanks!

 

[MajP]

The version I have appears to be working fine. What happens when you change the combo box?
So this is how it works. In the load event it calls the procedure, FillAllMonthLabels. This basically just calls the procedure FillSubFormMonthLabels 12 times, once for each subform. It passes in the year from the combobox

FillAllMonthLabels (Me.cboYear)

Private Sub Form_Load()
    DoCmd.ShowToolbar "Ribbon", acToolbarNo    'Hides the toolbar
    Set sFrmJan = Me.subFormJan.Form
    Set sFrmFeb = Me.SubFormFeb.Form
    Set sFrmMar = Me.subformMar.Form
    Set sFrmApr = Me.subFormApr.Form
    Set sFrmMay = Me.SubFormMay.Form
    Set sFrmJun = Me.SubFormJun.Form
    Set sFrmJul = Me.subFormJul.Form
    Set sFrmAug = Me.subFormAug.Form
    Set sFrmSep = Me.subFormSep.Form
    Set sFrmOct = Me.SubFormOct.Form
    Set sFrmNov = Me.subFormNov.Form
    Set sFrmDec = Me.subFormDec.Form

    '//Fills the subFomrMonths LblMonth with the appropiate month
    sFrmJan.LblMonth.Caption = "January"
    sFrmFeb.LblMonth.Caption = "February"
    sFrmMar.LblMonth.Caption = "March"
    sFrmApr.LblMonth.Caption = "April"
    sFrmMay.LblMonth.Caption = "May"
    sFrmJun.LblMonth.Caption = "June"
    sFrmJul.LblMonth.Caption = "July"
    sFrmAug.LblMonth.Caption = "August"
    sFrmSep.LblMonth.Caption = "September"
    sFrmOct.LblMonth.Caption = "October"
    sFrmNov.LblMonth.Caption = "November"
    sFrmDec.LblMonth.Caption = "December"

    FillCombo    'Fills cboYear combo box
    FillAllMonthLabels (Me.cboYear)    'Fills the subFormMonths days for the year based on cboYear
    FillAllHolidays Me.cboYear    'Fills the subFormMonths holidays for the year based on cboYear

    Me.cboSupervisor.SetFocus    'Sets focus to combobox

End Sub

Private Sub FillAllMonthLabels(TheYear As Integer)
'Used to fill in month labels
    mod_FillMonthLabels.FillSubFormMonthLabels sFrmJan, TheYear, 1
    mod_FillMonthLabels.FillSubFormMonthLabels sFrmFeb, TheYear, 2
    mod_FillMonthLabels.FillSubFormMonthLabels sFrmMar, TheYear, 3
    mod_FillMonthLabels.FillSubFormMonthLabels sFrmApr, TheYear, 4
    mod_FillMonthLabels.FillSubFormMonthLabels sFrmMay, TheYear, 5
    mod_FillMonthLabels.FillSubFormMonthLabels sFrmJun, TheYear, 6
    mod_FillMonthLabels.FillSubFormMonthLabels sFrmJul, TheYear, 7
    mod_FillMonthLabels.FillSubFormMonthLabels sFrmAug, TheYear, 8
    mod_FillMonthLabels.FillSubFormMonthLabels sFrmSep, TheYear, 9
    mod_FillMonthLabels.FillSubFormMonthLabels sFrmOct, TheYear, 10
    mod_FillMonthLabels.FillSubFormMonthLabels sFrmNov, TheYear, 11
    mod_FillMonthLabels.FillSubFormMonthLabels sFrmDec, TheYear, 12
End Sub

so this does all the work for filling the labels. As you can see you pass in a year, and tell it which month the subform represents. The only way this can default to 2013 if the year is not being passed in correctly. So debug the value that is being passed.

Public Sub FillSubFormMonthLabels(frm As Access.Form, TheYear As Integer, TheMonth As Integer)
'==================================================================================================
'//Fills the grids label(s) with the correct day and;
'  1) Hides day labels that dont have a date associated with them
'  2) Disable and locks text boxes without a date so data cant be entered
'==================================================================================================
    Dim ctl As Access.Label
    Dim ctlt As Access.TextBox    'Added to disable/lock text boxes without a date so data cant be entered
    Dim I As Integer
    Dim FirstDayOfMonth As Date   'First of month
    Dim DaysInMonth As Integer    'Days in month
    Dim intOffSet As Integer      'Offset to first label for month.
    Dim intDay As Integer         'Day under consideration.
    Dim ParentIsForm As Boolean
    Const ctlBackColor = 14211288    'Gray color thats used for Holiday shading/unshading
    'Need to know if the parent is a form or report because you can not do certain things with reports
    ParentIsForm = (TypeOf frm.Parent Is Access.Form)

    FirstDayOfMonth = getFirstOfMonth(TheYear, TheMonth)
    DaysInMonth = getDaysInMonth(FirstDayOfMonth)   'Days in month.
    intOffSet = getOffset(TheYear, TheMonth, vbSaturday)    'Offset to first label for month.
    ' Debug.Print DaysInMonth
    If ParentIsForm Then frm.cmdSubFormTransButton.SetFocus    'Sets focus to a transparent button in the subcalendar form
    For I = 1 To 37
        Set ctl = frm.Controls("lbl" & I)
        Set ctlt = frm.Controls("txt" & I)    'Added to disable/lock text boxes without a date so data cant be entered
        ctl.Caption = ""
        ctl.BackColor = ctlBackColor  'Resets the backcolor to Gray
        intDay = I - intOffSet        'Transforms label number to day in month
        If intDay > 0 And intDay <= DaysInMonth Then
            ctl.Caption = intDay  'Displays day number in correct label
            If ParentIsForm Then ctlt.Enabled = True
            'Added to enable textbox(s) that have a date associated with them
            ctl.Visible = True    'Added so the months labels that display a date show on the grid
        Else
            If ParentIsForm Then ctlt.Enabled = False    'Added to disable/lock text boxes without a date so data cant be entered
            ctl.Visible = False    'Added so months lables that don't display or have a date do not show on grid
        End If
    Next I
End Sub

to debug you can add a message box here
FillCombo 'Fills cboYear combo box
msgbox me.cboYear
FillAllMonthLabels (Me.cboYear)

 

[stevief46]

Oxicottin - I have a question on the summary of vacations etc. subform on the Yearly Calendar. I looked up the function fElapsedTime and it said that it counts all the time between two dates. When calculating how much time an employee accrues for vacation .833 a day here - it is based on days worked - which means a 5 day work week - so the 'all the days between' doesn't work as it counts weekends and holidays that occur during the week. Unless I missed something that takes that into consideration?

I found this attached bit of code, but am stumped about when to call this function to make it work with your code in mod_YearsOfService(code). I have a Holiday table with a similar name and a field that is correctly named so I only have to change one thing to make it match.

Another question too - We don't use purchased vacation days. The option right now is to just ignore it, leave it at zero/null purchased until I can eliminate it. Is there a good way to go about doing that?

Thanks for your help!
STef aka Stevie aka Stephanie

 

[MajP]

Can you be a little more specific of what you need?
I interpret this to mean you would like to provide two dates for an employee, and count all the days worked. So to do this you need to get the amount of days in between, subtract the holidays, subtract the weekends, and maybe subtract the employee absences. Do you accrue time for a sick day? Also I assume you would want to see the answer in days, where as the FelaspsedTimeYMD gave the answer in Years, Months, Days.

 

[oxicottin]

fElapsedTime is a bit of code I found off the net and I cant remember where.... It counts every day Sun-Sat including holidays or whatever and takes leap year into consideration.

As for removing the paid vacation how about I remove it and upload a new copy because I tried to write it down and there is a lot of deleting...

stevief46, MaJp would be the expert to ask if you needed help in code if you can tell 200 posts later he helped me get through this attendance database.





Thanks,
SoggyCashew.....

 

[MajP]

If I run the function Work_Days_Minus_Holidays
using today and 12/16/2014

work_Days_Minus_Holidays(now, #12/26/2014#)
It returns 9

1 day this week (since it is friday today)
5 days next week (no holidays)
then 22,23,and 26 (off 24, and 25)

Public Function Work_Days_Minus_Holidays(BegDate As Date, EndDate As Date) As Long
  'Figures out the M-Fs between two dates then subtracts any holidays
  Work_Days_Minus_Holidays = Work_Days(BegDate, EndDate) - HolidaysBetween(BegDate, EndDate)
End Function

Function Work_Days(BegDate As Date, EndDate As Date) As Long
  'This gets the workdays (M-f) between two dates
  'You still have to remove the holidays
  Dim WholeWeeks As Variant
  Dim DateCnt As Date
  Dim EndDays As Integer
      
  On Error GoTo Err_Work_Days
  
  BegDate = DateValue(BegDate)
  EndDate = DateValue(EndDate)
  WholeWeeks = DateDiff("w", BegDate, EndDate)
  DateCnt = DateAdd("ww", WholeWeeks, BegDate)
  EndDays = 0
  Do While DateCnt <= EndDate
     If Not IsWeekend(DateCnt) Then
        EndDays = EndDays + 1
     End If
     DateCnt = DateAdd("d", 1, DateCnt)
  Loop
  Work_Days = WholeWeeks * 5 + EndDays
Exit Function
Err_Work_Days:
  If Err.Number = 94 Then
     Work_Days = 0
        Exit Function
  Else
     MsgBox "Error " & Err.Number & ": " & Err.Description
  End If
End Function
Public Function HolidaysBetween(BegDate As Variant, EndDate As Variant) As Long
  'Returns the number of holidays in between two dates
  'Using a table of Holidays
  ' Note it is up to the developer to ensure that the range of dates does not exceed
  ' the data in the table.
  ' Also this assumes that you do not list holidays in your table that fall in on the weekend
  Const TableName = "tbl_Holidays"
  Const FieldName = "HolidayID"
  Dim strSql As String

  strSql = "HolidayDate Between " & SQLDate(BegDate) & " AND " & SQLDate(EndDate)
  HolidaysBetween = DCount(FieldName, TableName, strSql)
End Function

Public Function IsWeekend(dtmDate As Date) As Boolean
  Select Case Weekday(dtmDate, vbSunday)
  Case vbSunday, vbSaturday
    IsWeekend = True
  End Select
End Function

Function SQLDate(varDate As Variant) As Variant
     If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

 

[MajP]

Should say "using today and 12/26/2014

 

[stevief46]

Oxicottin - thank you!
MajP, What I am trying to figure is how many hours of time for vacations has been accrued up to 'today's date' during the current year. Our employees work 7 hours or 8 hours per day, and PT are 29 hours a week. If I can get hours I can figure the accrued time for days worked on any given date during the year. The information would always be based on the date the report is run. The amount of accrued time is based on how many years an employee has worked. In this case <=5, 5 to 14 and >=15, where each increment sees an increase in the amount of time accrued... So an employee who has worked 6 years accrues 1.25 hours per workday. There are some other bits of corporate fancy footwork that need to be factored in but for the moment I want to keep it fairly simple.
I am working as temp and my last day is Monday (for now - I am a 'recurring' temp). I plan on keeping on working on this lovely puzzle until I have it all figured out and adapted to this place. Credits belong to the two of you! I'll post what I end up with for sure.