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

 

[MajP]

Not sure what is happening with day 1. The other blocks look good.

 

[oxicottin]

Majp, Yes that is exactly what I was after but I'm having trouble on my end...I decpile and its giving me a Compile error: saying that PadString in not defined?

I also have another question, How exactly are you getting two entries in for the same date in your frm_CalendarInputBox? I have been just been adding dates to the table as of now to get by and follow what your showing me. I figured I would have had to have a subform on frm_CalendarInputBox that I could enter the absence and time taken but have the absence reason on the form so I would only have one memo field.

I appreciate all the help you have been giving me on this project, I'm seeing and learning stuff I would have never even touched or thought of EVER! This thread is going to be looked at a million times over, It has all kinds of cool information in it and working examples that I hope people will appreciate! Thanks!

Thanks,
SoggyCashew.....

 

[oxicottin]

Majp, Sorry I got the "Compile error: saying that PadString in not defined?" fixed, I missed a piece of your code.

Thanks,
SoggyCashew.....

 

[MajP]

I also have another question, How exactly are you getting two entries in for the same date in your frm_CalendarInputBox? I have been just been adding dates to the table as of now to get by and follow what your showing me. I figured I would have had to have a subform on frm_CalendarInputBox that I could enter the absence and time taken but have the absence reason on the form so I would only have one memo field.

The input form is up to you. You could build it several ways. But,I just hand jammed some values into the table for test. For your input form, I like the user interface format I posted, where you would have a subform. That way you could see all the absences for a single date. It also makes navigation and coding easy. If the absence reasons are relatively short (which I would assume, like "had doctors appointment") then I would just put that field as a column in the subform. My subform is in datasheet view, but you could also just go with continous form view for additional real estate. Another thing you could do, if they do have long absence reasons is to add a zoombox feature. I have plenty of code for that. But basically have a simple datasheet view subform, like I proposed. If they need to type in a long reason you could double click the reason textbox (or add a button) and a larger zoom box would open. Close the zoombox and whatever you typed would be in the subform.

I think the reason you did not get the carriage return was you had
...V<br></font><font...
should have been
..V</font><br><font...
after the closing font tag for the first string

 

[oxicottin]

Ok, I'll give it a go... What font did you use in your image posted above? For the life of me I cant get my text centered. From goofing with your new code it looks like the 9 is characters across in my textbox and if im right if my text is 4 characters long then it / 2 and moves that to the left and then - for the right space. Wouldnt this leave an uneven number and my text would be off center? Yours looks pretty dam good and mine is all boogered up... Lol

Thanks,
SoggyCashew.....

 

[MajP]

Couriter new is a fixed width.

So the code works like this.
Pass in the total length.
Lets say 9.
minus the length of the code . example "PD" which is 2
9-2 = 7. So yes you need to add 7 pads but like you said that means you cannot do it evenly.
So 7\2 (integer divsion) = 3
So put three on the front.
7-front padding = 7-3
so put 4 on the back. Still should look pretty good. So a two letter code and a three letter code would be off center slightly.
Some stuff I have not figured out. Look at the 21st. Those should be lined up.

Did you make sure to add this code. Since you are padding need to then left allign.
If rsDay.RecordCount > 1 Then
ctl.TextAlign = 1
Else
ctl.TextAlign = 2
End If

 

[oxicottin]

Ok, after messing with it over the weekend and finally getting the multi absent boxes centered on my end Im woundering if there is an easy way to enter the data for a single date without having to change relationships and everything else. What I was going to do was remove the two textboxes from frm_CalendarInputBox and create a subform with the AbsenceID and AbsenceTime that I removed and its record source tbl_YearCalender. The problem is how do I get all this linked together for the date I want? Also, I only wanted one AbsenceReason because most of the time is a long reason of what happened or whatever went on so I couldnt have it as a small text box in the subform. So what would be the way to approch this?

Thanks!

Thanks,
SoggyCashew.....

 

[MajP]

easy way to enter the data for a single date without having to change relationships and everything else

Should be no reason to change the relationships. You have developed a proper datbase application, so the data is the data. Changing an input form should have absolutely no change to your data relationship.

So (I am doing this off my head, correct me if wrong), you currently open the input form using code like this

DoCmd.OpenForm "frm_CalendarInputBox", , , , , acDialog, Format(selectedDate, "mm/dd/yyyy") & ";" & empID

That worked when you only had a single record for each date. So now change the recordsource of the input form. Something like

Select distinct absencedate, empid from tblYearCalendar
and some join to bring in the employee name. So the main form needs to show the employee name, and the absence date.

The subform which has the codes (pull down), hours, and absencereason. Would be linked to the main form by something like
link master fields: empid; absenceDate
link child Fields: empId; absenceDate

The display and entry of the absence reason is a user interface preference. I personally would put it in my subform as the 3rd column. You would not be able to see much of the text. But this form is more for entry than display. I would think for displaying this the user is going to a report. So I would add zoom box feature. If you click on that field it would pop open in a large zoom allowing you to read it. Same feature to allow you to easily add it. This feature is similar to the Access expression builder. You could even fire this on the on enter event. So when you enter that field, it would immediately open into zoom box. In my image the subform was a datasheet with an absence code combobox and a textbox to add the hours. Instead it could be a tabular form and you could make the textbox taller than the other controls. I think the more datasheet looking view is cleaner. (In truth you will use a tabular continuous subform so that you could have a combobox for the codes, a textbox for the hours, and a textbox for the reason)

You could also break the absence field into a seperate box below the subform, but that gets confusing to me. You would have a main form, a continous subform, and then another box which would be related to the record selected in the subform.

You could do this completely different and not use a subform and have a single form view. In that design your form would remain exactly the same as it is currently, but you would add a button to add a new record. This would move to the new record and you would set the defaults of that record to the empid, and selected date. Adding, editing, and deleting in that design would be easy. However, it will be hard to show the user how many absences there are.

 

[oxicottin]

MajP, I followed your advise and its working perfect. Here is what I did and some changes that I had to make.

First, the multi color/absence in calender, for some reason after trying over and over font after font and re-sizing my subFormMonth text boxes I think I found out why it wouldn't center my text. It was calculating everything as you planed with the Function PadString it was just breaking in the wrong place so it appeared to show my text was miss aligned.It had to do with that break <br> so I changed to the code below and it now centers. I also changed the font to Courier New sz7 and then changed the (TotalLength)from 9 to 6 in the Public Sub FillSubFormTextBoxes and resized th subFormMonth text boxes to a width of 0.3326".

[b]Changed From:[/b] 
AbsenceCode = AbsenceColorTag & "<br>" & AbsenceCode & "</font>"

[b]Changed To:[/b]
AbsenceCode = "<br>" & AbsenceColorTag & AbsenceCode & "</font>"

Second, I wanted to be able to show the hours in the dates/absences that had only one entry so I added these lines throughout the Sub FillSubFormTextBoxes.

Dim AbsenceTime As String  'Field in tbl_YearCalendar - Added to show hours missed on single absences
AbsenceTime = rsDay!AbsenceTime
ctl.Value = strCodes & vbCrLf & Format(AbsenceTime, "0.00")   'Displays the text or absencecode in the textbox to whats indicated in tbluAbsenceCodes and Time taken from tbl_YearCalendar

RESULT:

Next, I followed your advice on the frm_CalendarInputBox. I created a continuous subform subFormCalendarInputBox with a record source tbl_YearCalendar and the needed controls. then added this subform to the frm_CalendarInputBox and linked its master and child fields together like you said (Neat by the way)

Link Master Fields:    txtEmployeeID;txtAbsenceDate
Link Child Fields:     EmployeeID;AbsenceDate

This worked perfect but I only wanted to allow two records so I did some goggling and found my answer. I added this code to my subFormCalendarInputBox module and its On Current Event I added =LimitRecords([Form],2)

Public Function LimitRecords(frm As Access.Form, Optional RecLimit As Integer = 1)
   '[URL unfurl="true"]http://www.datagnostics.com/dtips/limitentries.html[/URL]
    ' Limit the number of records in the form passed as
    ' to no more than the number specified by .
    With frm.RecordsetClone
        If .RecordCount <> 0 Then .MoveLast
        frm.AllowAdditions = (.RecordCount < RecLimit)
    End With

End Function

RESULT:

Thanks,
SoggyCashew.....

 

[MajP]

Looks good. What is next?

 

[oxicottin]

lol, I'm going to have to keep entering absences ect to test the frm_CalendarInputBox because once in a while I will open it and it creates a new record or 3rd record and puts it first if I already have two but other than that well the report rpt_YearView needs to show the multi entries as well as the single entries as before.... As of right now the mod_rptYearView module is:

Option Compare Database
Option Explicit

'// AUTHOR: Dom DXecutioner (Dominick G. Hernandez)

Private m_strCTLLabel As String
Private m_strCTLLabelHeader As String    'mon,Tues,
Private colCalendarDates As Collection

Function getCalendarData() As Boolean
    Dim rs As DAO.Recordset
    Dim strDate As String
    Dim strCode As String
    Dim strSql As String
    Dim qdf As DAO.QueryDef

    Dim i As Integer

    Set qdf = CurrentDb.QueryDefs("qry_rpt_YearView")
    qdf("Forms!frm_YearCalendar!cboEmployee") = Forms!frm_YearCalendar!cboEmployee
    qdf("Forms!frm_YearCalendar!cboYear") = Forms!frm_YearCalendar!cboYear
    Set rs = qdf.OpenRecordset()

    Set colCalendarDates = New Collection
    With rs
        If (Not .BOF) Or (Not .EOF) Then
            .MoveLast
            .MoveFirst
        End If

        If .RecordCount > 0 Then
            For i = 1 To .RecordCount
                strDate = .Fields("AbsenceDate")
                strCode = .Fields("AbsenceCode")

                colCalendarDates.Add strCode, strDate
                .MoveNext
            Next i
        End If
        .Close
    End With
    '// return date collection
    Set rs = Nothing
End Function

Public Sub loadReportYearCalendar(theReport As Report)
    Dim i As Integer
    Dim datStart As Date
    Dim rptControl As Report
    m_strCTLLabel = "labelCELL"
    m_strCTLLabelHeader = "labelDAY"

    '// load dates into our collection
    Call getCalendarData

    With theReport

        '// Gets the first month of the year selected on frm_YearCalendar
        datStart = "1/1/" & Forms!frm_YearCalendar!cboYear

        For i = 1 To 12
            '// set pointer to subreport control hosting the mini-calendar
            Set rptControl = .Controls("childCalendarMonth" & i).Report
            '// run procedure to populate control with it's respective year
            Call loadReportCalendar(rptControl, datStart)
            '// reset and obtain first day of the following month
            datStart = DateAdd("m", 1, datStart)
        Next i
    End With
    '// clean up
    Set colCalendarDates = Nothing
    Set rptControl = Nothing
End Sub


Public Sub loadReportCalendar(theReport As Report, Optional StartDate As Date, Optional theHeaderColor As Variant)
    Dim i As Integer
    Dim intCalDay As Integer
    Dim datStartDate As Date
    Dim intWeekDay As Integer

    datStartDate = StartDate
    intWeekDay = WeekDay(datStartDate)

    With theReport

        .Controls("labelMONTH").Caption = Format(StartDate, "mmmm")

        '// change the day label's backcolor if necessary
        If Not (IsMissing(theHeaderColor)) Then
            For i = 1 To 7
                .Controls("labelDayHeader" & i).BackColor = theHeaderColor
            Next
        End If

        For i = 1 To 42
            With .Controls(m_strCTLLabel & i)
                If (i >= intWeekDay) And (Month(StartDate) = Month(datStartDate)) Then
                    If (datStartDate = Date) Then
                        .BackColor = vbWhite    '//Changed from vbYellow so you couldnt see todays date
                    End If

                    '// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
                    '// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

                    '// nevermind on how this dirty code has been setup; then objective is getting
                    '// the values ;) being elegant is your design and how you'd like to approach it

                    On Error Resume Next

                    Dim strCaption As String
                    Dim strKey As String

                    strKey = datStartDate
                    strCaption = ""
                    strCaption = colCalendarDates.Item(strKey)
                    colCalendarDates.Remove strKey

                    If strCaption = vbNullString Then
                        .Caption = Day(datStartDate)
                        .Bold = False
                    Else
                        .Caption = strCaption
                        .Bold = True


                        '// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
                        '// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
                        '// use these to generate permanent background color on the calendar report

                        .BackColor = GetBackColorCode(strCaption)
                        .ForeColor = GetTextColorCode(strCaption)
                    End If

                    '// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
                    '// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

                    datStartDate = DateAdd("d", 1, datStartDate)
                Else
                    .Caption = ""
                End If
            End With
        Next i
    End With
End Sub

Public Function GetBackColorCode(strCode As String) As Long
    GetBackColorCode = DLookup("AbsenceColorCode", "tbluAbsenceCodes", "AbsenceCode = '" & strCode & "'")
End Function

Public Function GetTextColorCode(strCode As String) As Long
    GetTextColorCode = Nz(DLookup("AbsenceTextColorCode", "tbluAbsenceCodes", "AbsenceCode = '" & strCode & "'"), 0)

End Function

Thanks,
SoggyCashew.....

 

[MajP]

This is how I would do it. Greatly reduces the amount of code and objects that you need to maintain.
I would make a new subreport month view by copying all the controls from the subform month view and pasting into a new report. Now you have a subform and a subreport that are identical
I would get rid of all the existing report code. Use this new subreport (identical to your subform) as the subreports for your year report

Now I would reuse all the existing fill month labels and fill textbox code, because you could now maintain the code in one place only. The code is very well written and provides this flexibility.

The only thing that has to be changed is that most of the code has a signature like this
Public Sub FillSubFormMonthLabels(frm As Access.Form, TheYear As Integer, TheMonth As Integer)
...

Where you pass in a reference to the specific subform. Now you want this code to also allow you to pass in a reference to a report. So you would have to modify the code to be something like
Public Sub FillSubFormMonthLabels(frmOrRpt As object, TheYear As Integer, TheMonth As Integer)
then in the code everywhere you have "frm" you would replace it with form or report
So for example
Set ctl = frm.Controls("lbl" & i)
wouuld be
Set ctl = frmOrRpt.Controls("lbl" & i)

Now one set of code will work for your form year calendar and your report year calendar

 

[oxicottin]

Majp, I’m a bit confused.... Ok first you want me to create a new report by copying all the textboxes ect that’s in the Form (subFormMonth) and pasting it in this new blank report. Then delete the old report calendar and name this new one the same as the old one (rpt_YearViewCal). Now my subForm and subReport Calendars are identical.

Next you wrote:

I would get rid of all the existing report code.

Im not sure what you mean by this? Are you meaning I no longer need the module (mod_rptYearView) so go ahead and delete it?

Im not also understaning what is ment by the second half.

Use this new subreport (identical to your subform) as the subreports for your year report

Next, your said the only thing is left to do is to change (frmOrRpt As object), would that be just in the modules (mod_FillMonthLabels) and (mod_FillTextBoxes)?

I am also confused on what code do I put in my report to get the correct data? do I enter the same VBA as my (frm_YearCalendar)is?



Thanks,
SoggyCashew.....

 

[MajP]

If you like the way your subforms look, and want to use them in the report then the answer is yes to the above. If you want to post your most recent version, I can do the modifications in about 10 mins. Shorter than explaining it.
The bottom line is that your subform works the way you want. All the code to make it work (filling labels, and textboxes) could also work for a subreport with controls that are named in the same manner. So you can call the existing code to control your subreport. The only problem was that code expects to be passed a form. The code needs to be modified to accept either a form or report reference.

 

[kjv1611]

Just read SkipVought's first post, and HAD to put a star on it. If for no other reason, this one phrase brightened my day:

I'd just say in passing that no one is going to have in their hip pocket an application that suites your needs as you envision it.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[MajP]

BTW I would recommend splitting the application. But all the tables into a seperate database. Leave the queries, code, forms, reports in another. Call the table database something like. Attendance_BackEnd. Now link to the backendtables. This way you can have a set of test tables without real names, especially if you want to post this. This also allows you to continuously update the development front end without messing with the database that holds your real data. Once you have an update you can then link that front end to the source tables. If you are going to do a multiuser then each user get a copy of the front end, and all users link to the back end data on a network. When you have an update you just give everyone a new front end.

 

[oxicottin]

Majp, below is my latest version.

Attendance_v4xx_ReportFix

Thanks,
SoggyCashew.....

 

[MajP]

http://www.4shared.com/file/E6I7dtJRce/Attendance_v5xx_ReportFix.html

This was easier than I thought. A report can use forms as the sourceobjects for the subreports. So I just used the new month calendar form. No need to copy and paste anything. Then I just copied the code from the the year calendar form into the report. The only thing I had to change was in setting the month labels there is code to enable and disable certain controls. You cannot do this if the form is within a report. So I had to check if the subform was inside the year form or inside the year report. So you will now see a check to determine if the parent is a form. Again this is where well structured code makes things very easy.

Whats next?

 

[MajP]

on that note, I use to like using 4Shared. Now it is nothing by a way to load adware and spyware. You cannot figure out what button to hit. I had to remove three programs.

 

[oxicottin]

MajP, the only reason I used 4Shared is because I thought that's what you preferred,I hate it!!!! I prefer using OneDrive,DropBox or MediaFire. I will check this beast out tonight and try see how you did it... Thanks!

Thanks,
SoggyCashew.....