Yearly calendar for attendance 11

[oxicottin]

Hello, I have been using excel for employee attendance and im sick of tabs ect. I have been looking for a MS access database that shows 12 months on a form and on the form your able to select a day and enter a attendence entry ect. Does anyone know of anything free out there Or a sample DB I can get started on? calendar stuff is quite confusing so I want to find a sample to start from.

Thanks!

Thanks,
SoggyCashew.....

 

[PHV]

Im I looking in the correct place?
I don't think so:

...
intDay = i - intOffSet        'Transforms label number to day in month
If intDay > 0 And intDay <= DaysInMonth Then
    ctl.Caption = intDay
    If isHoliday(FirstDayOfMonth + (intDay - 1)) Then ctl.BackColor = 16776960    'Color holiday backcolor Blue
    ctl.Visible = True
Else
    ctl.Visible = False
End If
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

So how this works.
1) you determine the offset for each month. The first column of the grid is Saturday. So if a month started on Saturday the offset is 0. If the month starts on Wednesday the offset is 4.
If you wanted to change the start day of the grid you would have to modify the offset formula.

2) So assume your month starts on Wed with an offset of 4. You cycle through all the columns (1 to 37). To demonstrate above the labels is the index (I) as you loop the controls. Intday is I - the offset shown below the label. If intday is greater than 0 and less than or equal to the days in the month then show it, else do not show it.

[tt]
1  2   3   4    5    6     (i)                 35   36
Sa Su Mon Tues Wed Thurs................       Fri  Sa
-3 -2 -1   0    1    2   intDay=(i-offset)     31   32 
[/tt]

You nested a converse condition like this

if X > 10 then
  do something
  if x < 10 then 
    do something else
  end if
end if

no chance it would ever check x < 10.

 

[MajP]

For practice I built this in VB.net with an access backend. It is unfortunate that Access does not have a native unbound grid control like the VB.net datagrid. This makes doing this much easier and provides some real flexibility and features. Some nice features is that it is scrollable allowing to provide more real estate. Each row and column is sizeable dynamically. You have control over each cell. ADO.net (unrelated to ADO) gives you so much control over the data that makes reading and writing to the DB very easy for something like this. I will post it when done.

 

[oxicottin]

PHV, I added the visible and it worked, Thanks... BUT I noticed something else. For some reason in September its not showing the last 4 day numbers of the month. If you select MetalShop/Bi,Todd he has information for absences.

I had also noticed another issue. Every time you open up the frm_CalendarInputBox by clicking on a txt box it creates a record
no matter if you enter anything in or not. I think its because of when the input box opens it has the date and employees name entered so that starts the record?

SKYDRIVE ATTACHMANT LINK 3





Thanks,
SoggyCashew.....

 

[oxicottin]

Well I figured out why those lbls wasn't showing. I had the properties "Top Margin" set like the txtbox's margin..Whops! But I still am wondering how to fix the input box creating a record every time it clicked.

Thanks,
SoggyCashew.....

 

[MajP]

In the table make absenceID, employeeID, and date all required fields.

 

[MajP]

I like how you reformat it to put the label into the textbox upper corner. Did you do that by hand or move it in code?

 

[oxicottin]

MajP, I did them by hand . as for making those fields required wouldn't there be a way to have possibly a msgbox open if there isn't a record with that date/employee and asking if I want to create a absence or not OR if there is already a record then just open the inputbox.

Thanks,
SoggyCashew.....

 

[MajP]

You could but that seems the opposite of what you would want. If the user double clicks an empty day, then pretty sure they want to create an absence. I would not want a msgbox asking me if I wanted to create a record. By making the fields required, it opens to a new record with the defaults set. But if they hit close without an absence it should just close and not create an empty record. I would think that is what you want.

 

[oxicottin]

Majp, I see what you mean but I feel better having a box come up then having to delete the record because of a possible whops I hit the wrong date. Below is the added code to get it working and an updated database... I found another issue, you can still click on an text box even though there isn't a date in the txt box. How can I disable/enable these text boxes?

SKYDRIVE ATTACHMANT LINK 4

Public Function gridClick()
'This just demoes a single function that fires when any of the grid text boxes are clicked
    Dim ctl As Access.Control
    Dim strMonth As String
    Dim intCol As String
    Dim intMonth As Integer
    Dim intDay As Integer
    Dim frm As Access.Form
    Dim intYear As Integer
    Dim selectedDate As Date
    Dim empID As Long
    Dim strWhere As String    '============Added

    Set ctl = Screen.ActiveControl
    Set frm = ctl.Parent
    strMonth = Replace(Split(ctl.Tag, ";")(0), "txt", "")
    intCol = CInt(Split(ctl.Tag, ";")(1))
    intYear = CInt(frm.cboYear.Value)
    intMonth = getIntMonthFromString(strMonth)
    intDay = intCol - getOffset(intYear, intMonth, vbSaturday)
    selectedDate = DateSerial(intYear, intMonth, intDay)
    empID = Nz(frm.cboEmployee, 0)

    'Since you know the date you could now open a form to
    'add, edit, or delete a value for that date and that empID
    'MsgBox selectedDate & " EmpID" & empID

    strWhere = "AbsenceDate = #" & selectedDate & "# AND EmployeeID = " & empID    '============Added

    If DCount("*", "tbl_YearCalendar", strWhere) Then    '============Added
        'Date/Emp already exists open frm_CalendarInputBox
        DoCmd.OpenForm "frm_CalendarInputBox", , , , , acDialog, Format(selectedDate, "mm/dd/yyyy") & ";" & empID
    Else
        Const cstrPrompt As String = "Absence record does not exist for this date.  Create a new Absence?"
        If MsgBox(cstrPrompt, vbQuestion + vbYesNo) = vbYes Then
            'Yes I want to create an event
            DoCmd.OpenForm "frm_CalendarInputBox", , , , , acDialog, Format(selectedDate, "mm/dd/yyyy") & ";" & empID
            'refill the text grid when frm_CalendarInputBox closes
            FillTextBoxes Forms("frm_YearCalendar"), empID, intYear
            Forms!frm_YearCalendar!cmdTransparentButton.SetFocus    '============Added so cur isnt sitting on text box
        End If
        'Nope lets get outa here
        Forms!frm_YearCalendar!cmdTransparentButton.SetFocus    '============Added so cur isnt sitting on text box
        Exit Function
    End If
    FillTextBoxes Forms("frm_YearCalendar"), empID, intYear
End Function

Thanks,
SoggyCashew.....

 

[MajP]

If u hit the wrong day then you just close the form and no record gets created. U would not have to delete anything. In the click event u return the year, month, and the intday. The int day is the column minus the offset. You will need the number of days in the month. U have that function. Make sure the intday is greater than 0 and less than or equal to the number of days in month.

 

[oxicottin]

In the click event u return the year, month, and the intday. The int day is the column minus the offset. You will need the number of days in the month. U have that function. Make sure the intday is greater than 0 and less than or equal to the number of days in month.

Ok, I got it to working by adding a few lines to the (Public Sub FillMonthLabels) which disabled if there wasn't a date and enabled if there was a date. Now my next issue is I want to show a grid below the calendar in the forms footer that would show all the absences and times for the year/employee in question.

Example: Year 2013, Employee 13 (Bi, Todd)

He has two Disciplinary leaves = 20hrs and 5 vacation days = 50 hours so the grid would show:
--------------------------------------
Disciplinary leave - 20
Vacation - 50
--------------------------------------
How would I accomplish this?

SKYDRIVE ATTACHMANT LINK 6



Thanks,
SoggyCashew.....

 

[MajP]

build a Group by query where you group on employee, absence type, and Year(absence date),and sum absence type. In the footer but a subform control. Use the query as the rowsource of the subform. Then linke the subform to both the comboboxes. It would look something like

Link Master Fields: [comboEmployee];[ComboYear]
Link Chiled Fields: [EmployeeID];[YearAbsence]

If you do not want to use the link you could do it also in code by using the filterby property for your subform. Then on the combo change events something like

dim strFilter as string
strFilter = "EmployeeID = " & me.comboEmployee & " AND YearAbsence = " & me.comboYear
me.subformname.form.filterby = strFilter
me.subformname.form.filteron = true

 

[oxicottin]

Worked great.... Once I get it looking good Ill post next version.

Thanks,
SoggyCashew.....

 

[oxicottin]

Almost done im adding another subform to show all dates ect for absences.

Thanks,
SoggyCashew.....

 

[MajP]

Make sure you save a copy and than compact and repair the database. When you have a form with this many controls they can get corrupt. Access has a limit to that amount of controls over the life of the form. This includes deleted controls.

 

[oxicottin]

MajP, I didn't know you were limited to controls on a form.... I gotta be close to the limit!! I have compact on close set so I should be good with deleted but what is the limit?

I added the other subform and query that shows by day/year/employee and changed a few colors. I got another question, instead of having a module with set holidays can I have a table that would have "Holiday Dates" and each date in the table would be like what the module does. How would this be done.


SKYDRIVE ATTACHMANT LINK 7

Thanks,
SoggyCashew.....

 

[MajP]

Making a table of holidays is a common approach. So just make a new procedure called formatholidays. Then look at the filltextboxes code. The only difference is the query. Build a recordset of holidays for the year. Loop the rs and instead of filling the text box you format.

 

[MajP]

The limit use to be 745 over the life of the form. Not sure if it has been increased. If you need to build a new frm and copy paste into that. Then delete the old form.

 

[oxicottin]

Majp, Im lost on how I would do this. Im thinking about how it would show for each year from th table.

For instance:
If I created a table (tbl_Holidays) with a (PK-HolidayID) and a Date/Time Field called (HolidayDate) and I entered 12/14/13 as a entry for a holiday. my question is I entered the year in the table so when I bring up the calender and look at the 2013 callendar it would show 12/24/13 as a holiday BUT if I go to the yea 2014 then it wouldnt show the holiday.

The way you have it now works for every year so how would I do it with the table and query? Could you give me an example?

Thanks,
SoggyCashew.....