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.....

 

[oxicottin]

MajP, im up for learning if you want to teach? If not thats fine to... Im thinking why not a subform with a query that would show kinda like tasks in outlook?

Thanks,
SoggyCashew.....

 

[jguesman14]

Oxicottin I am with you......I love to learn new things What I have read from this forum MajP is a great teacher!

 

[MajP]

This may be simpler than I thought after rereading. I assumed that you wanted a week view calendar like Outlook with sunday to saturday across the top, time down the side and events in the column. Also the same feature as the database year view for adding, editing, deleting events for each person. But if you just want

s there anyway that I can add a "week view" of everyone that is scheduled off for the week with the hours and amount of time they are scheduled off?

That sounds more like a simple sub form and could be done simply with a native continous form. It could be bound. You would just do a "group by" employee query and sum the scheduled off time. The only issue would be passing to the query the week start and week end dates. I personally am not a fan of having references to form controls within the sql. I find this not very flexible, hard to test, and not scalable. I am more apt to build that query in code and then make it the recordsource of the form.
So if that the case the only real requirements are functionality to
1) select a week to review
2) navigate to other weeks
3) Create the query for the week selected on the form
4) assign that query as the recordsource of the form

 

[oxicottin]

MajP, Im thinking it would be way cooler to have a subform that could be added to the frm_YearCalendar and act like the Outlook week event that would show absences for the week showing in the subform and in its dates row. Mabe use the =gridClick() when a text box is clicked on to change an event for that date row like in the calender but it would have to not be able to click in empty spots because you couldnt add an event you could just change or delete. It would also move by week up or down....

Thanks,
SoggyCashew.....

 

[jguesman14]

SoggyCashew this is exactly what I want to do. Please tell me how!

 

[MajP]

To do the Week view you need a crosstab query. Where the dates become the columns. However in order to ensure this works, you need to make sure there is a value for every day. One way to do this is to create a giant table of dates and do a left join on that. Since I only need 7 dates at a time, I will create a table of dates and fill it with the seven dates I need. Makes it a lot simpler.

tblDates
dateNumber ' 1 to 7
dtmDate ' the actual date

this table only holds the seven dates at a time. That datenumber field is key because it is used in the crosstab as a column heading and my table can be bound to it. If you did not do this you would have to programmatically change the recordsource or the headings.

Now the next big trick is that you need a row heading for the crosstab. So I had to do a ranking query. I could not think of any other way to do this, but there might be a better way. So Here is the Prep that I did

qryWeekViewPrep

SELECT 
 tblDates.DateNumber, 
 tblDates.dtmDate, 
 [AbsenceCode]+": "+[EmplName]+", "+[EmpFName] AS EventInfo, 
 Count(*) AS Rank
FROM 
 (tbluEmployees 
 RIGHT JOIN 
 (tbluAbsenceCodes 
 RIGHT JOIN 
 (tblDates LEFT JOIN tbl_YearCalendar AS YC1 ON tblDates.dtmDate = YC1.AbsenceDate) 
 ON 
 tbluAbsenceCodes.AbsenceID = YC1.AbsenceID) 
 ON 
 tbluEmployees.EmployeeID = YC1.EmployeeID) 
 LEFT JOIN tbl_YearCalendar AS YC2 
 ON (YC1.AttendanceID >= YC2.AttendanceID) AND (YC1.AbsenceDate = YC2.AbsenceDate)
GROUP BY 
 tblDates.DateNumber, 
 tblDates.dtmDate, 
 [AbsenceCode]+": "+[EmplName]+", "+[EmpFName]
ORDER BY tblDates.dtmDate;

The join looks pretty complicated but the only tricky part is
LEFT JOIN tbl_YearCalendar AS YC2
ON (YC1.AttendanceID >= YC2.AttendanceID) AND (YC1.AbsenceDate = YC2.AbsenceDate)

That is used to get your rank. So if 4 records occur on a given day they will be numbered 1 to 4

Now the crosstab is this

TRANSFORM 
 First(qryWeekViewPrep.EventInfo) AS FirstOfEventInfo
SELECT 
 qryWeekViewPrep.Rank
FROM 
 qryWeekViewPrep
GROUP BY 
 qryWeekViewPrep.Rank
PIVOT 
 qryWeekViewPrep.DateNumber;

The form code is pretty simple

Private Sub Form_Load()
  'I could code here to pass in the starting day in week using open args
  'Or you could do it after the form opens using the SetStartingDay
  Dim DayInTheWeek As Date
  DayInTheWeek = Date
  FirstDayOfWeek = (GetFirstDayOfWeek(DayInTheWeek, vbSunday))
  UpdateCalendar
End Sub

Public Sub SetStartingWeek(DayInTheWeek As Date)
  'if not using open args you could pass a day to start the calendar
  Me.FirstDayOfWeek = (GetFirstDayOfWeek(DayInTheWeek, vbSunday))
  UpdateCalendar
End Sub

Public Property Get FirstDayOfWeek() As Date
  FirstDayOfWeek = mFirstDayOfWeek
End Property

Public Property Let FirstDayOfWeek(ByVal Value As Date)
   mFirstDayOfWeek = Value
End Property

Private Sub cmdNext_Click()
  FirstDayOfWeek = FirstDayOfWeek + 7
  UpdateCalendar
End Sub

Private Sub cmdPrevious_Click()
  FirstDayOfWeek = FirstDayOfWeek - 7
  UpdateCalendar
End Sub

Public Function GetFirstDayOfWeek(DayInWeek As Date, Optional StartDay As vbDayOfWeek = vbSunday)
   GetFirstDayOfWeek = DayInWeek - Weekday(DayInWeek, StartDay) + 1
End Function

Public Sub UpdateCalendar()
  SetLabels
  LoadDates
End Sub

Public Sub SetLabels()
  Dim I As Integer
  Dim labelDay As Date
  labelDay = FirstDayOfWeek
  Me.lblDateRange.Caption = Format(labelDay, "dd MMM yyyy") & " - " & Format(labelDay + 6, "dd MMM yyyy")
   For I = 1 To 7
    Me.Controls("lblDay" & I & "number").Caption = Format(labelDay + (I - 1), "dd")
  Next I
End Sub

Public Sub LoadDates()
  Dim strSql As String
  Dim I As Integer
  'Delete out old dates
  strSql = "Delete * from tblDates"
  CurrentDb.Execute strSql
  For I = 0 To 6
    strSql = "Insert into tblDates (dateNumber,dtmDate ) values (" & I + 1 & ", " & SQLDate(FirstDayOfWeek + I) & ")"
    Debug.Print strSql
    CurrentDb.Execute strSql
  Next I
  Me.Requery
End Sub

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

You would pass in as starting date somehow. I did not code openargs but you could. I did provide a method to pass in a date after the form loads. This will work fine unless you open this form as dialog. If you pass in a date (or use the default) it calculates the first day of the week. It loads the labels starting from that date. Then it fills tblDate with the 7 required dates. The crosstab does everything else.

Lesson 2 would be clicking on a cell and opening the form to add/edit.
1) To do this you would return the value in the cell
2) if there is a value parse it to find the empolyee name
3) calculate which day it is by getting the field name (1,2,3,4...) and the the starting date which is a property of the form
4) use a dlookup to open to that record just like in the year calendar.

Here is a link

http://www.4shared.com/file/huHa7372ba/Attendance_V8.html

be careful of the spam.

 

[oxicottin]

Majp, awesome I think this is what jguesman14 was wanting and Im going to incorporate it into mine as well. I'm having an issue opening it in 2007 due to something in the week view that is needing Access 2010 I am getting a compatibility error that takes me to MS LIBRARY HERE I think it is a reference but which one? I have access 2010 and 2013 at home and It works perfectly except for the 2007 compatibility issue. Thoughts?



Thanks,
SoggyCashew.....

 

[oxicottin]

I am also getting this message when closing the database in 2007...

Thanks,
SoggyCashew.....

 

[MajP]

Did you import it into an existing database or are you running the version I sent? Try using the import feature into a 2007 database. If that does not work then simply rebuild the form, it is really basic should not take more than 15-20 minutes. Just use the same naming convention I did and drop the code.

 

[jguesman14]

This is exactly what I am looking for! I have already imported into my existing database and it works perfectly. One final question......how can I incorporate the color code for the leave codes into the weekly view? That's not a must but it is nice addition!

BTW......MajP and oxicottin you are both amazing!

 

[MajP]

I thought this was doable. The trick is to wrap the text with the tags that are already stored in the database. This could be done with a simple function. So assume you could write the function. You would pass in the codes and the text to wrap.

Something like
Public function TagText(ColorTag,TextToWrap) as string
'code to wrap
end function

Then use this in the qryWeekViewPrep

SELECT
tblDates.DateNumber,
tblDates.dtmDate,
TagText([AbsenceColorTag], [AbsenceCode]+": "+[EmplName]+", "+[EmpFName]) AS EventInfo,
Count(*) AS Rank

So now the information that gets displayed in the week view would be wrapped in tags. Should work, but there is a problem. For some reason fields bound to a crosstab cannot be set to rich text. Without being able to set the controls to RT you are stuck. Access will not allow it. Also conditional formatting is not possible because there are well beyond 4 format conditions. There is no way to work around this with out a complete redesign. The possible work around would be to insert the values into a Temp table. It is a little convoluted but should work.

 

[oxicottin]

Majp, yes that worked thanks! .... I have a question. I wanted to add it as a subform in the footer and have it linked to only employees that pertain to the "Supervisor" selected. How would I acomplish this?

Thanks,
SoggyCashew.....

 

[jguesman14]

Yes I think I will live without the color! LOL

 

[oxicottin]

Majp, I noticed something about the weekGrid. If you enter two or more employees off lets say on a Monday (same day) then the first employee disapears but row below shows the second employee and if I enter another employee off on the same day for Monday then the first two rows in Monday will be blank and the third one will have the third employees name.

Thoughts?

Thanks,
SoggyCashew.....

 

[MajP]

The crosstab and ranking query where kicking my ass, so I just coded this and it was for me far simpler. This solved both problems.

1) The trick is to build a non-normal table a table for the form. This table will serve as a grid that you write to each time. To use Rich Text the fields need to be memo fields. Richtext only works in memo fields or unbound fields. I forgot that, but it makes sense. Also the controls on the form need to be set to richtext in design view.

tblWeekViewGrid
Day1
Day2
Day3
....
Day7

2) Use the existing qry_FillTextBoxes but add the supervisor ID field to the query.

SELECT tbl_YearCalendar.AbsenceDate, [EmpLName] & ", " & [EmpFName] AS EmployeeName, tbluAbsenceCodes.AbsenceCode, tbluEmployees.EmployeeID, tbluAbsenceCodes.AbsenceColorCode, tbluAbsenceCodes.AbsenceTextColorCode, tbluAbsenceCodes.AbsenceColorTag, tbl_YearCalendar.AbsenceTime, tbluEmployees.SupervisorID
FROM tbluEmployees INNER JOIN (tbluAbsenceCodes INNER JOIN tbl_YearCalendar ON tbluAbsenceCodes.AbsenceID = tbl_YearCalendar.AbsenceID) ON tbluEmployees.EmployeeID = tbl_YearCalendar.EmployeeID
ORDER BY tbluEmployees.SupervisorID, tbluEmployees.EmpLName, tbl_YearCalendar.AbsenceDate;

so that you can filter by the supervisor

3) Create a recordset for all absences in your range and the supervisor. You do this day by day and write to the correct column in the grid. Before you write it, wrap it in the correct tags.

4) notice that when you call the code LoadGrid you can supply a supervisor id. You have to write that code because it will depend on if this is used as a subform or stand alone form.


The code is short, but pretty compact. The only real new code is

Private Sub LoadGrid(SupervisorID As Long)
  Dim RsRead As DAO.Recordset
  Dim RSGrid As DAO.Recordset
  Dim StrSql As String
  Dim I As Integer
  Dim FieldDate As Date
  Dim InputText As String
  CurrentDb.Execute "Delete * from tblWeekViewGrid"
  Set RSGrid = CurrentDb.OpenRecordset("tblWeekViewGrid", dbOpenDynaset)
  For I = 0 To 6
    FieldDate = Me.FirstDayOfWeek
    FieldDate = FieldDate + I
    StrSql = "Select * from qry_FillTextBoxes where absenceDate = " & SQLDate(FieldDate) & " AND " & SQLDate(Me.FirstDayOfWeek + 6) & " AND SupervisorID = " & SupervisorID
    Set RsRead = CurrentDb.OpenRecordset(StrSql)
    If Not (RSGrid.EOF And RSGrid.BOF) Then RSGrid.MoveFirst
    Do While Not RsRead.EOF
      InputText = RsRead!AbsenceCode & ": " & RsRead!EmployeeName
      InputText = WrapText(InputText, RsRead!AbsenceColorTag)
      If RSGrid.EOF Then
        RSGrid.AddNew
          RSGrid.Fields("Day" & I + 1) = InputText
        RSGrid.Update
      Else
        RSGrid.Edit
          RSGrid.Fields("Day" & I + 1) = InputText
        RSGrid.Update
      End If
      If Not RSGrid.EOF Then RSGrid.MoveNext
      RsRead.MoveNext
    Loop
  Next I
  RSGrid.Close
End Sub

Public Function WrapText(Text As String, AbsenceColorTag As String) As String
   WrapText = AbsenceColorTag & Text & "</font>"
   WrapText = "<div>" & WrapText & "</div>"
End Function

 

[MajP]

http://www.4shared.com/file/eETH13E1ce/WeekView.html

 

[jguesman14]

MajP,
I have imported the new week view into my database....thank you for all the hard work! I am having a little trouble though. I am getting a Debug prompt in the color coded week view. This is what I am seeing when I click "Debug":

Private Sub LoadGrid(SupervisorID As Long)
Dim RsRead As DAO.Recordset
Dim RSGrid As DAO.Recordset
Dim strSql As String
Dim i As Integer
Dim FieldDate As Date
Dim InputText As String
CurrentDb.Execute "Delete * from tblWeekViewGrid"
Set RSGrid = CurrentDb.OpenRecordset("tblWeekViewGrid", dbOpenDynaset)
For i = 0 To 6
FieldDate = Me.FirstDayOfWeek
FieldDate = FieldDate + i
strSql = "Select * from qry_FillTextBoxes where absenceDate = " & SQLDate(FieldDate) & " AND " & SQLDate(Me.FirstDayOfWeek + 6) & " AND SupervisorID = " & SupervisorID
Set RsRead = CurrentDb.OpenRecordset(strSql) ******THIS IS THE LINE THAT IS HIGHLIGHTED************
If Not (RSGrid.EOF And RSGrid.BOF) Then RSGrid.MoveFirst
Do While Not RsRead.EOF
InputText = RsRead!AbsenceCode & ": " & RsRead!EmployeeName
InputText = WrapText(InputText, RsRead!AbsenceColorTag)
If RSGrid.EOF Then
RSGrid.AddNew
RSGrid.Fields("Day" & i + 1) = InputText
RSGrid.Update




any suggestions?

 

[MajP]

Not sure if that is a typo but that line cannot break or it will not compile

strSql = "Select * from qry_FillTextBoxes where absenceDate = " & SQLDate(FieldDate) & " AND " & SQLDate(Me.FirstDayOfWeek + 6) & " AND SupervisorID = " & SupervisorID

If it is compiling but erroring then

Always debug your sql strings. I always do something like

dim strSql as string
strSql = "......."
debug.print strSql
then use the string in code

so look at the sql string and see what it is.

 

[oxicottin]

Majp, awesome week grid once again thanks for putting the time in to help us, I made some modifications from some of your previous code to stretch the back colors... I had to remove the text box scroll bars in the detail section and add the below code or yours...

Public Function WrapText(strText As String, AbsenceColorTag As String, TotalLength As Integer) As String
    Dim textlength As Integer
    Dim spacesToPad As Integer
    Dim frontPad As Integer
    Dim backPad As Integer
    textlength = Len(strText)
    If textlength < TotalLength Then
        spacesToPad = TotalLength - textlength
        frontPad = spacesToPad \ 2
        backPad = spacesToPad - frontPad
        strText = Space(frontPad) & strText & Space(backPad)
    End If
    WrapText = AbsenceColorTag & strText & "</font>"
    WrapText = "<div>" & WrapText & "</div>"
End Function

I also had to add the number of text in the row to this line in LoadGrid:

InputText = WrapText(InputText, RsRead!AbsenceColorTag, 25) 'Num of spaces in text box

I removed all the querys and tables ect the other versions of week view used since this only uses a table..... I attached an example as well!


OneDrive Majp_WeekView_Example

Thanks,
SoggyCashew.....

 

[MajP]

1. Nice style. Your forms are much nicer than mine.
2. This hopefully has really demonstrated that well written code is flexible and reusable. I try to always write code that can be reused or easily modified.