INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Yearly calendar for attendance

Yearly calendar for attendance

(OP)
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.....

RE: Yearly calendar for attendance

hi,

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.

Furthermore, when you say, "im sick of tabs ect." that leads me to think that your workbook design is much less than optimum, as I would expect that "sick of tabs" means that you may have tabs for each employee and maybe tabs for each week which means GOBS and GOBS of tabs.

I might envision a workbook with 2 or 3 tabs for a timesheet application. Tabs equate to tables,and tab/table structure is de-normalized, which is what I suspect. So translating Excel to Access would probably cause you a sickness of a different sort, without a significant redesign of your structure & process.

That's my impression from what you have stated and from what I know.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Yearly calendar for attendance

You will have to describe this in detail of what you want it to look like and what features it has. I have built a lot of calendar/booking applications. But like Skip said, they are all different. Real estate becomes the big issue. If you display a year on a form then you can only display very basic information per day. So in this one you have a year calendar and only able to display when an order goes out to the shipper in the combo.



You can click on a day and add information for that day. If you wanted more information you could put each month on a tab. The trick for all of these is to enter information into a normalized format and display in a non normal format.

RE: Yearly calendar for attendance

(OP)
Majp, my excel workbook almost looks exactly like your Access database can you upload an example of that database and I can work off of that. basicly I need a database that shows the year and each day or square I would be able to click in and add an attendance code like vacation and some hours taken and like in your example the box would turn green showing it is a vacation etc.thanks!

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

Take a look at thread 702-1705763. Bottom line there are a few ways demonstrated to do this.
1) Build a table of dates and use a crosstab query
2) Build an unbound form
3) Build a bound form and a "grid" table

There are pros and cons for all. I went with an unbound form, but this can be extremely hard to build by hand. That is a whole lot of textboxes and labels. So i wrote some code to add, move, format, and name the controls. Once built, this is the solution that provides the most flexibility.

RE: Yearly calendar for attendance

(OP)
Thank you for pointing me in the right direction much appreciated! This is a great starting point, I googled and googled and couldn't find anything. Thanks again!

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

In that thread is a link to my solution.

RE: Yearly calendar for attendance

(OP)
Majp, I found your upload in the thread but the file on 4Shared isn't there...... Any luck of getting a new link?

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

(OP)
Majp, I followed your instructions and got an example working to a point. The problems im haveing is in my attached example if I click on a date the year always says 1905. The second problem im haveing is the FillTextBoxes Sub SQL. Simply put I cant get it to work.... Third and last is I want to have an input popup for the text boxes so how would I get the info from the popup to the table? Thanks!

SKYDRIVE ATTACHMANT LINK

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

For the 1905 issue. I was using a date time picker which returns a date, then by taking Year() that would return an int. You are using a combobox which returns a string. If you use the function Year(somestring) you will return 1905. In your case you probably nead Cint(cboYear.value)

RE: Yearly calendar for attendance

add the employeeID to your query and then use that. your combo is already bound to it.

CODE

Public Sub FillTextBoxes(frm As Access.Form, EmpID As Long, theYear As Integer)
  Dim ctl As Access.TextBox
  Dim rs As DAO.Recordset
  Dim strSql As String
  Dim strMonth As String
  Dim intMonth As Integer
  Dim intDay As Integer
  Dim AbsenceDate As Date
  Dim FirstDayOfMonth As Date
  Dim intOffSet As Integer
  Dim AbsenceCode As String
  

  strSql = "Select * from qry_YearCalendar where EmployeeID = " & EmpID
  strSql = strSql & " AND year(AbsenceDate) = " & theYear
 'Debug.Print strSql 

RE: Yearly calendar for attendance

The click event is already there. The code show how to get the date, so all you need is the empID. I pop up a messagebox, but you have to build the form.

So pop up a form for that person on that date. Probably need to pass the date in openargs so you can set the date for new records. The easiest solution is when the popup closes you clear and repaint the entire form, instead of trying to just update the selected date.

CODE

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
  
  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
End Function 

RE: Yearly calendar for attendance

(OP)
Sorry MajP, Im learning so im getting stuck alot. I created a popup data entry form (frm_CalendarInputBox) and fixed the sugested changes you replyed but am still having trouble.

First, I notice the days are incorrect for the year AND when I change the year it stays the same.

Second, I am getting an 3061 Too few parameters. Expected 1. when I select an employee and I made the changes to the (Public Sub FillTextBoxes) SQL as instructed.

Third, I added some VBA to (Public Function gridClick) to open the input popup and show the date and employees name but how to I add the entered data to the correct date and empID and refresh calendar and show absenceCodein calendar.

Thanks!

SKYDRIVE ATTACHMANT LINK 2

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

http://www.4shared.com/office/Fdq2v5xw/MajP_YearCa...

Here is a working demo. There is probably a lot of ways to pop the form open. I used openargs and passed the date and employeeid. If the record exists then move to that record, if not set the default values.

RE: Yearly calendar for attendance

(OP)
Majp, this is exactly what I was looking to do thanks a million. I don't understand the code below could you explain or link me to what exactly it does? Thanks!

CODE --> MajP

If Me.OpenArgs & "" <> "" Then
    selectedDate = Split(Me.OpenArgs, ";")(0)
    empID = Split(Me.OpenArgs, ";")(1) 

Thanks!

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

That is a lot of shorthand. I chained together a few functions.

CODE

If Me.OpenArgs & "" <> "" Then
    selectedDate = Split(Me.OpenArgs, ";")(0)
    empID = Split(Me.OpenArgs, ";")(1) 

you could spell it out a little more using some variables.

CODE

'Through OpenArgs I am passing in a string of the form "12/05/2014;16"
  dim datePassed as string
  dim empID as string
  dim aArguments() as string
  'check to make sure something got passed in by openargs in the form date;employeeID
  If Me.OpenArgs & "" <> "" Then
    'Split takes a string and creates an array based on the delimeter
    aArguments = split(Me.openArgs,";")
    'so aAruments(0) is the first part of the split, the date
    'aArguments(1) is the second part
     selectedDate = aArguments(0)
     empID = aArguments(1) 

RE: Yearly calendar for attendance

(OP)
Majp, I have been messing with your example and Im trying to find a way to set the Date label (FillMonthLabels) to visible = false when there isnt a date. I tried the code below:

CODE

If intDay < 0 And intDay <= DaysInMonth Then 'added
         ctl.Visible = false
         End If 'added 

But it did nothing.... Im I looking in the correct place? Thanks!

CODE

Public Sub FillMonthLabels(frm As Access.Form, theYear As Integer)
  Dim ctl As Access.Label
  Dim i As Integer
  Dim amonths() As Variant
  Dim theMonth As Variant
  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 = -2147483616 'Used for Holiday shading/Unshading

  amonths = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
  For monthCounter = 1 To 12
    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" & amonths(monthCounter - 1) & i)
       ctl.Caption = ""
       ctl.BackColor = ctlBackColor  'reset the backcolor
       intDay = i - intOffSet        'Transforms label number to day in month
       If intDay > 0 And intDay <= DaysInMonth Then
         ctl.Caption = intDay
         
         If intDay < 0 And intDay <= DaysInMonth Then 'added
         ctl.Visible = false
         End If 'added
         
         If isHoliday(FirstDayOfMonth + (intDay - 1)) Then ctl.BackColor = 16776960 'Color holiday backcolor Blue
       End If
     Next i
  Next monthCounter
End Sub 

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

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

CODE --> VBA

...
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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Yearly calendar for attendance

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.

CODE


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 
 
You nested a converse condition like this

CODE

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.

RE: Yearly calendar for attendance



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.

RE: Yearly calendar for attendance

(OP)
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.....

RE: Yearly calendar for attendance

(OP)
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.....

RE: Yearly calendar for attendance

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

RE: Yearly calendar for attendance

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?

RE: Yearly calendar for attendance

(OP)
MajP, I did them by hand tongue. 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.....

RE: Yearly calendar for attendance

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.

RE: Yearly calendar for attendance

(OP)
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

CODE

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

RE: Yearly calendar for attendance

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.

RE: Yearly calendar for attendance

(OP)

Quote:

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

RE: Yearly calendar for attendance

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

RE: Yearly calendar for attendance

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

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

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



Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

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.

RE: Yearly calendar for attendance

(OP)
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.....

RE: Yearly calendar for attendance

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.

RE: Yearly calendar for attendance

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.

RE: Yearly calendar for attendance

(OP)
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.....

RE: Yearly calendar for attendance

Quote:

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

So the advanatage of doing it on the fly is that you do not have to populate every holiday for every year. The disadvantage is that you can not add special cases or work specific rules.
So unfortunately you have to enter every holiday for every year. However you could pre load it using basically a modification of the function.

so if you want all the holidays from 1980 to 2050 run the following. You will need to create the table first. This will save a lot of work. If you have other specic cases then you could add them here (ex. you shut down every Mar 1 for inventory)

FillHolidays 1980, 2050

CODE

Public Sub FillHolidays(StartYear As Integer, EndYear As Integer)

  Dim HolidayDate As Date
  Dim CurrentYear As Integer

 
  For CurrentYear = StartYear To EndYear
      'New Years
      HolidayDate = CDate("01/01/" & CurrentYear)
      InsertHoliday HolidayDate, "New Years"
       'ML King 3rd Monday of Jan
       HolidayDate = DayOfNthWeek(CurrentYear, 1, 3, vbMonday)
       InsertHoliday HolidayDate, "Martin Luther King Day"
      'Presidents Day  3rd Monday of Feb
      HolidayDate = DayOfNthWeek(CurrentYear, 2, 3, vbMonday)
      InsertHoliday HolidayDate, "Presidents Day"
     'Memorial Day    Last Monday of May
      HolidayDate = LastMondayInMonth(CurrentYear, 5)
      InsertHoliday HolidayDate, "Memorial Day"
     'Independance Day
      HolidayDate = CDate("07/04/" & CurrentYear)
      InsertHoliday HolidayDate, "Independence Day"
     'Labor Day   1st Monday of Sep
      HolidayDate = DayOfNthWeek(CurrentYear, 9, 1, vbMonday)
      InsertHoliday HolidayDate, "Labor Day"
     'Columbus Day    2nd Monday of Oct
      HolidayDate = DayOfNthWeek(CurrentYear, 10, 2, vbMonday)
      InsertHoliday HolidayDate, "Columbus Day"
    ' Veteranss Day
    ' Although originally scheduled for celebration on November 11,
    ' starting in 1971 Veterans Day was moved to the fourth Monday of October.
    ' In 1978 it was moved back to its original celebration on November 11.
      HolidayDate = CDate("11/11/" & CurrentYear)
      InsertHoliday HolidayDate, "Verterans Day"
    'Thanksgiving Day  4th Thursday of Nov
      HolidayDate = DayOfNthWeek(CurrentYear, 11, 4, vbThursday)
      InsertHoliday HolidayDate, "Thanksgiving"
    'CHRISTMAS
      HolidayDate = CDate("12/25/" & CurrentYear)
      InsertHoliday HolidayDate, "Christmas"
   Next CurrentYear
End Sub
Public Sub InsertHoliday(HolidayDate As Date, HolidayName As String)
     Dim strSql As String
     strSql = "Insert into tblHolidays (HolidayDate, HolidayName) values (#" & Format(HolidayDate, "mm/dd/yyyy") & "# , '" & HolidayName & "')"
     Debug.Print strSql
     CurrentDb.Execute strSql
End Sub 

RE: Yearly calendar for attendance

(OP)
MajP,our holdiays are:

New Years Day
Memorial day
Independance Day
Labor Day
Day Before Thanksgiving
Thanksgiving
Christmas Eve
Christmas

I would have to add a "Day Before Thanksgiving" and "Christmas Eve"

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

'Christmas Eve
HolidayDate = CDate("12/24/" & CurrentYear)
InsertHoliday HolidayDate, "Christmas Eve"
'Day Before Thanksgiving
HolidayDate = DayOfNthWeek(CurrentYear, 11, 4, vbThursday)
HolidayDate = HolidayDate - 1
InsertHoliday HolidayDate, "Day Before Thanksgiving"

RE: Yearly calendar for attendance

(OP)
Ok, im having trouble getting this to work. I created the table tbl_Holidays and ran your code to populate the dates and names which worked great. Then I created a module called mod_FillHolidays with the semi copied Sub from mod_FillTextBoxes as instructed. I then created the query qry_FillHolidays as well.

The mod_FillHolidays Public Sub:

CODE

Public Sub FillHolidays(frm As Access.Form, theYear As Integer)

    Dim ctl As Access.Label
    Dim rs As DAO.Recordset
    Dim strSql As String
    Dim strMonth As String
    Dim intMonth As Integer
    Dim intDay As Integer
    Dim HolidayDate As Date
    Dim FirstDayOfMonth As Date
    Dim intOffSet As Integer

    strSql = "Select * from qry_FillHolidays where year(HolidayDate ) = " & theYear
    'Debug.Print strSql
    Set rs = CurrentDb.OpenRecordset(strSql)
    clearHolidays frm

    Do While Not rs.EOF
        HolidayDate = rs!HolidayDate
        strMonth = Format(HolidayDate, "mmm")
        intDay = Day(HolidayDate)
        intMonth = Month(HolidayDate)
        FirstDayOfMonth = getFirstOfMonth(theYear, intMonth)    'First of month
        intOffSet = getOffset(theYear, intMonth, vbSaturday)    'Offset to first label for month.
        Set ctl = frm.Controls("lbl" & strMonth & intDay + intOffSet)
        ctl.BackColor = HolidayDate
        rs.MoveNext
    Loop

End Sub

Public Sub clearHolidays(frm As Access.Form)
    Dim ctl As Access.TextBox
    Dim i As Integer
    Dim amonths() As Variant
    Dim theMonth As Variant
    Dim monthCounter As Integer
    Const ctlBackColor = 14211288    '-2147483616    'Used for Holiday shading/Unshading
    amonths = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
    For monthCounter = 1 To 12
        For i = 1 To 37
            Set ctl = frm.Controls("lbl" & amonths(monthCounter - 1) & i)
            ctl.BackColor = ctlBackColor    '**********Added to reset the textbox backcolor
        Next i
    Next monthCounter
End Sub 

Then in the FillMonthLabels module/Sub I changed the word IsHoliday to my new Sub name FillHolidays:

CODE

If FillHolidays(FirstDayOfMonth + (intDay - 1)) Then ctl.BackColor = 16760576    'Color holiday backcolor bright Blue 

Now it not only dont show the holidays in the calendar it gives a error (Type Mismatch) on the + in the above code. Ideas?


SKYDRIVE TEST HOLIDAY DATABASE

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

In the old routine you checked every date to see if it was a holiday. Now you do it completely different. You fill the labels, and get rid of any code that deals with holidays. Then you call the fillholidays after FillMonthLabels completes.

So your code would look more like this.

fillMonthLabels(Me,cmboYear)
FillHolidays(Me,cmboYear)

Any event that use to call just FillMonthLabels would also call FillHolidays.

RE: Yearly calendar for attendance

(OP)
Majp, Ok that worked....

I used FillHolidays Me, CInt(cboYear.Value) under the FillMonthLabels everywhere needed in the calendar form and shortened the (Code1) FillHoliday module and then removed the (Code2)line and it works perfectly


CODE1

CODE

Public Sub FillHolidays(frm As Access.Form, theYear As Integer)

    Dim ctl As Access.Label
    Dim rs As DAO.Recordset
    Dim strSql As String
    Dim strMonth As String
    Dim intMonth As Integer
    Dim intDay As Integer
    Dim HolidayDate As Date
    Dim FirstDayOfMonth As Date
    Dim intOffSet As Integer

    strSql = "Select * from qry_FillHolidays where year(HolidayDate ) = " & theYear
    Set rs = CurrentDb.OpenRecordset(strSql)

    Do While Not rs.EOF
        HolidayDate = rs!HolidayDate
        strMonth = Format(HolidayDate, "mmm")
        intDay = Day(HolidayDate)
        intMonth = Month(HolidayDate)
        FirstDayOfMonth = getFirstOfMonth(theYear, intMonth)    'First of month
        intOffSet = getOffset(theYear, intMonth, vbSaturday)    'Offset to first label for month.
        Set ctl = frm.Controls("lbl" & strMonth & intDay + intOffSet)
        ctl.BackColor = 16760576    'Color holiday backcolor bright Blue
        rs.MoveNext
    Loop

End Sub 

CODE2

CODE

If FillHolidays(FirstDayOfMonth + (intDay - 1)) Then ctl.BackColor = 16760576    'Color holiday backcolor bright Blue 

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

(OP)
Majp, I have been trying to display the number of vacation days each employee gets per years of service and if they bought a vacation it ads to the number of weeks. I also wanted to display hours used and hours left of vacation on the form. I am able to do this BUT I cant figure out how to get the query (qry_YearCalendarSubRight) to display totals all the time, it only starts to display anything if I enter a absence for an employee then it will display.

SKYDRIVE DATABASE 9





Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

How is the sub form query set up? Is the subform linked to the combo? You can do that
Link master fields: [yourEmployeeCombo]
Link child fields: [employeeid]

If instead the subform is not linked but references a control, then you will have to requeries it often. Remember subforms load before the main form. So you might need something like
Me.yoursubforname.form.requery

RE: Yearly calendar for attendance

(OP)
Yes they are linked but that isnt the problem its the query. The query only brings up records that have to do with employees only if there is an entry for the year for them. I want it brought up for each employee even if no entry is entered.

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

You probably need a left join in your query, to include each employeeid

RE: Yearly calendar for attendance

(OP)
Yes left join worked I never did a left join so I had to play around with it but got it to work.

Majp, How would I go about getting the current days "Text Box" to have a yellow border? I messed around with the modules and cant figure out how to. Also, I ran a function that tells me how many controls I have on my form and there are 947 and I havent added much from the origional calendar.

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

Interesting, the 754 limit is well exceeded and according to MS that is still the limit. My guess is it went up some time back and never updated the help file..
http://office.microsoft.com/en-us/access-help/acce...

Quote (MS)

Number of controls and sections you can add over the lifetime of the form or report: 754

So to determine the txtbox or label that is today, the naming conventions are
txtJan1 .... txtJan37 where 1 - 37 are the columns.
So to find the month is easy. To find the column it is the same as loading the grid. Figure the day and figure the offset. If the first day falls in the fifth column then the offset is 5.
Once you have the month and column just concatenat
txt & Dec & 32 = txtDec32

CODE -->

Public Sub TestHilite()
  GetTodaysTextBox.BorderColor = vbYellow
End Sub
Public Function GetTodaysTextBox() As Access.TextBox
  Dim CurrentDay As Date
  Dim strMonth As String
  Dim offest As Integer
  Dim txtBxName As String
  CurrentDay = Date
  strMonth = Format(CurrentDay, "mmm")
  'Debug.Print strMonth
  Offset = getOffset(Year(CurrentDay), Month(CurrentDay), vbSaturday)
  'Debug.Print Offset
  txtBxName = "txt" & strMonth & Offset + Day(CurrentDay)
  'Debug.print TxtBxName
  Set GetTodaysTextBox = Forms("Frm_YearCalendar").Controls(txtBxName)
End Function 

RE: Yearly calendar for attendance

(OP)
lol thanks Majp, I would have never got that.... Worked great!

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

Is there a question there? Did you make each month calendar a subform or did you actually move around all the existing controls? The subform could have saved a lot of time if you were doing it from scratch. Since you already had the controls, would not have been as big of a deal.

RE: Yearly calendar for attendance

(OP)
Ok, I attached a sample DB with what I want to do with the report. There is a report button on the frm_YearCalendar
and it will open a report rpt_YearView. Im having two troubles,

1st, Lets say you selected Chad Z as supervisor and Z, Chad as employee and hit the "REPORT" button. it would bring
up a year calendar and page two shoud be all the absences but thats semi not the case. It does bring up the report
BUT it shows the record over and over 18 times BUT lets say I selected Chad Z as supervisor and C, Bob as employee it showes
2 pages. Why?

2nd, I am unable to figure out how to get the module mod_rptYearView to use the table tbluAbsenceCodes like you showed me for
the mod_FillTextBoxes module. I know its at the case area but I have no clue on how it would be written.


SKYDRIVE REPORT EXAMPLE DB

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

I am not seeing any of these problems

Quote:


1st, Lets say you selected Chad Z as supervisor and Z, Chad as employee and hit the "REPORT" button. it would bring
up a year calendar and page two shoud be all the absences but thats semi not the case. It does bring up the report
BUT it shows the record over and over 18 times
I see a calendar with 9 correctly filled in absence codes, and page 2 is the correct list of the nine absences.

Quote:


BUT lets say I selected Chad Z as supervisor and C, Bob as employee it showes
2 pages. Why?
1st page is the calendar, the second page is the list of their one absence. Looks correct to me.

Quote:


2nd, I am unable to figure out how to get the module mod_rptYearView to use the table tbluAbsenceCodes like you showed me for
the mod_FillTextBoxes module. I know its at the case area but I have no clue on how it would be written
delete the case and replace with this

CODE

'// 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

'see new functions
 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 

RE: Yearly calendar for attendance



RE: Yearly calendar for attendance



RE: Yearly calendar for attendance

(OP)
majp, the code word wonders thanks a million but I'm still having the issue with it showing a calendar and summary page for each absence. I see whats it doing but have no clue why. Lets say Chad Z has 9 absences and I open the report then it will show a page 1-2 nine times (one for each absence).....

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

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)

RE: Yearly calendar for attendance

(OP)
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.....

RE: Yearly calendar for attendance

(OP)
Thank you Majp for all your help I finally finished the DB thumbsup2. 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.....

RE: Yearly calendar for attendance

(OP)
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?

CODE --> vba

Private Sub cmdresize_Click()
  If Me.Section(acFooter).Height = 4188 Then
       Me.Section(acFooter).Height = 7700
       'Me.frm_AbsenteeismPolicySummarySub.Height = 6000
     Else
        'Me.frm_AbsenteeismPolicySummarySub.Height = 1000
        Me.Section(acFooter).Height = 4188
    End If
End Sub 

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

(OP)
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.....

RE: Yearly calendar for attendance

(OP)
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.....

RE: Yearly calendar for attendance

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.

RE: Yearly calendar for attendance

(OP)
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.....

RE: Yearly calendar for attendance

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.

CODE

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.

CODE

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

CODE

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.

RE: Yearly calendar for attendance

(OP)
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.....

RE: Yearly calendar for attendance

I modified yours and will send it back later.

RE: Yearly calendar for attendance

(OP)
Thank You MajP thumbsup2 I will wait for the link...

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

http://www.4shared.com/file/LtpwFYaMba/Maj_P_atten...

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.

RE: Yearly calendar for attendance

(OP)
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.....

RE: Yearly calendar for attendance

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

Quote:

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.

RE: Yearly calendar for attendance

(OP)
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

CODE --> vba

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

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

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

RE: Yearly calendar for attendance

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?

RE: Yearly calendar for attendance

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.

RE: Yearly calendar for attendance

(OP)
Majp, since I had to move the transparent command button that I had on the main form to the sub calendar form I set focus to it and it works.

frm.cmdSubFormTransButton.SetFocus
For i = 1 To 37
Set ctl = frm.Controls("lbl" & i)
Set ctlt = frm.Controls("txt" & i)



I had another question I have been trying to figure out.You know I have ben wanting to enlarge the main forms footer and the absence subform. Well im very close to what I am wanting except im stuck at a DCount. Basicly what im doing is im having an 2 images ontop each other (Visible No) in the subform and in the subforms "Current" event below I want to show the max and hide the min images if the DCount is >=3 and if its <=2 I want to hide both max and min images. The problem is sometimes there is no record for the DCount so its not hiding or anthing. I tried using IsNull = false and zero but nothing works if there is no record. How can I force the DCount to show a 0 records? Thanks!

CODE --> vba

Private Sub Form_Current()
    Dim SubRecCount As Integer
     
    SubRecCount = DCount("*", "qry_AbsenteeismPolicySummarySub") 'Counts records for selected employee
 
MsgBox SubRecCount

    If SubRecCount >= 3 Then    'Show the max button hides the min
        Me.imgMaxSub.Visible = True
        Me.imgMinSub.Visible = False
        
    ElseIf SubRecCount <= 2 Then 'Hides the buttons
        Me.imgMaxSub.Visible = False
        Me.imgMinSub.Visible = False
        
    ElseIf IsNull(SubRecCount) = False Then '
        Me.imgMaxSub.Visible = False
        Me.imgMinSub.Visible = False
    End If
End Sub 

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

First of all your logic does not make sense. You never show the min button based on that logic.

The Dcount returns 0 not null, if there is no records. If it returned null you would know because you would get an error because you could not assign subRecCount = null.

Since 0 is less than 2 this should actually work, so there may be something else going on. My guess is that it is your currentevent. If a form has no records than I do not think the current event fires. I know the current event does not fire in an unbound form. Call it from when you requery the subform.


A better trick to toggle on/off is like this
Me.imgMaxSub.Visible = (SubRecCount >= 3) '
me.imgMinSub.Visible = (SubRecCount <= 2) '

So this part "(SubRecCount >= 3)", can return either true and false.

RE: Yearly calendar for attendance

(OP)

Quote (MaJp)

First of all your logic does not make sense. You never show the min button based on that logic.

I wasnt dont with the code, I was going to have the Min image visible once the subform was enlarged.

Quote (MaJp)

My guess is that it is your currentevent. If a form has no records than I do not think the current event fires. I know the current event does not fire in an unbound form. Call it from when you requery the subform.

I think it is that also because I would at least get a msgbox showing 0 if the SubRecCount fired but I dont get anything. You say call it from where you requery, which is the comboboxes ect and a few other places. Im not sure I know what you mean by "call it". Thanks!!!!!!

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

Every time you change employee combo and the year combo those subform requery. So in the change event of the combos add a call to showHide the the images instead of calling it from the current event.


RE: Yearly calendar for attendance

(OP)
Majp, I have been testing for a few weeks now and it has been working great for myself. I only needed it for myself and never thought to test changeing the supervisor I always just opened the DB selected a supervisor then started selecting employees, I never then went and tried to select a diferent supervisor and what happens when I do is it doenst clear the calendar, it still shows the last employee I was on.

What would I use in the what VBA would I use in the cboSupervisors AfterUpdate Event that would clear the subform calendar? Im sure its already in a module I have but what....

Thanks?

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

(OP)
If I use the below code it will clear the subforms textboxes but is it the correct way?

CODE

clearSubFormTextBoxes Me.SubFormJan.Form
clearSubFormTextBoxes Me.SubFormFeb.Form
clearSubFormTextBoxes Me.subformMar.Form
clearSubFormTextBoxes Me.subFormApr.Form
clearSubFormTextBoxes Me.SubFormMay.Form
clearSubFormTextBoxes Me.SubFormJun.Form
clearSubFormTextBoxes Me.subFormJul.Form
clearSubFormTextBoxes Me.subFormAug.Form
clearSubFormTextBoxes Me.subFormSep.Form
clearSubFormTextBoxes Me.SubFormOct.Form
clearSubFormTextBoxes Me.subFormNov.Form
clearSubFormTextBoxes Me.subFormDec.Form 

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

(OP)
Ok, after messing with the code I posted above I think it works prety good unless there is a better way please let me know.... Here is what I came up with. Thanks!

CODE

Private Sub cboSupervisor_AfterUpdate()
  
If IsNull(Me.cboEmployee) = True Then
'Theres no data in cboEmployee
     Me.cboEmployee.Enabled = True
     Me.cboEmployee.Requery 'Just incase you went back and reselected another supervisor without selecting and employee
     Caption = "Absentee Tracker" 'Clears years of service and displays tracker untill employee is selected
Else
'There is data in cboEmployee lets clear it
     Me.cboEmployee = Null
     Me.cboEmployee.Enabled = True
     Me.cboEmployee.Requery
     Caption = "Absentee Tracker" 'Clears years of service and displays tracker untill employee is selected
     'Clear all subForm Calendars using (mod_FillTextBoxes) (Public Sub clearSubFormTextBoxes)
     clearSubFormTextBoxes Me.subFormJan.Form
     clearSubFormTextBoxes Me.SubFormFeb.Form
     clearSubFormTextBoxes Me.subformMar.Form
     clearSubFormTextBoxes Me.subFormApr.Form
     clearSubFormTextBoxes Me.SubFormMay.Form
     clearSubFormTextBoxes Me.SubFormJun.Form
     clearSubFormTextBoxes Me.subFormJul.Form
     clearSubFormTextBoxes Me.subFormAug.Form
     clearSubFormTextBoxes Me.subFormSep.Form
     clearSubFormTextBoxes Me.SubFormOct.Form
     clearSubFormTextBoxes Me.subFormNov.Form
     clearSubFormTextBoxes Me.subFormDec.Form
End If

End Sub 

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

That is exactly correct. but for portability of the code, I would put the clears into a single procedure and reuse the variables that were created for the subforms.

CODE -->

Public Sub ClearAllTextBoxes
     clearSubFormTextBoxes sFrmJan
     clearSubFormTextBoxes sFrmFeb
     clearSubFormTextBoxes sFrmMar
     clearSubFormTextBoxes sFrmApr
     clearSubFormTextBoxes sFrmMay
     clearSubFormTextBoxes sFrmJun
     clearSubFormTextBoxes sFrmJul
     clearSubFormTextBoxes sFrmAug
     clearSubFormTextBoxes sFrmSep
     clearSubFormTextBoxes sFrmOct
     clearSubFormTextBoxes sFrmNov
     clearSubFormTextBoxes sFrmDec
End Sub 

We defined at the class level variables for each of the subforms. Makes the code a little easier to rewrite (shorter). You have similar procedures for adding textbox values for employees, and for adding all the month labels. Now you can call this from other locations. There may be other events or instances where you need to do this. Then replace your clears with the single line

ClearAllTextBoxes

RE: Yearly calendar for attendance

(OP)
Majp, Here is another thought. If an employee lets say came in late then worked for part of the day then left early then that would mean I would have to enter 2 absences or instances into thier atendance calendar and right now you cna only do one. What would be a goood way to aproch this so I can show two instances in my calendar? I dont ever think there will be more than two in a day.

Thanks!

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

You would have to modify your pop to either have an add new button, to add a second record and just move your form to a new record. The problem wiht that is you can not see how many codes you have listed. Or design the form with a master form, subform. In the master you would have the date, and employee information, the subform would be the combo to pull down the codes and a textbox for the times. This way you could see all the codes for that date. This would be easy to do, but takes a little thinking. The main form would not be bound, and you would pass the employeeID and date using the open args. I think that is how it is done already. Now link the subform date field, and employee ID field using the child master relationship. This is a trick that many people do not know. You can link a subform field to a forms field, or to a control.

Something like
link Master Fields: txtBoxEmployeeID; txtBoxAbsenceDate
link Child Fields: EmployeeID, AbsenceDate

Since you pass to the main form the EmployeeID, you can show the employee name and other information using a Dlookup on the main form.


So now that you can add, edit, and delete multiple absences for an employee for a given day. To display you would modify it a little. Just have to read all the absences for the day and put them into a string.
current Code

CODE

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
    Set rs = CurrentDb.OpenRecordset(strSql)
    clearSubFormTextBoxes frm    'Uses(clearTextBoxes)Sub to clear the textbox grid on the frm as (frm_YearCalendar)

    Do While Not rs.EOF
        AbsenceDate = rs!AbsenceDate
        AbsenceCode = rs!AbsenceCode
        AbsenceTextColorCode = rs!AbsenceTextColorCode
        AbsenceColorCode = rs!AbsenceColorCode
        'strMonth = Format(AbsenceDate, "mmm")
        intDay = Day(AbsenceDate)
        'IntMonth = Month(AbsenceDate)
        FirstDayOfMonth = getFirstOfMonth(TheYear, TheMonth)    'First of month
        intOffSet = getOffset(TheYear, TheMonth, vbSaturday)    'Offset to first label for month.
        Set ctl = frm.Controls("txt" & intDay + intOffSet)
        ctl.Value = AbsenceCode    'Displays the text or absencecode in the textbox to whats indicated in tbluAbsenceCodes
        ctl.BackColor = AbsenceColorCode    'Changes the texbox(s) backcolor to whats indicated in tbluAbsenceCodes
        ctl.ForeColor = AbsenceTextColorCode    'Changes the texbox(s) text to whats indicated in tbluAbsenceCodes
        rs.MoveNext
    Loop 

So first get a recordset of all days that have absences then use that to get a new recordset for each day.

CODE

strSql = "Select distinct AbsenceDate from qry_FillTextBoxes where EmployeeID = " & EmpId    'Query that finds the absence Year() by employeeID
    strSql = strSql & " AND year(AbsenceDate) = " & TheYear & " AND Month(AbsenceDate) = " & TheMonth
    Debug.Print strSql
    Set rs = CurrentDb.OpenRecordset(strSql)
    clearSubFormTextBoxes frm    'Uses(clearTextBoxes)Sub to clear the textbox grid on the frm as (frm_YearCalendar)
    'loop the days with absences for that month
    Do While Not rs.EOF
        AbsenceDate = rs!AbsenceDate
        strSql = "Select * from qry_FillTextBoxes where EmployeeID = " & EmpId    'Query that finds the absence Year() by employeeID
        strSql = strSql & " AND absenceDate = #" & AbsenceDate & "#"
        Set rsDay = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
        Debug.Print strSql
        Do While Not rsDay.EOF
            AbsenceCode = rsDay!AbsenceCode
            Debug.Print " in loop " & AbsenceCode
            Debug.Print rsDay.AbsolutePosition
            If strCodes = "" Then
             strCodes = AbsenceCode
           Else
             strCodes = strCodes & "," & AbsenceCode
           End If
           AbsenceTextColorCode = rsDay!AbsenceTextColorCode
           AbsenceColorCode = rsDay!AbsenceColorCode
           rsDay.MoveNext
        Loop
        
           Debug.Print strCodes & EmpId
          'Not sure what to do here with the colors, because you can only have one set per textbox
           intDay = Day(AbsenceDate)
           FirstDayOfMonth = getFirstOfMonth(TheYear, TheMonth)    'First of month
           intOffSet = getOffset(TheYear, TheMonth, vbSaturday)    'Offset to first label for month.
           Set ctl = frm.Controls("txt" & intDay + intOffSet)
           ctl.Value = strCodes  'Displays the text or absencecode in the textbox to whats indicated in tbluAbsenceCodes
           ctl.BackColor = AbsenceColorCode    'Changes the texbox(s) backcolor to whats indicated in tbluAbsenceCodes
           ctl.ForeColor = AbsenceTextColorCode    'Changes the texbox(s) text to whats indicated in tbluAbsenceCodes
           strCodes = ""
        rs.MoveNext
    Loop 

Not sure how you want to color this, because you cannot dual color. Maybe you could come up with a color for more than one absence.

RE: Yearly calendar for attendance

(OP)
Majp, Im thinking maybe having two text boxes for each date because I want to be able to display two diferent colors on the calendar. I tried to follow your directions and imedently got lost.

Quote (Majp)

You would have to modify your pop

Are you talking about frm_CalendarInputBox? if so I tried to allow it to add a second record but it wont keep the date. I would rather just have two sections like what I have now in my frm_CalendarInputBox but the first section would be for one textbox thats on the calendars date and the other for the others textbox on the same date.


This evening after work EST I will upload a fully working DB of what I have so far because I have also added the hours into the textbox which was needed.


Thanks!

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

If you go with the two set of textboxes there will be a lot of modifications throughout the database, forms, and code.
The better coice may be to change the format choice of the textboxes from "Plain Text" to "Rich Text".
Then if there are two or more records you could then apply different formatting (text color and text highlight) to each absence.

So what you would have to do is select all your textboxes and under the data tab change the
"Text Format " from "Plain Text" to "Rich Text"
Now as you build your text string you are going to wrap it in html tags. To make things easier put the tags in the table.
So here is the string for the word "absence One" with red background and black text. (Absence One represents one of your codes)

<font color="#0C0C0C" style="BACKGROUND-COLOR:#FF0000">Absence One</font>

So if these tags are kept in the table your code would look something like

CODE

Do While Not rsDay.EOF
            AbsenceCode = rsDay!AbsenceCode
            AbsenceColorTag = rsDay!AbsenceColorTag "The html tag stored in the table
            AbsenceCode = absenceColorTag & AbsenceCode & "</font>"
           If strCodes = "" Then
             strCodes = AbsenceCode
           Else
             strCodes = strCodes & "," & AbsenceCode
           End If
           AbsenceTextColorCode = rsDay!AbsenceTextColorCode
           AbsenceColorCode = rsDay!AbsenceColorCode
           rsDay.MoveNext
        Loop
        'I think you actually need to wrap it with a <div> </div>
        strCodes = "<div>" & strCodes & "</div>" 

The final string for two codes, first is black text red background, second is white text blue background would look like this:

<div>
<font color="#0C0C0C" style="BACKGROUND-COLOR:#FF0000">Absence One</font>
<font color=white style="BACKGROUND-COLOR:#000080"> Absence Two</font>
</div>

This may not look as good because only the text is highlighted not the whole text box, but the calendar will look better. So what you could do is if there is only one record for the day is choose to highlight the wholed textbox, if more than one use the richtext formats.

So you could do a recordset count first

CODE

if rsDay.recordcount > 1 then
       Do While Not rsDay.EOF
            AbsenceCode = rsDay!AbsenceCode
            AbsenceColorTag = rsDay!AbsenceColorTag "The html tag stored in the table
            AbsenceCode = absenceColorTag & AbsenceCode & "</font>"
           If strCodes = "" Then
             strCodes = AbsenceCode
           Else
             strCodes = strCodes & "," & AbsenceCode
           End If
           AbsenceTextColorCode = rsDay!AbsenceTextColorCode
           AbsenceColorCode = rsDay!AbsenceColorCode
           rsDay.MoveNext
        Loop
else
       Do While Not rsDay.EOF
            AbsenceCode = rsDay!AbsenceCode
             If strCodes = "" Then
             strCodes = AbsenceCode
           Else
             strCodes = strCodes & "," & AbsenceCode
           End If
           AbsenceTextColorCode = rsDay!AbsenceTextColorCode
           AbsenceColorCode = rsDay!AbsenceColorCode
           rsDay.MoveNext
        Loop
end if 

RE: Yearly calendar for attendance


It would look something more like this
If more than one record than you could still apply a generic background to the textbox (maybe light gray) and then do the tagging.

RE: Yearly calendar for attendance

Doing it that way instead of the two set of textboxes would be minutes of work instead of many hours of work.
Also I have built a similar version in Visual Studio using vb.net. So this is my idea for having the ability to do multiple codes per day.

This makes it nice to add, edit, delete and see all the activities for the day.

RE: Yearly calendar for attendance

(OP)
Majp, below is the version I have been using. I will try to get what your talking about working but I have never herd of using html so its new to me. You say to change to rich text in my popup but I only have two text boxes. Anyways here is the link

ATTENDANCE v3 (Public Version)

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

No the rich text is the calendar, so you can high light multiple strings in one textbox. I actually have it working and will post it soon.

RE: Yearly calendar for attendance

(OP)
Oh Ok.... I will wait for your example so I can try to incorperate into mine. Thanks!

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance



Here is the link to the file. It also includes a form that will allow you to edit text and then get the proper tags for that format. "RichTextHelper"
http://www.4shared.com/file/fG0NbDNlce/MajP_Attend...
Pretty cool, I have to admit.

RE: Yearly calendar for attendance

(OP)
Majp, Yes it is very cool... I set mine up so each absence is on ists own new row and I will only ever need two max so mine would look like:

VFMLA
PD

I am trying to figure out how to extend the color for the full row using html in the caleder but its not working. Here is what I did.

1) In the tbluAbsenceCodes/AbsencColorTag I added ; display:block; so it now looks like:
<font color=white style='BACKGROUND-COLOR:#458B00; display:block;'>

I then added <br> to the Public Sub FillSubFormTextBoxes so it forces a line.

CODE --> vba

If strCodes = "" Then
                AbsenceCode = AbsenceColorTag & AbsenceCode & "<br></font>"
                strCodes = AbsenceCode
              Else
                AbsenceCode = AbsenceColorTag & " " & AbsenceCode & "<br></font>" 

I run it and check the Debug.Print you had and it shows up correctly but it just doesnt work.

CODE --> html

<div>
<font color=white style='BACKGROUND-COLOR:#458B00; display:block;'>V<br></font> 
<font color=black style='BACKGROUND-COLOR:#FFA500; display:block;'>FMLA<br></font>
</div> 

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

so build a function to pad the string to make it appear to fill the whole block.
I figure about 9 total characters
Since you want it centered you need some spaces on the front and on the back.

CODE

Public Function PadString(strText 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)
  'since the string is centered in the box need to pad half to front and half to back
  If textlength < TotalLength Then
    spacesToPad = TotalLength - textlength
    frontPad = spacesToPad \ 2
    backPad = spacesToPad - frontPad
    strText = Space(frontPad) & strText & Space(backPad)
  End If
  PadString = strText
End Function 

so pass in "PD" and you would get something like " PD "
unfortunely to make this work well, you need to then left align the ctl.

So the updated code looks like

CODE

Public Sub FillSubFormTextBoxes(frm As Access.Form, EmpId As Long, TheYear As Integer, TheMonth As Integer)
'==================================================================================================
'//Fills the grids textbox(s) with data and color on the correct date an absence was entered on.
'==================================================================================================
    Dim ctl As Access.TextBox
    Dim rs As DAO.Recordset
    Dim rsDay As DAO.Recordset
    Dim strSql As String
    Dim strMonth As String
    Dim intDay As Integer
    Dim FirstDayOfMonth As Date
    Dim intOffSet As Integer
    Dim strCodes As String
    Dim AbsenceDate As Date   'Field in tbl_YearCalendar
    Dim AbsenceCode As String   'Field in tbluAbsenceCodes
    Dim AbsenceColorCode As String   'Field in tbluAbsenceCodes - Added to color textbox(s)
    Dim AbsenceTextColorCode As String   'Field in tbluAbsenceCodes - Added to color textbox(s)text
    Dim AbsenceColorTag As String
    On Error GoTo errlbl:
    strSql = "Select distinct AbsenceDate from qry_FillTextBoxes where EmployeeID = " & EmpId    'Query that finds the absence Year() by employeeID
    strSql = strSql & " AND year(AbsenceDate) = " & TheYear & " AND Month(AbsenceDate) = " & TheMonth
    'Debug.Print strSql
    Set rs = CurrentDb.OpenRecordset(strSql)
    clearSubFormTextBoxes frm    'Uses(clearTextBoxes)Sub to clear the textbox grid on the frm as (frm_YearCalendar)
    'loop the days with absences for that month
    Do While Not rs.EOF
       AbsenceDate = rs!AbsenceDate
       strSql = "Select * from qry_FillTextBoxes where EmployeeID = " & EmpId    'Query that finds the absence Year() by employeeID
       strSql = strSql & " AND absenceDate = #" & AbsenceDate & "#"
       Set rsDay = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
       'Debug.Print strSql
       rsDay.MoveLast
       rsDay.MoveFirst
       If rsDay.RecordCount > 1 Then
          AbsenceColorCode = 16777215
          AbsenceTextColorCode = 1
          Do While Not rsDay.EOF
             AbsenceCode = rsDay!AbsenceCode
             AbsenceColorTag = rsDay!AbsenceColorTag 'The html tag stored in the table
             If strCodes = "" Then
                'Pad your string
                AbsenceCode = PadString(AbsenceCode, 9)
                AbsenceCode = AbsenceColorTag & AbsenceCode & "</font>"
                strCodes = AbsenceCode
              Else
                AbsenceCode = PadString(AbsenceCode, 9)
                'Here is the location for the Break
                AbsenceCode = AbsenceColorTag & "<br>" & AbsenceCode & "</font>"
                strCodes = strCodes & AbsenceCode
              End If
              rsDay.MoveNext
           Loop
           strCodes = "<div>" & strCodes & "</div>"
           Debug.Print strCodes
       Else
          AbsenceCode = rsDay!AbsenceCode
          strCodes = AbsenceCode
          AbsenceTextColorCode = rsDay!AbsenceTextColorCode
          AbsenceColorCode = rsDay!AbsenceColorCode
          rsDay.MoveNext
       End If
       intDay = Day(AbsenceDate)
       FirstDayOfMonth = getFirstOfMonth(TheYear, TheMonth)    'First of month
       intOffSet = getOffset(TheYear, TheMonth, vbSaturday)    'Offset to first label for month.
       Set ctl = frm.Controls("txt" & intDay + intOffSet)
       'change the alignment if it has more than one code
       If rsDay.RecordCount > 1 Then
         ctl.TextAlign = 1
       Else
         ctl.TextAlign = 2
       End If
       ctl.Value = strCodes  'Displays the text or absencecode in the textbox to whats indicated in tbluAbsenceCodes
       ctl.BackColor = AbsenceColorCode    'Changes the texbox(s) backcolor to whats indicated in tbluAbsenceCodes
       ctl.ForeColor = AbsenceTextColorCode    'Changes the texbox(s) text to whats indicated in tbluAbsenceCodes
       strCodes = ""
       rs.MoveNext
    Loop
    Exit Sub
errlbl:
    ' Debug.Print "Form is Nothing = " & frm Is Nothing
     Debug.Print Err.Number & " " & Err.Description & " in Fill textboxes"

End Sub 

FYI,
You will have to resize all the textboxes in the month calendar subform or you will not be able to see the second code.
Then you will have to resize each of the subform controls to fit the larger month calendar.
However, this is why using the subform is such an easier approach. Also you may want to change the font to a fixed width font (each character the same size) to make the padding equal. Calibri is not fixed width so letters and spaces are different sizes.

RE: Yearly calendar for attendance



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

RE: Yearly calendar for attendance

(OP)
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.....

RE: Yearly calendar for attendance

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

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

Quote:

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

RE: Yearly calendar for attendance

(OP)
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 tongue

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

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

RE: Yearly calendar for attendance

(OP)
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.....

RE: Yearly calendar for attendance

Quote:

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.

RE: Yearly calendar for attendance

(OP)
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".

CODE

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

Changed To:
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.

CODE

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)

CODE

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)

CODE

Public Function LimitRecords(frm As Access.Form, Optional RecLimit As Integer = 1)
   'http://www.datagnostics.com/dtips/limitentries.html
    ' 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.....

RE: Yearly calendar for attendance

Looks good. What is next?

RE: Yearly calendar for attendance

(OP)
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:

CODE --> VBA

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

RE: Yearly calendar for attendance

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

RE: Yearly calendar for attendance

(OP)
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:

Quote (Majp)

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.

Quote (Majp)

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

RE: Yearly calendar for attendance

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.

RE: Yearly calendar for attendance

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:

Quote (SkipVought)

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

RE: Yearly calendar for attendance

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.

RE: Yearly calendar for attendance

http://www.4shared.com/file/E6I7dtJRce/Attendance_...
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?

RE: Yearly calendar for attendance

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.

RE: Yearly calendar for attendance

(OP)
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.....

RE: Yearly calendar for attendance

They actually have a desktop utility that is really nice. Super easy to upload, download, and manage your files. Easy to get URLs and download by URL. So if I want to upload or download something I use that. Never have to open the web page. That was the first time I had actually opened the web page, and saw how horrible it is. I feel bad now for all the people I sent there. I might try Dropbox since that seems common.

RE: Yearly calendar for attendance

Dropbox works really well as an easy file syncing tool as well as online storage, btw. I've used it, in test, to sync files from my phone to my PC... and in the cloud.

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

RE: Yearly calendar for attendance

(OP)
MajP, I messed with the report at work a bit and noticed something weird... It doesn't print whats displayed on the calendar report. I tried it several times and what it is doing is lets use Chad Z for 2014 instance. It will show everything correct on the report and when you print it out it prints the calendar, Holidays and only the single absences that were entered and it wont print the text for the absences either. So what it prints lets just say for Jan 29 would be a green square without the text (VFML 5.00) and Feb 17 would be a blue square no text and March there wouldn't be anything for the 6th and 10th because they had double absences but the 5th would be a blue square. Ideas?

Mabe I misses something when moving data. I noticed you removed the module mod_YearViw. and you changed one line in the module mod_FillMonthLabels but I just exported the whole thing. Then you copy and pasted the calender subform to subrpt. I also noticed you added the same code as the frm_YearCalendar(s) On load event to the rpt_YearView on load event. and everything works except it prints something else....

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

May be an issue with the printer handling rich text. Try export to PDF and then print. If that works I have code to automate it.

RE: Yearly calendar for attendance

My guess is that this is an access limitation, in how RichText is handled during printing from a report. I tried exporting in all of the formats and get the same results. However, not sure why this is happening, because the year calendar form prints fine.
So the very simple workaround is to build a form that looks identical to your report. This should take about 10 minutes because you only have a textbox year, employee name, then 12 calendar subforms, then another subform. The source objects for all the subforms remain the same.
If you want you can also use the existing report for viewing (since reports are usually a little easier to view on the screen), but when you hit the print button you can have it print the new form instead.

RE: Yearly calendar for attendance

(OP)
Majp, I will give it a try today. I will build a identical FORM to the REPORT and give it a try but I have to print to .pdf because I have no printer at home just at work. If I print to pdf would that show as if I printed to paper or would I still have to print to paper to see for sure, and I cant do that until Monday...

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

Like I said the issue is with all export formats, not just printing. Currently it will not work exporting the report to pdf, but it does work if you export the form to PDF. So if your new export works with PDF pretty certain it will print as well. Personally I set up most of my application with a reports menu. When I click on a report if gives me the option to view an access report, open it as PDF, open as Word RTF. It is a module I add to most applications. If I have report views I want to share, I always want that option to save as PDF. If I have reports that people want to edit I save as RTF. Unfortunately the formatting is usually a little messed up and will need to get cleaned up. If I want to share a properly formatted document in WORD, then I usually build a template and write to that template through code.

RE: Yearly calendar for attendance

(OP)
MajP, I built the form identical to the report and open it in preview and print but it also prints only the colors and no text and no colors or text of the absences with two entries. Thanks!

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

Sorry, I now see the cause, but I do not know the answer, or even if this can be fixed. So I thought this worked, but then found out why it does not. The calendar or any other richtext will print properly unless it is in a subform/subreport. I assume it has to do with the order things are rendered and then printed. I tested a calendar as a standalone form and all the codes printed.
I cannot think of any easy workaround.
My suggestion would be for printing you redo the report to run using the older code to fill and color the textboxes and do away with the richtext formatting. You will not be able to color multiple codes, so then if there are two codes you will have to pick one color, or no color.

RE: Yearly calendar for attendance

So I found a solution. The issues was not with the rich textbox, but with unbound controls in a subform or subreport. So one solution would be to set the control source instead.

I went with the controlsource approach. So with that approach you still have to build a form to work as your report. The reason is if you use a report then the you cannot set the controlsource after load it.
So using your new form that is a mirror of the old report, then change the code in the procedure

FillSubFormTextBoxes
remove the code where you change the ctl.value. You no longer set the value of the controls. Replace with the following two lines

CODE

strCodes = "=" & Chr(34) & strCodes & Chr(13) & Chr(10) & Format(AbsenceTime, "0.00") & Chr(34)
ctl.ControlSource = strCodes 

Then where you clear the textboxes in clearsubformtextboxes instead of setting the value set the control source

ctl.ControlSource = ""

RE: Yearly calendar for attendance

I believe there is another solution, and this one uses the report with the subforms. In that version we were loading the labels and the textboxes of the subform when the report loaded. Instead if you updated the subforms from the detail section's format event it should work. So basically what was happening is that is was printing before the values of the subform was loaded. By changing the event that loads the subforms it should work.

RE: Yearly calendar for attendance

(OP)
majp, sorry i didnt respond i didnt get an email saying you responded to the thread... I have been racking my brain on reading about this rich text and how to print and I see you have come up with a solution? Your last post if im correct thats all I really would need to do is call it from the detail section but im not quite understanding? What im getting is your saying its loaded but not acually loaded, like i would have to requery after the page loads?. Thanks for not giving up....

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

I tested the second solution and still was unable to get it to work. It may work if you made the subform into a subreport. But the first solution does work. So instead of setting the value of the textbox you set the control source property. The only problem with this is that you are stuck using a form and subform (which you should already have done).

RE: Yearly calendar for attendance

(OP)
It works perfectly with the form! your a genius! I have no clue how you do it..... thumbsup2

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

Quote:

I have no clue how you do it
I do it by answering posts like this. I learn as much or more. I can now quickly knock out a year view form, and have code that can be reused. I am a lot smarter on rich text formatting, and understand some limitations of printing unbound controls in subforms/reports and the work arounds. My library of code grows every time. So I will stick all of this code in my library, and then next person that asks about year view forms, I will quickly whip this out. They will think I am a genius to be able to so quickly come up with a solution. In truth I just select from my Chinese menu of code and databases I have. The year view or month view is a common question, so next time you can be the teacher and you will get smarter. You learn more from teaching than from being taught.

RE: Yearly calendar for attendance

(OP)
Majp, after testing now im finding another problem... This one has to do with the (frm_CalendarInputBox). It works BUT sometimes it doesnt work. What
happens is I added the code/Public Function I found into the (subFormCalendarInputBox)

CODE

Public Function LimitRecords(frm As Access.Form, Optional RecLimit As Integer = 1)
'http://www.datagnostics.com/dtips/limitentries.html
' 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 


Then call it from its on current event using

=LimitRecords([Form],2)


Here is whats weird. If I have a entry on a date lets say March 1st and I click to open it and it shows the entry plus a second blank new enty which is correct. I then
close the frm_CalendarInputBox and I click on the date again and this time it MIGHT show the blank new entry first then the origional old entry second. At this point im forced to
create a record and I start getting errors ect. and this only happens if I open and the new record is the first record.

Im not sure if its from the above code OR if its from the code used when the form (frm_CalendarInputBox) On Load event opens .

CODE

Private Sub Form_Load()
    Dim selectedDate As String
    Dim EmpId As Long
    Dim strWhere As String
    Dim rs As DAO.Recordset
    If Me.OpenArgs & "" <> "" Then
        selectedDate = Split(Me.OpenArgs, ";")(0)
        EmpId = Split(Me.OpenArgs, ";")(1)
        strWhere = "AbsenceDate = #" & selectedDate & "# AND EmployeeID = " & EmpId
        'Debug.Print strWhere
        Set rs = Me.Recordset
        rs.FindFirst strWhere
        If rs.NoMatch Then
            'Create a New Record
            DoEvents
            DoCmd.GoToRecord , , acNewRec
            Me.EmployeeID = EmpId
            Me.AbsenceDate = CDate(selectedDate)
        End If
    End If
End Sub 

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

(OP)
Ok, messed with it a bit more and what its doing is if you enter a new absence then close the popup then reopen the same date, it will open to a new record but with the code you are limited to two records. the new record is new the first record and the old one is the second record so now that made a blank record in my table. The last DB has the same frm_CalendarInputBox and subFormCalendarInputBox did you still have a copy? Thanks!

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

Since you are now using a form and subform for your input, you need to redesign things.

The main form needs to be unbound.

To make this easier get rid of the hidden combobox called "txtEmployeeid"
Add a hidden control on the main form called "txtEmployeeID"
Get rid of the control source in the text box txtEmployee and rename it txtEmployeeName

So the new code is simply this

CODE

Private Sub Form_Load()
    Dim selectedDate As String
    Dim EmpId As Long
    Dim strWhere As String
    Dim rs As DAO.Recordset
    If Me.OpenArgs & "" <> "" Then
        selectedDate = Split(Me.OpenArgs, ";")(0)
        EmpId = Split(Me.OpenArgs, ";")(1)
        Me.txtEmployeeName = DLookup("EmpLName & ', ' & EmpFName", "tbluEmployees", "EmployeeID = " & EmpId)
        Me.txtEmployeeID = EmpId
        Me.txtAbsenceDate = CDate(selectedDate)
    End If
End Sub 

Now your validation should occur in the subform, not in the close event of the main form. Do not try to validate in the close event. In the subform do your validation of the controls in the before update event. If you try to validate in a close event you will get in a continous loop. The before update event allows you to cancel the event if they do not have all the correct values filled in. Google "verify access data in beforeupdate event" for examples.

In your tableYear you need to make sure you have required fields, this makes troubleshooting a lot easier. You should never have a blank record because at the table level you should have a required absenceID, absenceDate, etc. That would have helped to find this problem.

RE: Yearly calendar for attendance

(OP)
Majp, that did the trick.... sorry I didnt reply back quicker I have been trying to finalize this DB to post another updated "Public Version". Below is a link to my MS OneDrive AKA SkyDrive from Microsoft they changed the name anyway, it is a direct DL to the file so no waiting! I figured out how to change the link they give you to share your file/files and make it a downloadable link. Thats all you have to do is dont shorten the link and when you get it change the first word "resid" to "download" without quotes and thats it.... Anyway, I think im going to start working on a way to get employees, new absences ect entered into the tables ect before moving further.

NEWEST PUBLIC VERSION v4.XX

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

(OP)
Majp, I have found an error I cant get rid of. the database runs great so I decided to hide the navigation pane along with the ribbon. In the settings I unchecked the navigation pane so it doesn't show and now if I open a report and right click and select (Email as .pdf) it opens as it should ect and i can send BUT if I cancel the email it gives me a (SendObject action was canceled) error and I ok out of that and it closes my report as expected but now my DB is froze and I try to close and it gives me an error (You may need to interrupt the module) error. I have to CTL+ALT+DEL and stop access process to get it to shut down. Now if I show the navigation bar everything works fine.... Ideas?

Problem is the (mod_ShortCutMenuCommands)\ Function EmailAsPDF()

Thanks....

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

In VBIDE
Tools, Options, General Tab, Error Trapping, "Break on Unhandled Errors"

You have it set up to break on all errors so your error trapping does absolutely nothing. It should never ever be set up like that, unless you are doing some type of debugging and want to identify the error number. Also on the first tab ensure you have "require variable declaration". This forces you to declare all variables. If not you can get unmanageable code.

CODE

Function EmailAsPDF() As String
'==================================================================================================
'//Code works with the macros so I can get a right click and choose to send .pdf by email
'==================================================================================================
    Dim strSubject As String
    Dim strMessageText As String
    Dim rptCur As String
    On Error GoTo errlbl
    rptCur = Screen.ActiveReport.Name

    strSubject = "Absence Report For " & Screen.ActiveReport.txtEmployeeName
    strMessageText = "Attached is a report for or between " & Screen.ActiveReport.txtYear & _
                   " for " & Screen.ActiveReport.txtEmployeeName & "."
    'vbNewLine & vbNewLine
    DoCmd.SendObject acSendReport, Screen.ActiveReport.Name, acFormatPDF, , , , strSubject, strMessageText, True
    'Close the report or form
    DoCmd.Close acReport, rptCur
    Exit Function
errlbl:
  '2501 is the cancel error
  If Err.Number <> 2501 Then
    MsgBox Err.Number & " " & Err.message & vbCrLf & " In EmailPDF"
  End If
  If CurrentProject.AllReports(rptCur).IsLoaded Then
      DoCmd.Close acReport, rptCur
  End If
End Function 

RE: Yearly calendar for attendance

(OP)
Majp, I still get the error, also my copy in VBIDE- Tools/Options/General Tab/Error Trapping: Break on Unhandled Errors is checked but the require variable declaration wasnt so I check that and saved. I then replaced your code over mine and tried and I still get the same exaxt errors. In you texting did you uncheck Access Options/Current Database/Navagation: Display Navagation Pane? I have that unchecked and I am unable to exit the email but if I have that checked I can exit that email without these errors. Whats this have to do with the navagation pane?

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

So I googled this and there are lots of discussion of this, but did not see any solutions. Pretty much requires the exact same conditions you mentioned. If you make the report not "pop up" it will work. However, that is not a viable workaround because if the form is dialog then the report would end up behind it. You could do something like hit the report button, hide the form, open the report (not pop up), close the report, re show the form. This might not look as clean, but may be an option.

What happens is that an internal error is handled by access and it locks up before the error is trapped in code. The only solution which is pretty major is to bypass the docmd.sendobject and use automation of Outlook.
http://www.experts-exchange.com/Database/MS_Access...

RE: Yearly calendar for attendance

I believe the problem may be with the macro for the shortcut menu not the vba code. From some of what I have read is that if a macro fails there is no way for it to release the connection. My guess is rebuild this shortcut menu without the use of macros.

Quote:


This is one of the reasons I avoid the macros in Access because they don't release the object or the shell, the Macro processor keeps a thread locked to the process and then throws a tantrum when something stalls the process.

I tried this by going into the Macro and adding an OnError step. And you get the message here.

RE: Yearly calendar for attendance

(OP)
Majp, Ok I deleted/removed completly the macros and used the VBA below in each report to bring up a right click menu and I still get the same results/errors if I close the email even with error handling. The code uses access controls Policy ID numbers or calls the module like EmailAsPDF(). This was what I could find for a right click menu after Googeling.... Thoughts?

CODE

Private Sub Report_Load()
    CreateReportShortcutMenu 'Loads shortcut menu
End Sub

Private Sub CreateReportShortcutMenu()
'==================================================================================================
'//In the Report_Load Event enter CreateReportShortcutMenu then in the reports Property/Shortcut
'   Menu Bar enter the MenuName "vbaShortCutMenu"
'
' You must Reference to Microsoft Office xx.0 Object Library
'   Office 2003 - use 11.0
'   Office 2007 - use 12.0
'   Office 2010 - use 14.0
'   Office 2013 - use 15.0

' This Reference IS NOT the same as Microsoft Office 14.0 Access database engine Object Library
'==================================================================================================

    Dim MenuName As String
    Dim CB As CommandBar
    Dim CBB As CommandBarButton

    MenuName = "vbaShortCutMenu"

    On Error Resume Next
    Application.CommandBars(MenuName).Delete
    On Error GoTo 0

    'Create the menu
    Set CB = Application.CommandBars.Add(MenuName, msoBarPopup, False, False)

    'The following code creates the options for the menu
    Set CBB = CB.Controls.Add(msoControlButton, 15948, , , True)
    CBB.Caption = "Print..."

    Set CBB = CB.Controls.Add(msoControlButton, 7, , , True)
    CBB.Caption = "Zoom: 100%"

    Set CBB = CB.Controls.Add(msoControlButton, 12499, , , True)
    'Starts a new group.
    CBB.BeginGroup = True
    'Change the caption displayed for the control.
    CBB.Caption = "Save as PDF"

    Set CBB = CB.Controls.Add(msoControlButton, , , , True)
    CBB.Caption = "Send By E-mail..."
    CBB.Tag = "Send E-mail..."
    CBB.OnAction = "=EmailAsPDF()"  'Calls a module with Function EmailAsPDF()

    'Adds the Close command.
    Set CBB = CB.Controls.Add(msoControlButton, 923, , , True)
    'Starts a new group.
    CBB.BeginGroup = True
    'Change the caption displayed for the control.
    CBB.Caption = "Close Report"

    Set CB = Nothing
    Set CBB = Nothing

End Sub 

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

I guess it disproves the theory that it is the macro not releasing.
So the other suggestion is to save the report and use automation instead of the docmd.sendobject. From what the poster says, it cured the problem. So the trick is to open the report, save it to disk, automate outlook, create email, and [optional] delete the report from disk.

So their basic code is

CODE

Set objOutlook = CreateObject("Outlook.Application")
            Set o = objOutlook.CreateItem(olMailItem)
            o.To = strTO
            o.Subject = "DCN Notice:   DCN " & strDCN 
            o.Body = "DCN " & strDCN & " has been submitted for review/assignment"
            o.Importance = olImportanceHigh  'High importance
           'here is where they first save the report to disk
           'See the code i provided with some additional features to save to a specified location
           'and kill the existing file.
            DoCmd.OutputTo acOutputReport, "rptReviewNotice", acFormatRTF, strLoc
            o.Attachments.Add strLoc, olByValue
            o.Display
            MsgBox "DCN " & strDCN & " has been submitted for review"
ErrSend:
    Select Case err.Number
   'error trap here
Exit Function 


This code portion does not show how you make a full file name and path and if that file name exists how to delete it first. Also when you are done you may want the location of the file so you can kill it, or just leave it and kill it when you overwrite as shown below. So here is an example of opening a report, and saving it to disk. It also returns the full path if you want to kill it once done.

CODE

Public Function OpenReportAndSave(strReportName As String) As String
    'Create report and save as an attachment to the current record
    Dim myCurrentDir As String
    Dim myReportOutput As String
    Dim myMessage As String
    On Error GoTo ErrorHandler
    'In your case the report is already open so no need to open it.
    'DoCmd.OpenReport strReportName, acViewPreview
    myCurrentDir = CurrentProject.Path & "\"
    myReportOutput = myCurrentDir & strReportName & ".pdf"
    If Dir(myReportOutput) <> "" Then ' the file already exists--delete it first.
        VBA.SetAttr myReportOutput, vbNormal ' remove any file attributes (e.g. read-only) that would block the kill command.
        VBA.Kill myReportOutput ' delete the file.
        DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, myReportOutput, , , , acExportQualityPrint
     End If
        
    OpenReportAndSave = myReportOutput
    Exit Function
ErrorHandler:
    MsgBox Error$
End Function 

RE: Yearly calendar for attendance

(OP)
Majp, is there a way I can just hide the Access shell or(application window) this way I can just keep the navigation pane open and be done, I remember I messed with this on a project for work because I hate the shell, I like it to look more like an application than a MS Access program. There is a lot of info out on this subject but in your option whats that best code for it?

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

I am not a big fan of hiding the application window. I have found too many problems doing that. Not very portable, there tends to be other issues. I would look at the approach of automating outlook. The below code does work. Basically show the nav window, send the email, then hide the nav window.

CODE

Function EmailAsPDF() As String
'==================================================================================================
'//Code works with the macros so I can get a right click and choose to send .pdf by email
'==================================================================================================
    Dim strSubject As String
    Dim strMessageText As String
    Dim rptCur As String
    On Error GoTo errlbl
    rptCur = Screen.ActiveReport.Name

    strSubject = "Absence Report For " & Screen.ActiveReport.txtEmployeeName
    strMessageText = "Attached is a report for or between " & Screen.ActiveReport.txtYear & _
                   " for " & Screen.ActiveReport.txtEmployeeName & "."
    'vbNewLine & vbNewLine
    ShowNavigationPane
    DoCmd.SendObject acSendReport, rptCur, acFormatPDF, , , , strSubject, strMessageText, True
    'Close the report or form
    DoCmd.Close acReport, rptCur
    HideNavigationPane
    Exit Function
errlbl:
  '2501 is the cancel error
  If Err.Number <> 2501 Then
    MsgBox "Error"
    'MsgBox Err.Number & " " & Err.message & vbCrLf & " In EmailPDF"
  Else
    MsgBox "Canceled"
  End If
  If CurrentProject.AllReports(rptCur).IsLoaded Then
      DoCmd.Close acReport, rptCur
  End If
  HideNavigationPane
End Function


Public Sub ShowNavigationPane()
  DoCmd.SelectObject acTable, , True
  DoCmd.Minimize
End Sub
Public Sub HideNavigationPane()
  DoCmd.SelectObject acTable, "Tbl_YearCalendar", True
  DoCmd.RunCommand acCmdWindowHide
End Sub 

RE: Yearly calendar for attendance

(OP)
Majp, LOL I wish I read that before I did the whole DB with hiding the shell.... I got everything to work but I see what your saying, I ran into a lot of "I have to do it this way" in order to get it to work..... The example in your last post was what I had used when all this began and I think im going to go back with it and be done with this stupid email as pdf thing.... Yes it sucks seeing it on the side then not but Oh well....

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

Like I said the cleanest is to use Outlook automation. This works fine. Need to add a reference to Outlook

CODE

Public Function EmailAsPDFUsingAutomation()
    On Error GoTo Error_Handler
    Dim objOutlook As Outlook.Application
    Dim objEmail As Outlook.MailItem
    Dim strSubject As String
    Dim strMessageText As String
    Dim rptCur As Access.Report
    Dim AttachmentName As String
    Set rptCur = Screen.ActiveReport

    strSubject = "Absence Report For " & rptCur.txtEmployeeName
    strMessageText = "Attached is a report for or between " & rptCur.txtYear & _
                   " for " & rptCur.txtEmployeeName & "."
    Set objOutlook = CreateObject("Outlook.application")
    Set objEmail = objOutlook.CreateItem(olMailItem)
    AttachmentName = SaveOpenReportAsPDF(rptCur.Name)
    Debug.Print AttachmentName
    With objEmail
        '.To = strgTo
        .Subject = strSubject
        .Body = strMessageText
        .Attachments.Add AttachmentName
        .Display
    End With
    CloseAllReports
Exit_Here:
    Set objOutlook = Nothing
    Exit Function

Error_Handler:
    MsgBox Err & ": " & Err.Description
    CloseAllReports
    Resume Exit_Here
End Function

Public Function SaveOpenReportAsPDF(strReportName As String) As String
    'Create report and save as an attachment to the current record
    Dim myCurrentDir As String
    Dim myReportOutput As String
    Dim myMessage As String
  On Error GoTo ErrorHandler
    myCurrentDir = CurrentProject.Path & "\"
    myReportOutput = myCurrentDir & strReportName & ".pdf"
    If Dir(myReportOutput) <> "" Then ' the file already exists--delete it first.
        VBA.SetAttr myReportOutput, vbNormal ' remove any file attributes (e.g. read-only) that would block the kill command.
        VBA.Kill myReportOutput ' delete the file.
    End If
    DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, myReportOutput, , , , acExportQualityPrint
    SaveOpenReportAsPDF = myReportOutput
    Exit Function
ErrorHandler:
    MsgBox Error$
End Function

Public Sub CloseAllReports()
  Dim rpt As Access.Report
  For Each rpt In Application.Reports
    DoCmd.Close acReport, rpt.Name
  Next rpt
End Sub 

RE: Yearly calendar for attendance

(OP)
Majp, your correct the Outlook automation worked with no troubles except the saved file in the curent directory isnt being deleted when the report closes so I created a public function DeleteOpenReportAsPDF and called it after:

CODE

With objEmail
        '.To = strgTo
        .Subject = strSubject
        .Body = strMessageText
        .Attachments.Add AttachmentName
        .Display
    End With
    Call DeleteOpenReportAsPDF 'Deletes the saved .pdf (NOTE: Report has to be popup and modal) 
   CloseAllReports 'Close
Exit_Here:
    Set objOutlook = Nothing
    Exit Function 

But the thing is I had to make my reports modal/yes or I get an error 2476: You entered an expression that requires a report to be the active window. Unless you have anyother way to delete without making reports modal then this works.....

CODE

Public Function DeleteOpenReportAsPDF()
'==================================================================================================
'//Delete the saved .pdf (NOTE: Report has to be popup and modal)
'==================================================================================================

    Dim strReportName As String
    Dim myCurrentDir As String
    Dim myReportOutput As String
    
    strReportName = Screen.ActiveReport.Name
    
    myCurrentDir = CurrentProject.Path & "\"
    myReportOutput = myCurrentDir & strReportName & ".pdf"
    MsgBox myReportOutput
    If Dir(myReportOutput) <> "" Then ' the file already exists--delete it
        VBA.SetAttr myReportOutput, vbNormal ' remove any file attributes (e.g. read-only) that would block the kill command.
        VBA.Kill myReportOutput ' delete the file.
        End If
End Function 

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

When you call the function savereportaspdf that function returns the full path and name tou your calling function. So you can just kill attachmentname in your emailattachmentaspdf function. If you want a stand alone function then pass in the the file name and path "attachmentname" since you have that information. You want to always avoid using active report,form,control whenever you can for this reason. The focus is no longer on a report therefore no active report.

RE: Yearly calendar for attendance

CODE

Public Function DeleteSavedReport(FileName as string)
'==================================================================================================
'//Delete the saved .pdf, Filename is complete path and file name
'==================================================================================================
 'Add some error handling here 
 If Dir(FileName) <> "" Then ' the file already exists--delete it
        VBA.SetAttr FileName, vbNormal ' remove any file attributes (e.g. read-only) that would block the kill command.
        VBA.Kill FileName ' delete the file.
  End If
End Function 

So from your main program you simply would call it like
DeleteSavedReport AttachmentName
And you can reuse this to delete any file.
However it could simply be one line of code in your main routine
vba.kill attachmentname

RE: Yearly calendar for attendance

(OP)
Majp, that worked perfectly.... Thanks!

Majp, I have another DB I have been using for 5 months now an I was just informed that its giving incorrect calculations due to hrsWorked. If you have time can you look at the posting I made in:

TEK-TIPS Microsoft: Access Queries And JET SQL


Thanks!

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

Love the database! How difficult would it be to change the week to begin with Sunday?

RE: Yearly calendar for attendance

Extremely easy. The getOffset function takes in the first day of the week as an argument. If you see the code it is passed in vbSaturday, change that to vbSunday and change your labels.

RE: Yearly calendar for attendance

(OP)
Wow MajP your right that was easy...The (vbSaturday) is only found once in the modules:

mod_FillHolidays
mod_FillMonthLabels
mod_FillTextBoxes

And I replaced the weeks names to Sun - Sat in:

subFormMonth
rpt_YearViewCal

In all it took a whole 5 minutes.... I was woundering how this was done but I like the other way! Great question jgesman14.....

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

That worked perfectly! And so easy to do! Ok so now one final question that's probably going to be a little more in depth. Is 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? Currently I use Outlooks week view in the calendar for a quick view but I am hoping to incorporate it in the database.

RE: Yearly calendar for attendance

Sure, but that would take some definite work. The concepts are here, but you would need separate functions. Some ways it would be easy. Assume you pass a function a day 13 May 2014. Pretty easy to get first day of the week from that. Just move until you get sunday or use a little math using the dayofweek function. Then it would be easy to fill the remaining lables. You would fill it much the same way you fill the month view only using a different query.

RE: Yearly calendar for attendance

I think that may be to advanced for me! LOL

RE: Yearly calendar for attendance

(OP)
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.....

RE: Yearly calendar for attendance

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

RE: Yearly calendar for attendance

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

Quote:

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

RE: Yearly calendar for attendance

(OP)
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.....

RE: Yearly calendar for attendance

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

RE: Yearly calendar for attendance



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

CODE --> Sql

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

CODE --> SQL

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


The form code is pretty simple

CODE

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

be careful of the spam.

RE: Yearly calendar for attendance

(OP)
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.....

RE: Yearly calendar for attendance

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

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

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.

RE: Yearly calendar for attendance

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!

RE: Yearly calendar for attendance

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.

RE: Yearly calendar for attendance

(OP)
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.....

RE: Yearly calendar for attendance

Yes I think I will live without the color! LOL

RE: Yearly calendar for attendance

(OP)
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.....

RE: Yearly calendar for attendance

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.

Quote:


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

CODE

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 

RE: Yearly calendar for attendance

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?

RE: Yearly calendar for attendance

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

CODE

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.

RE: Yearly calendar for attendance

(OP)
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...

CODE --> VBA

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:

CODE --> VBA

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

RE: Yearly calendar for attendance

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.

RE: Yearly calendar for attendance

(OP)
Majp, after placing the subform on my frm_YearCalendar and linking it to the supervisors combo box like you said I used:

LoadGrid Forms!frm_YearCalendar.cboSupervisor

and then I open my frm_YearCalendar and I get an error '94' invalid use of null. and I see why but how do I fix it? Obviously when I open the form my supervisor combo box is going to be null and every time I change supervisors it will so how do I get it to give no information untill supervisor is selected?

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

You can use NZ function to change null to 0 or use an if then to do nothing if is null

RE: Yearly calendar for attendance

(OP)
Yes that worked I used;

LoadGrid Nz(Me.Parent!cboSupervisor, 0)

and I no longer recieve the error message now I have to toss in a requery for the weekView subform in the after update of the supervisors combo box.... One more question, is there a way we can use =gridClick() in the on click event of the text boxes to open the frm_CalendarInputBox like we did for the calendar? Just asking no bigie if not....


Thanks Again!

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

No but you can do the same technique and build a new function "=WeekViewClick()".
The function can be similar in how it calls the form. The only difference is how you get the absence date and employeeid. In fact it is much easier. Just copy the grid click. So copy the gridclick and paste it and rename it WeekViewClick. You need to get the date and employeeID differently. In gridclick that came from the main form.

I will give you a hint. In the columns of the form use the tag property. Tag them 0 through 6. Get the tag, and the FirstDayOfWeek, which I conveniently made a public property of the form. Now you should be able to determine the selected date (hint, add). Getting the employeeID will be tough. You can get the employee name by using the split function (with :) to get the name in last,First. You can split it again with (,) to get the last name and first name. Then you can use a dlookup to get the employeeID. Then the code remains the same.

I

RE: Yearly calendar for attendance

(OP)
Ok, I will try or atempt to try "=WeekViewClick()" after I get this requery working. I have tried everything and googled requerying diferent ways for the subform and it just wont requery. I did read and it does work on requerying the weekView subform and here is what the guy explained to the person he was helping.

Quote (Web)

There is a bug in access that will not requery your subform. If you try to just do something like subForm.SourceObject=subForm.sourceObject, it kicks out an error. However the compiler will allow you to set a SourceObject to a variable and then back. The string is the least memory intensive variable that will hold a sourceobject(a variant will do as well, but will use more memory).

It is a very strange solution to a very unusual problem. But with microsoft finding strange solutions to bugs, is a common place

CODE --> VBA

Dim temp As String
temp = ""
temp = subFormWeekViewGrid.SourceObject
subFormWeekViewGrid.SourceObject = temp 

Now why is this working and a requery wont? Ideas?

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

I think you are over thinking it. Let the subform do the work.

So this assumes that you are using the form as a subform and it is dependant on a supervisor. The supervisor id is not loaded when the parent opens. You have to pick it from a combo. So use the combos afterupdate.

Parent Form

CODE

Private Sub cboSupervisor_AfterUpdate()
  If IsNumeric(Me.cboSupervisor) Then
    Me.frmWeekView.Form.UpdateCalendar
  End If
End Sub 

When the you change the supervisor, tell the subform to update. (Could also pass in a new date to have it move to that date using the SetStartingWeek property.)

now you could pass in the supervisor from main to sub, or have the subform get the supervisor from the main. Here is the latter.

CODE

Public Sub UpdateCalendar()
  SetLabels
  'User must add Code to get supervisor ID. This assumes never a supervisor 0
  LoadGrid nz(Me.Parent.cboSupervisor,0)
  Me.Requery
End Sub 

Also that quote you posted looks like garbage to me. I know of no such bug (maybe in access 95).

RE: Yearly calendar for attendance

That quote is stupid the more I read it. It is like saying "there is a bug with my screwdriver because I cannot use it to hammer a nail. But if you flip it over and use the handle it works." In other words just because they are doing something wrong does not make it a bug.

To test this I pulled out the requery from the sub form to require the main form to force the requery.


CODE

Private Sub cboSupervisor_AfterUpdate()
  If IsNumeric(Me.cboSupervisor) Then
    Me.frmWeekView.Form.UpdateCalendar
    Me.frmWeekView.Form.Requery
  End If
End Sub 
No problem. So if done the proper way (using a hammer to drive a nail) it works no problem.

Changing the source object, is used to change the object (form) within a subform control. In certain cases it might trigger it to requery. The reason the temp variable works is that the source object goes out of scope and basically reloads.
so in my case
me.frmWeekView.sourceobject = me.frmweekview.sourceobject
does not work, but would not expect it to.
Do not forget
me.nameOfSubformControl.Form to return a reference to an object inside a subform control

RE: Yearly calendar for attendance

I will let you finish the weekViewClick. But here is a helper function for that function. You have to get the employeeID.

I forgot the string will have all the tags. So splitting it up and getting the first and last name takes some manipulation. Replace and Split functions are your friend for this.

Look at the remarks to see how each line of code narrows it down
everything will be in this form

CODE -->

'<div><font color=white style='BACKGROUND-COLOR:#0000FF'>PD: Cat, Bob</font></div> 

CODE

Public Function getEmpIDFromString(strVal As String) As String
  Dim aVals() As String
  Dim lName As String
  Dim fName As String
  Dim empID As Long
  Dim strWhere
  '<div><font color=white style='BACKGROUND-COLOR:#0000FF'>PD: Cat, Bob</font></div>
  strVal = Replace(strVal, "<", ">")
  '>div>>font color=white style='BACKGROUND-COLOR:#0000FF'>PD: Cat, Bob>/font>>/div>
  aVals = Split(strVal, ">")
  strVal = aVals(4)
  'PD: Cat, Bob
  aVals = Split(strVal, ":")
  strVal = aVals(1)
  'Cat,Bob
  aVals = Split(strVal, ",")
  lName = aVals(0)
  lName = "'" & Trim(lName) & "'"
  'Cat
  fName = aVals(1)
  fName = "'" & Trim(fName) & "'"
  'Bob
  strWhere = "EmpFName = " & fName & " AND EmpLName = " & lName
  'debug.print  strWhere
  empID = DLookup("EmployeeID", "tblUEmployees", strWhere)
  getEmpIDFromString = empID
End Function 

RE: Yearly calendar for attendance

(OP)
Thanks Majp, your requery works perfect! As for the weekViewClick() I have no clue and its way out of my scope.... lol, I looked at the getEmpIDFromString and I have no clue what to even do with that.

Thanks,
Soggy....

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

C'mon, you should be able to do it. Look at the forest and not the trees. The gridClick function does 90% already.
So here is the gridClick function we have already.

CODE

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 
So what does it do? It figures out the selected date from the controls and figures out the employeeID from the combo.
So the goal of this function is to get the selected date and employeeid so that you can pop open the form to edit it. That is the bottom line.

CODE

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

With the new form getting the selected date is easier than in the year view. You know the 1st day of the week because you calculated that already to load the form. The employee ID is hard to get because it is not stored anywhere on the form. But you have a string like with a bunch of tags but also something like "PD: Cat, Bob". So if we can get the first name and last name from that string we can find the employeeID.

So I showed you how to get the employeeID

CODE

dim ctrl as access.control
dim EmpID as long
dim strVal as string 'value in the textbox
dim selectedDate as date

'get the string value from the control that you double click
set ctrl = me.form.activecontrol
strVal = nz(ctrl.value,"") 
So now you have the value from the textbox. It will either be empty and thus I converted it to an empty string using Nz, or it is going to look something like

Quote:

'<div><font color=white style='BACKGROUND-COLOR:#0000FF'>PD: Cat, Bob</font></div>

If it is "" then you will want to prompt if they want to add a new record. I will let you code that. Else you want to find the employeeID

CODE

empID = getEmpIDFromString(strVal) 
Although the code looks complicated all it does is take that string and chops it up until you end up with Bob and Cat.
Now the only thing you need is the date. I said to tag the controls from 0 to 6. And the first day of the week is known

CODE

selectedDate = me.FirstDayOfWeek + cint(ctrl.tag) 

So now after clicking on the control you have the selected date and an employeeid. Or if it is an empty day you know that there is no record for that date and you can prompt them to add new.

So from the original grid click you had these variables

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

Now you only need

Dim ctl As Access.Control
Dim selectedDate As Date
Dim strVal as string
Dim empID As Long
Dim strWhere As String '============Added

See if you can put it all together.

RE: Yearly calendar for attendance

MajP,

Just wanted to say thanks for sticking this one out, and going into such detail explaining the steps. Should help the person who needs it now, as well as folks later.

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

RE: Yearly calendar for attendance

(OP)
Majp, I have been experencing some unusual errors with access so I had t reinstall access but after the install Im still stuck with the same Run-Time error as I was before. I am getting a Run-time error ‘13’: Type mismatch when I select the month (December) and december only to add a absence. Debug takes me to;

CODE --> VBA

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

Then after that nothing works correctly I have to close and reopen. Another thing thats weird is if I dont click debug and click end then the window comes up again so it must come up twice then changing employees gives a Run-Time error '91' Object varable or With block varable not set.

Thoughts?

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

CODE

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

RE: Yearly calendar for attendance

With out looking at this, I cannot remember how the month is passed in, but check from where it is being passed.

CODE -->

Public Function getIntMonthFromString(strMonth As String) As Integer
'Assume Jan, Feb..Dec
  'my guess the string is corrupted, maybe with a space
  strMonth = trim(strMonth)
  debug.print strMonth
  'What is passed in?
  getIntMonthFromString = Month("1/" & strMonth & "/2013")
End Function 

if that does not work just roll your own function

CODE -->

Public Function getIntMonthFromString(strMonth As String) As Integer
'Assume Jan, Feb..Dec
  select case strMonth
    case "Jan"
      getInMonthFromString = 1
    case "Feb"
      getIntMonthFromString = 2
    ....
    case "Dec"
      getIntMonthFromString = 12
    case else
       Msgbox "Your Passed " & strMonth
   end case
End Function 

Bottom line is you are passing in Jan, Feb,...Dec and need to return 1,2...12

RE: Yearly calendar for attendance

Quote:

Then after that nothing works correctly I have to close and reopen. Another thing thats weird is if I dont click debug and click end then the window comes up again so it must come up twice then changing employees gives a Run-Time error '91' Object varable or With block varable not setAlso. I do not think there is a lot of error checking.
So start adding error checking to the application. This thing is pretty complicated for vba, lots of places to get errors.

RE: Yearly calendar for attendance

(OP)
Majp, I tried the strMonth = trim(strMonth)and the print came up with "December" and you wrote im wanting a numberic return like 1,2....12 corect? Wounder why something changed I never did anything to change it.

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

No, that is fine. You are passing in a month that is a string, like January, February.. My guess was that you where not passing in a good month as a string. The function takes that input concatenates it to make a date, then runs the month function on it which should return 12. So try this.

CODE -->

Public Function getIntMonthFromString(strMonth As String) As Integer
  dim strDate as string
  dim intMonth as integer
  strDate = "1/" & strMonth & "/2013"
  debug.print "strDate: " & strDate
  intMonth = Month(strDate)
  debug.print "intMonth: " & intMonth
  getIntMonthFromString = intMonth
End Function 

So what do the debugs print? Trying to determine if the string is bad, or the function.

RE: Yearly calendar for attendance

(OP)
Majp, I ran the VBA and in the the immediate window:

strDate: 1/Decembember/2013

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

Is that a typo or is the month passed actually spelled wrong? If it is actually spelled wrong then that is the problem, and that would make sense. Find from where you pass the month. Is it from a combo, tag property, label? I do not remember. Make sure you spell it correctly.

RE: Yearly calendar for attendance

(OP)
Good catch.... I didn't even notice that! Nope that was a copy and Paste from the immediate window.

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

(OP)
Ok, I found the (2) spots that was causing the problems thanks to Majp's catch thumbsup2....

1st was in rpt_YearView Form_Load() Event

CODE --> VBA

'sFrmDec.LblMonth.Caption = "Decembember" Fixed
    sFrmDec.LblMonth.Caption = "December" 

2nd was in frm_YearCalendar Form_Load() Event

CODE --> VBA

'sFrmDec.LblMonth.Caption = "Decembember" Fixed
    sFrmDec.LblMonth.Caption = "December" 


Thanks Again....

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

I have been searching for an attendance calendar like this! Was there a final link to the finished product? It has been several years (5) since I last used Access and I have spent several hours going through this thread and getting familiar with the coding again and into the right 'mindset'. Great collaboration! Thanks, STef

RE: Yearly calendar for attendance

Here is the last version I had. There are a lot of bells and whistles in this which will make it somewhat complicated. Example: the ability to have multiple colors in the date.

https://drive.google.com/file/d/0B-RIQEKNzmfVYzFmU...

If you want to try to implement this and have questions, I would recommend starting a new thread. Then in the new thread and in this thread, put a cross reference. You do this by typing in the thread number in the format "ThreadXXX-XXXXXX" . Like:

Here is how to go to a New Thread
thread703-1718634: call handling for seperate businesses

RE: Yearly calendar for attendance

BTW, the reason for a new thread is that this thread is so long and takes forever to scroll.

RE: Yearly calendar for attendance

Thank you MajP, I'm going to jump in and try not to sink! Will follow your suggestions.

RE: Yearly calendar for attendance

oxicottin or MajP, Is there another place that I can get a copy of the Week View Calendar that you put on One Drive to share? Maybe a Dropbox link? We just got the full MO 2010 except One Drive. Thanks for your help.

Stef

RE: Yearly calendar for attendance

(OP)
Give me a day or two to do some deleting and ill up you a copy of what im currenty using. Sorry for the delay...

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

(OP)

@Steve here is the version I'm using in 2007 but it works in all versions, I uploaded it to my MediaFire.... You will have to make it your own by removing some stuff... Enjoy! Admin password I put on the admin form but its "oxicottin" if you dont see it...

Enjoy!

HERE IS CURRENT VERSION 5.xx

Thanks,
SoggyCashew.....

RE: Yearly calendar for attendance

Thanks Oxicottin! I'll download today. Have to clean up the ones that you guys were using when building it. What a great program. I don't need the 'bought vacation' portion of it, and the way they figure vacation time here is 'different' but the bones are here and are exactly what I need. Stevie (aka Stephanie)...

RE: Yearly calendar for attendance

Oh! FANTASTIC. Thanks!!

RE: Yearly calendar for attendance

Oxicottin.... Sorry about this - I can't unlock it.

RE: Yearly calendar for attendance

Nevermind...... :-O

RE: Yearly calendar for attendance

Hi i'm new to the site, tried running Attendance Control Public v5.xx works great, what i have noticed that my Windows 8 regional settings is set as date format is dd/MM/yyyy (English Australia) when i enter an employee absence date the yearly view doesn't color in certain dates, but updates in the weekly view and when i change the Regional settings to (English US) the yearly view works fine. Please can you assist.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close