By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,996 Members | 884 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,996 IT Pros & Developers. It's quick & easy.

Scheduling/Tracking Calendar found on bytes

P: 51
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()
Expand|Select|Wrap|Line Numbers
  1. "SELECT tblPatients.Last, tblPatients.First, tblPatients.Date, tblVisitType.Type, tblVisitType.Code, tblPatients.Time " & _
  2.          "FROM tblVisitType INNER JOIN tblPatients ON tblVisitType.TypeID = tblPatients.TypeID " & _
  3.          "WHERE tblPatients.Date Between " & lngFirstOfMonth & " And " & lngLastOfMonth & _
  4.          " ORDER BY tblPatients.Time, tblPatients.Last, tblPatients.First;"
Changed to:
Expand|Select|Wrap|Line Numbers
  1. "SELECT employeehours.employee, employeehours.leavedatefrom, employeehours.leavedateto,[types of leave].calinput, [types of leave].attendancecode, employeehours.sumofnumofhrs " & _
  2.          "FROM [types of leave] INNER JOIN employeehours ON [types of leave].attendancecode = employeehours.typeofleave " & _
  3.          "WHERE employeehours.leavedatefrom Between " & lngFirstOfMonth & " And " & lngLastOfMonth & _
  4.          " ORDER BY employeehours.leavedatefrom, employeehours.employee;"
Public Sub PopulateEventsList(ctlDayBlock As Control)
Expand|Select|Wrap|Line Numbers
  1.    "SELECT tblPatients.Patient_ID, tblPatients.Last, tblPatients.First, tblPatients.Date, tblPatients.Time, " & _
  2.           "tblVisitType.Type, tblVisitType.Code, tblPatients.Insurance FROM tblVisitType INNER JOIN tblPatients ON " & _
  3.           "tblVisitType.TypeID = tblPatients.TypeID " & _
  4.           "WHERE tblPatients.Date = " & ctlDayBlock.Tag & _
  5.           " ORDER BY tblPatients.Time, tblPatients.Last, tblPatients.First;"
Expand|Select|Wrap|Line Numbers
  1. "SELECT employeehours.employee, employeehours.leavedatefrom, employeehours.leavedateto " & _
  2.           "[types of leave].calinput, [types of leave].attendancecode, employeehours.sumofnumofhrs " & _
  3.            "FROM [types of leave] INNER JOIN employeehours ON [types of leave].attendancecode = = employeehours.typeofleave " & _
  4.           "WHERE employeehours.leavedatefrom = " & ctlDayBlock.Tag & _
  5.           " 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

Expand|Select|Wrap|Line Numbers
  1. Call LogErrors(Err.Number, Err.Description, "frmCalendar", "PopulateCalendar() Sub-Routine", "Called from Multiple Locations")
  2.     Resume Exit_PopulateCalendar
Changed to frmCalendarEMP to refer to the actual calendar:
Expand|Select|Wrap|Line Numbers
  1. Call LogErrors(Err.Number, Err.Description, "frmCalendarEMP", "PopulateCalendar() Sub-Routine", "Called from Multiple Locations")
  2.     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
Dec 14 '13 #1

✓ answered by 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.

Share this Question
Share on Google+
16 Replies

Expert 5K+
P: 8,619
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.
Dec 14 '13 #2

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

Thank you again,
Attached Files
File Type: zip (181.2 KB, 75 views)
Dec 14 '13 #3

Expert 5K+
P: 8,619
Just give me some time so that I can look at it for you.
Dec 14 '13 #4

Expert 5K+
P: 8,619
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.
Attached Files
File Type: zip (101.6 KB, 64 views)
Dec 15 '13 #5

P: 51
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.

Dec 15 '13 #6

Expert 5K+
P: 8,619
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.
Dec 15 '13 #7

P: 51
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?
Dec 16 '13 #8

Expert 5K+
P: 8,619
Sorry, have never tackled anything of this nature. Post what you have and we'll see what we can do.
Dec 17 '13 #9

P: 51
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,
Attached Files
File Type: zip Calendar_Osmosisgg_emp (312.1 KB, 57 views)
Dec 17 '13 #10

Expert 5K+
P: 8,619
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:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Report_Open(Cancel As Integer)
    2. Dim frm As Access.Form
    3. Dim ctl As Control
    5. Set frm = Forms![frmCalendar]
    7. DoCmd.Maximize
    9. With Me
    10.   ![lblMonthRpt].Caption = frm![lblMonth].Caption
    11.   ![Label1].Caption = frm![txtDayBlock01]
    12.   ![Label2].Caption = frm![txtDayBlock02]
    13.   ![Label3].Caption = frm![txtDayBlock03]
    14.   ![Label4].Caption = frm![txtDayBlock04]
    15.   ![Label5].Caption = frm![txtDayBlock05]
    16.   ![Label6].Caption = frm![txtDayBlock06]
    17.   ![Label7].Caption = frm![txtDayBlock07]
    18.   ![Label8].Caption = frm![txtDayBlock08]
    19.   ![Label9].Caption = frm![txtDayBlock09]
    20.   ![Label10].Caption = frm![txtDayBlock10]
    21.   ![Label11].Caption = frm![txtDayBlock11]
    22.   ![Label12].Caption = frm![txtDayBlock12]
    23.   ![Label13].Caption = frm![txtDayBlock13]
    24.   ![Label14].Caption = frm![txtDayBlock14]
    25.   ![Label15].Caption = frm![txtDayBlock15]
    26.   ![Label16].Caption = frm![txtDayBlock16]
    27.   ![Label17].Caption = frm![txtDayBlock17]
    28.   ![Label18].Caption = frm![txtDayBlock18]
    29.   ![Label19].Caption = frm![txtDayBlock19]
    30.   ![Label20].Caption = frm![txtDayBlock20]
    31.   ![Label21].Caption = frm![txtDayBlock21]
    32.   ![Label22].Caption = frm![txtDayBlock22]
    33.   ![Label23].Caption = frm![txtDayBlock23]
    34.   ![Label24].Caption = frm![txtDayBlock24]
    35.   ![Label25].Caption = frm![txtDayBlock25]
    36.   ![Label26].Caption = frm![txtDayBlock26]
    37.   ![Label27].Caption = frm![txtDayBlock27]
    38.   ![Label28].Caption = frm![txtDayBlock28]
    39.   ![Label29].Caption = frm![txtDayBlock29]
    40.   ![Label30].Caption = frm![txtDayBlock30]
    41.   ![Label31].Caption = frm![txtDayBlock31]
    42.   ![Label32].Caption = frm![txtDayBlock32]
    43.   ![Label33].Caption = frm![txtDayBlock33]
    44.   ![Label34].Caption = frm![txtDayBlock34]
    45.   ![Label35].Caption = frm![txtDayBlock35]
    46.   ![Label36].Caption = frm![txtDayBlock36]
    47.   ![Label37].Caption = frm![txtDayBlock37]
    48.   ![Label38].Caption = frm![txtDayBlock38]
    49.   ![Label39].Caption = frm![txtDayBlock39]
    50.   ![Label40].Caption = frm![txtDayBlock40]
    51.   ![Label41].Caption = frm![txtDayBlock41]
    52.   ![Label42].Caption = frm![txtDayBlock42]
    53. End With
    54. 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.
Attached Files
File Type: zip Calendar With Seperate Dates With (80.6 KB, 64 views)
Dec 18 '13 #11

P: 51
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 ;)
Attached Files
File Type: docx yrviewexample.docx (120.2 KB, 272 views)
Dec 20 '13 #12

Expert 5K+
P: 8,619
Sorry osmosisgg, but you are talking about a major undertaking involving a considerable amount of time.
Dec 20 '13 #13

P: 51
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.
Dec 22 '13 #14

Expert 5K+
P: 8,619
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.
Dec 24 '13 #15

P: 51
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT [employee hours].employee, [employee hours].TypeOfLeave, [employee hours].leavedatefrom, Year([leavedatefrom]) AS [Year], [employee hours].sumofnumofhrs
  2. FROM [Types of Leave] INNER JOIN [employee hours] ON [Types of Leave].AttendanceCode = [employee hours].TypeOfLeave
  3. GROUP BY [employee hours].employee, [employee hours].TypeOfLeave, [employee hours].leavedatefrom, [employee hours].sumofnumofhrs
  4. ORDER BY [employee hours].leavedatefrom;
Dec 29 '13 #16

Expert 5K+
P: 8,619
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.
Dec 29 '13 #17

Post your reply

Sign in to post your reply or Sign up for a free account.