Populate Annual Calendar Report

oxicottin

oxicottin

Learning by pecking away....
Local time
Today, 14:30
Joined
Jun 26, 2007
Messages
888
Hello, I have an employee attendance database that is currently working but im not happy with the way im viewing an annual calendar. I found a Calendar Report here in the sample code that I think is much cleaner than the form/Report im using. How could I incorporate this sample into my database so I can view the employees annual attendance by the year and name that is selected that on my frmCalendar form. Im still learning VBA so its hard for me to understand whats going on in the module of the report DB.

Thanks!
 

Attachments

My current "learning" project on learning how to create object orientated code actually uses some ideas from the example you show above.

Basically I am trying to recreate the "outlook" calendar in an MS Access form. It's a part time project I am doing evenings and weekends, and my goal is to create some code that I can sell.

I would be interested in any links or other information you find in your quest if you would be good enough to post links here.
 
I have modified code for a yearly calendar report and included it with your attendance database. Print is an issue for the icalendar report can not generate only one copy of the page.
 

Attachments

You did a great job. Noticed you dropped your old yearly calendar. I need to fix my messages now.
 
firstly, thanks for allowing people like me who is slowly learning the Access Development ropes to see how these sorts of databases are programmed. Being able to open, view, modify and generally try to figure out what is actually happening in these examples helps me sooo much and I learn new things everytime I look at other peoples great work.

With regards to this Calendar file, firstly, I am getting a few issues with it that I can explain which I was hoping someone may be able to help me understand why;

1. when trying to enter an absent code into the 3rd Dec for an employee, it opens up the Attendance Entry Form for March 12, 2008, as 4th Dec opens up April 12, 2008, all the way through to Dec 13, where it now opens up 13 Dec, 2008.

It seems to be looking at the month rather than the date for the 1st 12 days in the month.

(I'm currently running Access 2007 also, just incase that may be the problem)

Any help in understanding these errors will be very helpful.

Thanks,
 
Following a few more hours looking at some of the great stuff in this site, I stumbled across some of my answers.

I have attached some of my mods, but there are a few things I am struggling with...

1. All months show 31 days in the calendar form, and create errors if you accidently try to add to a date that doesn't exist. (Can these phantom dates be removed from the calendar form?)

2. It would be good to get the option to add comments against an entry. Previous versions had this feature, but I am lost how it ties in to this version.

3. If you add a entry into the wrong date by accident, you can not delete it without going into the table and actually deleting the record. (This may create issues for users without access skills or database privellages.

4. I wonder how much work would be involved to generate a report that could provide a Department Attendance? This type of report could form the basics of many business related reports...


I have spent a few hours trying to figuring these things out, but after completly corupting the database 6 times and having to start from scratch, I have all but conceeded. Hoping someone has a better idea than I have.

Thanks,

Adam
 

Attachments

Adam I am new at this but I could suggest:

I have downloaded the version you posted and have no date problems. My system date is set at month day year so this may be your issue as your system is probably set to month day year. Lots on the internet how to fix this. I will have a look when time allows and get back to you.

On the post download Attendance Database w_rpt_YearView.zip as it has a comments section. Just delete what you do not want that is what I do.

I entered events and deleted them with no problem. If you try to enter into a square where this is no date you get an error. This issue may be tied into the system date settings
Since I am just new to access I will not be attempting this. Good luck on this.
 
Ok ADAM AUS,

Ok I got rid of the errors if you clicked into a box that had no date. Add these lines of code.

In the frmCalendar(s) Private Sub Form_Load add this code:

Code: 
For i = 1 To 37
       Me.Controls("Text" & i).Visible = False
       Me.Controls("Day" & i).Visible = False
       [B][COLOR="DarkGreen"]Me.Controls("DBox" & i).Visible = False ‘Added[/COLOR][/B]
       'Changes the calendars boxes to gray on the forms load and when Form_Load is called
       Me.Controls("DBox" & i).BackColor = -2147483633

In the module basCalendar(s) Function Cal add these TWO lines of code:

Code: 
For a = 1 To 37
        f("Day" & a + gOffset).Visible = False
        f("Text" & a + gOffset).Visible = False
       [B][COLOR="darkgreen"] f("DBox" & a + gOffset).Visible = False '******************************Added[/COLOR][/B]
        f("date" & a + gOffset) = Null
        f("day" & a + gOffset) = Null
     Next
     
     'Modify the Date Format so that it is equivilent
     'to the US Date Format since this function wants
     'to work in that format. This way, any Country
     'date format will work.
     dayOne = Format(DateValue(m & "/1/" & Y), "mm/dd/yyyy")
     
     workdate = dayOne
     
     '--------------------------------------------------------------
     'The code here is new. The Offset was not quite getting acquired
     'properly but I didn't like the method anyway. Now, the Offset is
     'gathered by the WeekDay name as indicated by the first 3 letters
     'within the WeekDay Labels at the top of Calendar. This way it
     'doesn't matter which day is indicated as the beginning of the week.
     'Normally, Sunday is the beginning of a calendar week but you have
     'decided to use Monday as the beginning. This function did not offset
     'properly if that was the case. Now it does. It doesn't matter which
     'is the first day of the week. If you put Thusday (or Thu) as the
     'first day of the week, this function will still offset properly.
     'This makes things very very flexable and far more accurate.
     WkDay = Format(dayOne, "dddd")
     For a = 1 To 7
       If LCase(Left$(f.Controls("WeekDayLabel" & a).Caption, 3)) = _
          LCase(Left$(WkDay, 3)) Then
          gOffset = a - 1: Exit For
       End If
     Next a
     '---------------------------------------------------------------
     
     'The old method to get Offset.
'    gOffset = Weekday(dayOne) - 1
     
     For a = 1 To LenMonth(dayOne)
        f("Day" & a + gOffset).Visible = True
        f("Text" & a + gOffset).Visible = True
       [B][COLOR="darkgreen"] f("DBox" & a + gOffset).Visible = True '*************************Added[/COLOR][/B]
        f("date" & a + gOffset) = workdate
        workdate = workdate + 1
        f("day" & a + gOffset) = a
     Next


Enjoy :p
 
Last edited:
Hi Thank you so much for the help...prrff.. I have copied the code exactly and it seems to be working, but .

If i do an entry on let say the 25 Dec 08 with no attendance reason
Click on the record again, and add a reason The reason will be available on the next click
However if i do it for 3 Dec 08 it will not keep the comment associated to the current inputID

We are in the UK so the default date setting for user machines is
dd mmm yy

On the bascalendar is changed the
Function SendToInputBox(mynum) setting to
dd/mm/yyyy

I have attached a copy what i am working with, sorry had to break the db apart quite big..

To Access the Form you have to click on FrmHome, and then on the command Employees and then Holidays.

I really appreciate any help
 

Attachments

Hello

Something Strange, As per above post. If in Input the dates via my form Inputbox, it does the entries correctly into the tblInput in format dd/mm/yy wich is brilliant, and stumble across the following
I did enties for OCT / Nov and Dec all with comments in

If i view the comments, in OCT they are only visble in my inputbox Attentance Reason Section from the 10th of that Month

For Nov they are only visible from the 11 and for December they are only visible from the 12th...

For Some reason it does not pick up the comments prior to the a certain date (depending on month). Although they are in my table.

cant i just possible associate my input box with my input id to ensure what ever the system date format would be it should not matter..

Any advise / assistance would be appreciated.
 
Natural sorry but I keep getting error after error and missing .dll file errors when trying to open the DB.

I have a question thats worth looking into. Would anyone know how to incorporate coloring the days background of a holiday and adding some text to the day like "X-Mas" or Labor Day" ect. if there isnt already an attendance text there. Im wanting not to save the text im wanting to just show the name of the holiday and color its background. Im gussing it would have to be added to the frmCalendar(s) Public Sub SetCalendar(). Any Ideas?

Thanks!
 
Hi Oxicotton

Terribly sorry for the inconvenience caused.. MM i created the db in Office 2003 xp sp3... Just did a test on a addtional pc to see if i migth have anything different but seems to run okay..

Please let me know
 
Here is the completed DB with all the fixes except for the Holidays that I wanted to highlight. Enjoy! :p
 

Attachments

Hi Oxicotton


I am sturggeling, and no luck on this side.
Can i ask you a big favour if you change youre system's date to DD/MM/YY
and on BasCalendar the Function SendToInputBox(mynum)

Change

MyDate = Format(f("Date" & mynum), "mm/dd/yyyy")

to

MyDate = Format(f("Date" & mynum), "dd/mm/yyyy")

and insert a vaction in dec the 3rd and one on the 4th, go back to the 3rd and insert some comment in the attendance reason and the same on the 4th.. then back to the 3rd , there would be no comment,

but if you do it on the 12dec. There would be a comment, but not the 11.

I have no idea. why it wont pick up the comment for all
The input date is correct etc

Thank you in advance
Regards
 
Natural I downloaded your calendar and changed my regional settings to short short date dd/MM/yyyy
long date d-MMM-yy
Your calendar worked fine everything showed on calendar form and all entries and comments were in the table.

Before these changes only the last half of a month would show in the calendar form using mm/dd/yyyy format.

Dave
 
I thought this employee accident DB would be appropriate since we are working with employee databases.... Enjoy :D

Password= soggycashew
 

Attachments

Oxicottin,

I have looked at your "Employee Attendance V1.5" db and it seems to be close to what I need to create a product release schedule. I would probably need to modify the code significantly, but it is very close.

I am not able to get to the code, though to see how you did it. How can I get to it?

Thanks in advance

mafhobb
 
You must log in or register to reply here.

Similar threads

B
  • Question Question
Auto-populate dependent table/form based on selection in main table/form.
Replies
1
Views
231
Pat Hartman
P
D
Calendar displaying autonumber from a combo box..
Replies
5
Views
358
Pat Hartman
P
Y
Disable certain dates in a calendar in a form
Replies
3
Views
476
youssefalaouia
Y
A
Solved VBA code for deleting Outlook 'Appointments' lying within a specified date range
Replies
8
Views
367
Gasman
Gasman
A
Calendar events but not in Outlook
Replies
13
Views
393
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom