Yearly calendar for attendance 11

[oxicottin]

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

Thanks!

Thanks,
SoggyCashew.....

 

[MajP]

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.

 

[kjv1611]

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

 

[oxicottin]

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

 

[MajP]

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.

 

[MajP]

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.

 

[oxicottin]

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

 

[MajP]

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.

 

[oxicottin]

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

 

[MajP]

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.

 

[MajP]

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

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 =

 

[MajP]

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.

 

[oxicottin]

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

 

[MajP]

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

 

[oxicottin]

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

Thanks,
SoggyCashew.....

 

[MajP]

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.

 

[oxicottin]

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)

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

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 .

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

 

[oxicottin]

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

 

[MajP]

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

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.

 

[oxicottin]

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

 

[oxicottin]

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