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]

I did not notice the additional pages.

Change the recordsource of the main report to

"SELECT DISTINCT qry_YearView.EmployeeID, qry_YearView.EmployeeName, qry_YearView.Year FROM qry_YearView; "

To understand this. Imagine your report did not have any of the subforms added yet. Just the two fields EmployeeName and Year at the top of the report. You have a report in single record view. So if it is bound to a query with nine records you would get at page for every record.

In thruth you do not even have to bind the main form. All the subforms are populated in code. You could just make the Name and Year field a calculated field.
=[Forms].[frm_YearCalendar].cboYear
=[Forms].[frm_YearCalendar].cboEmployee.column(1)

 

[oxicottin]

Changing the record source worked, I see that now it just chooses the employee ID and year for the reports calendar instead of all the records and then it uses the reports code to populate the days/colors then the sub report uses the query. I appreciate all the time and effort you have put into helping me with getting this finished, I wouldn't have been able to figure out a lot.

Thanks,
SoggyCashew.....

 

[oxicottin]

Thank you Majp for all your help I finally finished the DB . I deleted all the above links and uploaded a final PUBLIC database to my 4Shared. I am uploading this final DB because there is hardly any information or samples on a employee yearly attendance calendar or any employee yearly calendar for that matter. This is a complete database minus a switchboard which I dont need. I added a button at the top of the footer to re-size that section if needed among a few other things just poke around....

Please give credits to Majap, myself (oxicottin) and a few members of the Utter Access and MS forums community's for a few query formulas also Dom DXecutioner of Access World Forums for the report example he posted that was incorporated.

FINISHED EMPLOYEE ATTENDANCE DB .accdb.zip

Thanks,
SoggyCashew.....

 

[oxicottin]

Majp, Ok im having an issue and I have tried everything and have enven posted the question .... The database works flawless in 2010 and 2013 but if ran it in 2007 which is what Im using at work it freezes when I enlarge the the footer section but If I use the code below to insted of whats in my final DB I can get the footer not to freeze the DB but I have to comment out the expanding of the subform or it freezes the DB. I have been pulling my hair out on this, I know its happening when im tring to resize the subform but have no clue why its freezing the DB and it only does it in 2007. I tried it at home in 2007 and it does it there as well so it isnt my work PC.... Ideas?

Private Sub cmdresize_Click()
  If Me.Section(acFooter).Height = 4188 Then
       Me.Section(acFooter).Height = 7700
       [COLOR=#4E9A06]'Me.frm_AbsenteeismPolicySummarySub.Height = 6000[/color]
     Else
        [COLOR=#4E9A06]'Me.frm_AbsenteeismPolicySummarySub.Height = 1000[/color]
        Me.Section(acFooter).Height = 4188
    End If
End Sub

Thanks,
SoggyCashew.....

 

[PHV]

What about this ?

Private Sub cmdresize_Click()
  DoCmd.Echo False
  If Me.Section(acFooter).Height = 4188 Then
    Me.Section(acFooter).Height = 7700
  Else
    Me.Section(acFooter).Height = 4188
  End If
  DoCmd.Echo True
End Sub

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

 

[oxicottin]

PHV, I dont have a problem expanding ect the footer now that im using the Me.Section(acFooter).Height, it the expanding of the subform its where the DB is freezing. I have tried the DoCmd.Echo False/True and commented out the previous code above and still the DB freezes. Thoughts? Has anyone tried the DB in Access 2007? If so did it do it freeze for you?

Thanks,
SoggyCashew.....

 

[oxicottin]

Ok, I tried something to see if it helped and it did. I deleted Oct, Nov and Dec and all its textboxs and labels then tried the DB again and it worked so I think possible I have to many controls on the form? But why would it work in 2010 and 2013?

Thanks,
SoggyCashew.....

 

[MajP]

my guess is that is a limit thing and the limits have been increased. You may try creating a new form and copying everything into it. See if that helps.

 

[oxicottin]

MajP, That was it.... I created a new form and moved all the controls to the new form ect compacted and now it works smooth as butter on 2007, 2010, 2013... Thanks Again!

Thanks,
SoggyCashew.....

 

[MajP]

But I have a much better Idea.

We can simplify this and make it much more stable. If I new your format would end up like this instead of linear calendars I would have suggested this in the first place. Make a single "Month" calendar. Copy one month and paste into a new form. Called "subFormMonth". Size that form to the size of the calendar. Change the name of the month label to "lblMonthName". In that month rename your labels and textboxes.

Change the naming convention
from txtJan1 to txt1,txt2...
and lblJan1 to lbl1, lbl2,lbl3

Now build a brand new form
Add the header and footer
Add a subform control with your new monthsubform
Play with the formatting (no scroll bars, navigation buttons, record selectors ...) and size until it looks good. Should look identical to what you have. Except instead of 12 calendars made of controls you will have 12 subforms

Copy and paste the subform control 11 times.

Name your subform controls
subFormJan, subFormFeb .....

Now in your fill labels module add a new procedure. Very little modifications, basically just take the loop out.

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 monthCounter As Integer
    Const ctlBackColor = 14211288    'Gray color thats used for Holiday shading/unshading
   
    FirstDayOfMonth = getFirstOfMonth(TheYear, monthCounter)
    DaysInMonth = getDaysInMonth(FirstDayOfMonth)   'Days in month.
    intOffSet = getOffset(TheYear, monthCounter, vbSaturday)    'Offset to first label for month.
        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
                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
                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

Now on your main form you will need code something more like this.

Public subfrmJan As Form_subFormMonth
Public subFrmFeb As Form_subFormMonth
... one for each


Private Sub Form_Load()
  Set subfrmJan = Me.subFormJan.Form
  Set subFrmFeb = Me.subFormFeb.Form
  ...
  UpdateAllCalendars
End Sub

Of course you will have to do similar modification to the fill textboxes.
FillSubFormTextBoxes(frm As Access.Form, empID As Long, TheYear As Integer, TheMonth as Integer)
...
strSQL = "Select * from qry_FillTextBoxes where EmployeeID = " & empID 'Query that finds the absence Year() by employeeID
strSQL = strSQL & " AND year(AbsenceDate) = " & TheYear & " AND Month(absenceDate) = " & TheMonth

In your Main Form you then would likely want methods like

Public Sub UpdateAllCalendars
  subfrmJan.LblMonth.Caption = "January"
  subFrmFeb.LblMonth.Caption = "February"
 ...
  mod_FillMonthLabels.FillSubFormMonthLabels subfrmJan, me.comboYear, 1
  mod_FillMonthLabels.FillSubFormMonthLabels subFrmFeb, me.comboYear, 2
...

Now you should be able to figure out how to update the textboxes for each or one subform calendar
You will go from 100s of controls to tens of controls (the subforms are 1 control). Also the ability to modify the form will greatly increase. I can barely edit it now.

 

[oxicottin]

Majp, I tried the DB at work and it crashes like B4 so I give up.... I started doing what you’re tiring to show me but I am quickly getting confused. I can follow directions but to adventure out and figure this out without step by step guidance it would be impossible for me. I think I need to start a new database and start fresh then follow your directions. This might take some time I'm still learning.

Thanks,
SoggyCashew.....

 

[MajP]

I modified yours and will send it back later.

 

[oxicottin]

Thank You MajP I will wait for the link...

Thanks,
SoggyCashew.....

 

[MajP]

http://www.4shared.com/file/LtpwFYaMba/Maj_P_attendance.html

You need to add some repetitive code in the FillAllHolidays functions on the Main form. You will see. You need to readd some code from your original for controlling the reports. I did not copy everything back in.

 

[oxicottin]

MaJp, this is awesome! I have been messing with it since you posted it.... Im trying to figure out a few thing so I can write notes in the VBA and to learn how it works. I have one question so far. How does it know what day/Label Or text/attendcode I selected in the sub calendar when I open the input box? I see each label and text box in the bub calendars tag has a something;number in it and then in the module the line:

intCol = CInt(Split(ctl.Tag, ";")(1))

but why are you splitting it? It seems it doesn't matter what goes in front of the semicolon.

Thanks,
SoggyCashew.....

 

[MajP]

There was no reason to split it anymore, but I was to lazy to go back and change all the tags. So if you want you can go back and change the tag in each control. Then you can simply put 1,2,3 ... in the tag, and then intcol = cint(ctl.tag). Or since each control is names txt1 or lbl1,2,3... you could determine the column by using some string functions on the name intCol = cint(right(ctl.name,1))

How does it know what day/Label Or text/attendcode I selected in the sub calendar when I open the input box?

When you click on a control it fires the common click event.
When this event fires it knows the active control
ctl = activecontrol
Then it can determine which form called it because the parent of that specific control is its specific form
sFrm = ctl.Parent
Remember that we gave each form a specific monthlabel caption, we could have gave them each a specific tag also. Unfortunately, in access each instance of the form has the same name so you cannot use the name property.
So since you know the ctl that was clicked, you know which subform was clicked using the parent property, and you know which month that represents by pulling it from that forms label.

So now you can update the textboxes in that month(form) since you pass a reference of that specific form to you update labels/textboxes procedures.

 

[oxicottin]

Ok, I got the database working correctly at home in v2013 and took to work which uses 2007 and now I get an error.

First, As soon as the database opens I get a

Run-time error '2164':
You can't disable a control while it has focus.

I select debug and it takes me to the yellow highlighted line of code in the mod_FillMonthLabels / Public Sub FillSubFormMonthLabels

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.

    Const ctlBackColor = 14211288    'Gray color thats used for Holiday shading/unshading

    FirstDayOfMonth = getFirstOfMonth(TheYear, TheMonth)
    DaysInMonth = getDaysInMonth(FirstDayOfMonth)   'Days in month.
    intOffSet = getOffset(TheYear, TheMonth, vbSaturday)    'Offset to first label for month.
    ' Debug.Print DaysInMonth
    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
            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
            [highlight #FCE94F]ctlt.Enabled = False[/highlight]   '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

Thanks,
SoggyCashew.....

 

[MajP]

That is true you cannot disable a control when it has the focus. So my guess when you import into 2007 the tab order is not maintained, and one of the textboxes has focus

Unfortunately labels cannot take focus. If there is another control on the subform you can set the focus there before the loop.
frm.Someothertextbox.setfocus
or you could work around it. Something like

frm.controls("txt37").setfocus
For i = 1 To 37
if i = 37 then frm.controls("txt1").setfocus

 

[trevil620]

If you have some other control on your main form, you have several choices; first I would try:
In your Form_Load, before calling these subs, add a Me.txtCtrl.SetFocus.

The speculative judgment of the quality of an answer is based directly on … what was the question again?

 

[MajP]

Also forget using txt37 as I suggested, because this would often already be disabled. I simply pick one in the middle that is always enabled, like txt15, or you can add a txtFocus and give it dimensions of 0 height and width. Then set it to text focus. Also make it the first in the tab order.
So the question is not why this does not work in A2007, but why did it work in 2010 plus. My guess it has to do with the tab order. The first control in the tab order gets the focus. My guess it was set to a control that did not get disabled.