Scheduling/Tracking Calendar found on bytes

[osmosisgg]

Hello All,

I've grabbed the db Patients from this website and am attempting to tweak it.

I took the tblPatients and made it into EmployeeHours. The fields are now: employee, typeofleave, sumofnumofhrs, leavedatefrom, leavedateto

I added types of leave (to get the attendance code like LA for annual leave)

I took the qryPatients and made it into qryEMP, so now I get the same info as the EmployeeHours with the addition of the attendance code.

Public Sub PopulateCalendar()
WAS:

"SELECT tblPatients.Last, tblPatients.First, tblPatients.Date, tblVisitType.Type, tblVisitType.Code, tblPatients.Time " & _
         "FROM tblVisitType INNER JOIN tblPatients ON tblVisitType.TypeID = tblPatients.TypeID " & _
         "WHERE tblPatients.Date Between " & lngFirstOfMonth & " And " & lngLastOfMonth & _
         " ORDER BY tblPatients.Time, tblPatients.Last, tblPatients.First;"

Changed to:

"SELECT employeehours.employee, employeehours.leavedatefrom, employeehours.leavedateto,[types of leave].calinput, [types of leave].attendancecode, employeehours.sumofnumofhrs " & _
         "FROM [types of leave] INNER JOIN employeehours ON [types of leave].attendancecode = employeehours.typeofleave " & _
         "WHERE employeehours.leavedatefrom Between " & lngFirstOfMonth & " And " & lngLastOfMonth & _
         " ORDER BY employeehours.leavedatefrom, employeehours.employee;"

Public Sub PopulateEventsList(ctlDayBlock As Control)
WAS:

   "SELECT tblPatients.Patient_ID, tblPatients.Last, tblPatients.First, tblPatients.Date, tblPatients.Time, " & _
          "tblVisitType.Type, tblVisitType.Code, tblPatients.Insurance FROM tblVisitType INNER JOIN tblPatients ON " & _
          "tblVisitType.TypeID = tblPatients.TypeID " & _
          "WHERE tblPatients.Date = " & ctlDayBlock.Tag & _
          " ORDER BY tblPatients.Time, tblPatients.Last, tblPatients.First;"

ChangedTo:

"SELECT employeehours.employee, employeehours.leavedatefrom, employeehours.leavedateto " & _
          "[types of leave].calinput, [types of leave].attendancecode, employeehours.sumofnumofhrs " & _
           "FROM [types of leave] INNER JOIN employeehours ON [types of leave].attendancecode = = employeehours.typeofleave " & _
          "WHERE employeehours.leavedatefrom = " & ctlDayBlock.Tag & _
          " ORDER BY employeehours.leavedatefrom, employeehours.employee;"

I might have to go review that last one. I want the calendar to show the employee, the attendance code and the numofhrs (how many hrs they took) Also, to show the data for all dates between the leavedatefrom through the leavedateto when folks take more than one day off.

ISSUE: I am getting a compile error: ambiguous name detected:Log errors

WAS:

Call LogErrors(Err.Number, Err.Description, "frmCalendar", "PopulateCalendar() Sub-Routine", "Called from Multiple Locations")
    Resume Exit_PopulateCalendar

Changed to frmCalendarEMP to refer to the actual calendar:

Call LogErrors(Err.Number, Err.Description, "frmCalendarEMP", "PopulateCalendar() Sub-Routine", "Called from Multiple Locations")
    Resume Exit_PopulateCalendar

I can't seem to get past this error. Once I am able to get past it, I may encounter others. At that point, I may need more assistance. I have tried searching the forum and now everything went greek on me.

If there is any other information needed, please let me know.
Please help, osmosisgg

[ADezii]

Your current SQL Statement will not reflect the Date Range that you need, namely [leavedatefrom] to [leavedateto], but only [leavedatefrom]. You will need the WHERE ... BETWEEN ... AND ... Construct in the Statement.

The Ambiguous name detected is more than likely a Syntax Error in the SQL Statement or a Field misnamed.

Sorry, did not see the modified SQL Statement. Play with it for awhile, and if you are still having problems send the DB void of any sensitive Data (or no Data at all, just the Structure) as an Attachment to a Post. When I get a chance I'll have a look at it. There are simply so many areas to go wrong with this Database, and trying to figure out where the problem lies is sometimes vary difficult, at least for me.

[osmosisgg]

Thank you for responding. I am not having much luck. The zip file is attached.

Thank you again,
osmosisgg

[ADezii]

Just give me some time so that I can look at it for you.

[ADezii]

To be perfectly honest with you, Osmosisgg, there were so many Syntax/Logic Errors in the Database that you sent me that I had to essentially start from scratch using your Structure. The Database that I am attaching now should be more than adequate in placing you in the right direction on this Project.

[osmosisgg]

WOW! This is awesome! I do have a few questions. Was it all my syntax/logic that was in error? I recall only changing what I mentioned in the first post. I do see where I didn't get the relationship correct on the query and the strqrys. Thank you so much again. I only have to resize the form and it's ready.

Osmosisgg

[ADezii]

Glad it worked out for you. BTW, the Ambiguous name detected Error was because you had the Public LogErrors() Sub-Routine listed in two different Modules, which is a NO-NO.

[osmosisgg]

AH, I think it was because I was trying to edit one and keep the other as reference-just in case I really threw a whammy on it. I really appreciate your help. The next step I would like to try is modifying a code I found from somewhere else. It makes a report by employee: shows as an annual calendar, inserts the typeofleave code, and highlights the days according to the leave type based on a color coded scheme.
Have you tackled something like this yet?

[ADezii]

Sorry, have never tackled anything of this nature. Post what you have and we'll see what we can do.

[osmosisgg]

I made some notes in the modules since I am unsure. This is the biggest project I have ever attempted or so I feel - well, I felt this same way when I first touched access LOL!

Thanks for taking a look. Hopefully something can be figured out.

Thanks again,
osmosisgg

[ADezii]

I started from scratch and created a Calendar Report derived from the Calendar itself. Here is a brief explanation:

1. Since the Calendar (frmCalendar) has no Record Source and subsequently none of its Controls are bound, I figured that the only method to generate a Report is from frmCalendar itself when it is Opened.
2. I created rptCalendar which mirrors frmCalendar in that while frmCalendar has 42 Text Boxes sequentially numbered txtDayBlock01..txtDayBlock42, I created 42 sequentially numbered Labels numbered Label1...Label42. A Command Button on frmCalendar Opens rptCalendar reflecting current Dates Values in the Form.
3. In the Open() Event of the Report, the Values in each of the 42 Date Text Boxes on the Form are simply copied to their counterparts in rptCalendar (frmCalendar![txtDayBlock01] ==> rptCalendar![Label1]...frmCalendar![txtDayBlock42] ==> rptCalendar![Label42].
4. All Code in the Report is contained in its Open() Event which performs the above-listed function. Below, find this Code:
   

   Private Sub Report_Open(Cancel As Integer)
   Dim frm As Access.Form
   Dim ctl As Control
    
   Set frm = Forms![frmCalendar]
    
   DoCmd.Maximize
    
   With Me
     ![lblMonthRpt].Caption = frm![lblMonth].Caption
     ![Label1].Caption = frm![txtDayBlock01]
     ![Label2].Caption = frm![txtDayBlock02]
     ![Label3].Caption = frm![txtDayBlock03]
     ![Label4].Caption = frm![txtDayBlock04]
     ![Label5].Caption = frm![txtDayBlock05]
     ![Label6].Caption = frm![txtDayBlock06]
     ![Label7].Caption = frm![txtDayBlock07]
     ![Label8].Caption = frm![txtDayBlock08]
     ![Label9].Caption = frm![txtDayBlock09]
     ![Label10].Caption = frm![txtDayBlock10]
     ![Label11].Caption = frm![txtDayBlock11]
     ![Label12].Caption = frm![txtDayBlock12]
     ![Label13].Caption = frm![txtDayBlock13]
     ![Label14].Caption = frm![txtDayBlock14]
     ![Label15].Caption = frm![txtDayBlock15]
     ![Label16].Caption = frm![txtDayBlock16]
     ![Label17].Caption = frm![txtDayBlock17]
     ![Label18].Caption = frm![txtDayBlock18]
     ![Label19].Caption = frm![txtDayBlock19]
     ![Label20].Caption = frm![txtDayBlock20]
     ![Label21].Caption = frm![txtDayBlock21]
     ![Label22].Caption = frm![txtDayBlock22]
     ![Label23].Caption = frm![txtDayBlock23]
     ![Label24].Caption = frm![txtDayBlock24]
     ![Label25].Caption = frm![txtDayBlock25]
     ![Label26].Caption = frm![txtDayBlock26]
     ![Label27].Caption = frm![txtDayBlock27]
     ![Label28].Caption = frm![txtDayBlock28]
     ![Label29].Caption = frm![txtDayBlock29]
     ![Label30].Caption = frm![txtDayBlock30]
     ![Label31].Caption = frm![txtDayBlock31]
     ![Label32].Caption = frm![txtDayBlock32]
     ![Label33].Caption = frm![txtDayBlock33]
     ![Label34].Caption = frm![txtDayBlock34]
     ![Label35].Caption = frm![txtDayBlock35]
     ![Label36].Caption = frm![txtDayBlock36]
     ![Label37].Caption = frm![txtDayBlock37]
     ![Label38].Caption = frm![txtDayBlock38]
     ![Label39].Caption = frm![txtDayBlock39]
     ![Label40].Caption = frm![txtDayBlock40]
     ![Label41].Caption = frm![txtDayBlock41]
     ![Label42].Caption = frm![txtDayBlock42]
   End With
   End Sub
    

5. A Picture is worth a thousand words, especially in this case, so I am uploading the Demo as an Attachment for you to freely modify and incorporate into your Project. I'm sure that it can be improved upon, but I will leave that up to you.
6. There is, however, a big drawback to this approach, I'll let you figure that out yourself and correct it.

[osmosisgg]

Hi. Sorry for not responding sooner. I've been extremely busy with work and family. I've attached an example printout of a report that I was hoping could be done within the db you fixed for me. I saw the output from the last post and yeah- not exactly pretty.

vision as attached - by employee, year view, with type of leave (type of leave based on what I needed in the previous db you fixed) and highlights of type of leave within the date it was taken - yeah and of course the highlights would match the legend ;)

[ADezii]

Sorry osmosisgg, but you are talking about a major undertaking involving a considerable amount of time.

[osmosisgg]

Would it be any easier with a form that selects the individual employee with their leave types, dates and hours? Take the leave type and insert onto the report with the annual calendar based on the employee name that has already been selected? My issue is that I get so lost with all the vba scripts.

[ADezii]

You would have to modify the Basic SQL String in PopulateCalendar() to now include an additional Employee Name or ID Criteria, populate the Calendar, then generate the Report off it. I see no other way.

[osmosisgg]

Happy Holidays!
Here is the code for the query. If you could please point me in the right direction to populate the calendar into the report.

SELECT [employee hours].employee, [employee hours].TypeOfLeave, [employee hours].leavedatefrom, Year([leavedatefrom]) AS [Year], [employee hours].sumofnumofhrs
FROM [Types of Leave] INNER JOIN [employee hours] ON [Types of Leave].AttendanceCode = [employee hours].TypeOfLeave
GROUP BY [employee hours].employee, [employee hours].TypeOfLeave, [employee hours].leavedatefrom, [employee hours].sumofnumofhrs
ORDER BY [employee hours].leavedatefrom;
 

[ADezii]

When I get a chance I'll try to modify the Attachment that I sent you in Post# 11 but it will include the new SQL and be filtered for a specific Employee. The Report will still only display a specific Employee for the Active Month. If this will work for you I'll give it a try, if not this will be the end of the road for this Thread.