The post below are all relating to the Code Archive demo by Peter Hibbs which can be found here
Please post any further questions/answers in this thread and not directly in the Code Archive
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Peter,

Great Calendar!

I was wondering if this can be adapted for 3 people (in my case salesman)? I am looking to be able to use this to pull daily, weekly monthly appointments for salesman in my co.

If you can point me in the right direction it would be greatly appreciated.

- Charles

This post has been edited by Alan_G: Apr 1 2012, 07:06 AM

Hi Charles,

Well, I am not quite sure exactly what you want but have a look at Post#31 in this earlier thread which may give you some ideas.

HTH

Peter Hibbs.

Peter,

The Post #31 is what I was looking for. thank you.

- Charles

Hi Charles,

Very good and good luck with your project. (IMG:style_emoticons/default/thumbup.gif)

Peter Hibbs.

Peter,

Unfortunatly I cannot open it. It looks like it's in Access 2010 format and I only have 2000 and 2007 versions. I would appreciate if you could convert it to either 2000 or 2007 format. I would rally like to take a look at it and implement it for my needs.

Thank you so much for all your help,

Charles

P.S.
I was looking at the very last attachment in the post (MyCalendarv2_1a.zip).

Hi Charles,

That version from Vick has some bugs in it and it is probably more complicated than you need so it may not help you much.

What exactly are you trying to do and what, if anything, have you done so far. It would probably be easier for you to start your amendments from scratch rather than try to modify someone else's version. My notes from that thread may help more than a demo.

Peter Hibbs.

Hi Peter,
I absolutely love your calendar, thanks for posting it.
I need to tweak it a little. Before I do I would like to ask you for some directions and any ideas you may have. I assume it would be easier for you to think of the best ways to implement what I need, as the builder of the demo. Maybe you will also find interest in how efforts are made to try to get your demo implemented.

Anyway, so here goes:
I would like to show something else in the Monthly calendar mode. Instead of showing the details of the appointments in each day, I would like to show only an aggregate of the appointments for that day. Specifically, I would like to show a count of the number of appointments already set for that day, but preferably separated in to two figures:
- The number of appointments already set before the hour 14:00
- The number of appointments already set, after the hour of 14:00.


Also, I would like to be able to set available dates for the calendar, so that appointments could be set only in days that will be predetermined as available. This means that there needs to be some interface that allows:
-Selection of recurring days, say for example, open the calendar to be available for appointments in every Monday between 8:00 and 13:00 for the next five months.
-Exceptions, for example, except for Monday, March 5th 2012 between the hours of 10:00 to 12:00.
-Open single non-recurring days, for example, open next Tuesday for appointments between the hours 15:30 and 17:30
-Perhaps also allow for different intervals to be set for the different opened appointment slots above.

I noticed that you already addressed the issue of having multiple calendars for different people, in the accompanying pdf file you provided. How about different calendars for different locations, like rooms for example, or for particular appointment types, like a particular medical examination for example.

In terms of the color scheme, I would like the day-boxes to be colored differently in a way that will designate:

- Which day is “open” for scheduling, in other words has available slots for new appointments. Preferably each day will show two different colors, one for morning and one for afternoon, showing if they are available for new appointments.
- Another color that will show which day was open, but now already full with all slots already taken.
- Another color that will show days that are not available at all for this calendar, so that they were not ‘opened’ to begin with.

I assume this could be done either with conditional formatting (with the use of functions instead of directly writing the conditions in the conditional formatting form), or with the ole controls that you suggested.


I know this is a handful, but if you have just a few pointers or any thoughts that I could start with, that would be great.

Thanks again so much
Gilad

Is there a version for Datasheet view of the subform.
Its a drag going through all the fields just to get to the next row.

Hi Gilad,

See my comments in blue below.

I absolutely love your calendar, thanks for posting it. Thanks very much.
I need to tweak it a little. Before I do I would like to ask you for some directions and any ideas you may have. I assume it would be easier for you to think of the best ways to implement what I need, as the builder of the demo. Maybe you will also find interest in how efforts are made to try to get your demo implemented. OK.

Anyway, so here goes:
I would like to show something else in the Monthly calendar mode. Instead of showing the details of the appointments in each day, I would like to show only an aggregate of the appointments for that day. Specifically, I would like to show a count of the number of appointments already set for that day, but preferably separated in to two figures:
- The number of appointments already set before the hour 14:00
- The number of appointments already set, after the hour of 14:00.
This one is pretty straightforward, you first of all need to create a query which returns a count of the number of appointments for mornings and afternoons and then use that query in the VBA code instead of the Appointments table itself. Assuming you are using the same fields as the original demo your query would look something like this :-

SELECT Count(tblAppointments.ApptID) AS ApptCount, DateValue([ApptStart]) AS Start, DateValue([ApptEnd]) AS [End], IIf(TimeValue([ApptStart])<"14:00:00","AM = ","PM = ") AS AMPM
FROM tblAppointments
GROUP BY DateValue([ApptStart]), DateValue([ApptEnd]), IIf(TimeValue([ApptStart])<"14:00:00","AM = ","PM = ");

and would be called qryMonthAppts (or whatever you prefer).
Then you need to change the VBA code slightly in the ShowMonthAppts procedure, the relevant lines are shown below. Note that I have only tested this quickly, you may find some obscure bugs if you decide to use this code yourself.



Also, I would like to be able to set available dates for the calendar, so that appointments could be set only in days that will be predetermined as available. This means that there needs to be some interface that allows:
-Selection of recurring days, say for example, open the calendar to be available for appointments in every Monday between 8:00 and 13:00 for the next five months.
-Exceptions, for example, except for Monday, March 5th 2012 between the hours of 10:00 to 12:00.
-Open single non-recurring days, for example, open next Tuesday for appointments between the hours 15:30 and 17:30
-Perhaps also allow for different intervals to be set for the different opened appointment slots above.
I don't think there is any easy way to restrict access to any specific dates on the calendar form itself but what you can do is apply any restrictions on the pop up form (frmCalendarAppt) when you attempt to set up a new appointment. I guess you could have another table which holds a list of dates which are not available or are available (or whatever) and then when the user opens the appointment schedule form some code in the Open event of the form could check the second table and check the selected date against the dates in the table and then show an appropriate message.

I noticed that you already addressed the issue of having multiple calendars for different people, in the accompanying pdf file you provided. How about different calendars for different locations, like rooms for example, or for particular appointment types, like a particular medical examination for example.
Well, basically you would do this in the same way as you would for different employees, etc. You would need to add a field to the Appointments table which would designate which room was to be used for that appointment and then on the calendar forms you would need to have some way of selecting which room you wanted to display, perhaps with a Combo or List Box control, and then pass the ID of that room to the VBA procedures so that the code would only show appointment records that match that ID. On the pop up appointment schedule form you would also need to add a control which listed all the rooms so that the users could select which room was to be used whenever they create or amend an appointment.

In terms of the color scheme, I would like the day-boxes to be colored differently in a way that will designate:

- Which day is “open” for scheduling, in other words has available slots for new appointments. Preferably each day will show two different colors, one for morning and one for afternoon, showing if they are available for new appointments. The only way you could have two different colors for the same day would be to add 6 more rows to the monthly calendar so that you had two rows for each day, one for AM and one for PM. It could be done but it would mean a complete re-write of the VBA code in the procedure shown above, not a trivial task, I think.
- Another color that will show which day was open, but now already full with all slots already taken. It would probably be easy enough to determine if all slots are used or if there are still some free by using the count field in the code above. I assume you would know how many appointments you are allowed for each session (i.e. AM or PM) so you would only need to compare that figure with the numbers returned by the query. Changing the colors of the date cells is another matter though (see below). I suppose another option is to add a bit of text to the date cell if the AM and/or PM session is full to indicate if there any free slots or not.
- Another color that will show days that are not available at all for this calendar, so that they were not ‘opened’ to begin with.
I assume this could be done either with conditional formatting (with the use of functions instead of directly writing the conditions in the conditional formatting form), or with the ole controls that you suggested. This would not be possible using 'Conditional Formatting' because you only have a maximum of three colors and they are already used (unless you are using this with Access 2010 in which case it may be possible). As you say, you could use the OLE type fields to show multiple colors and that is covered in the supplied PDF document but again, this would need a fair bit of VBA code to implement.

Anyway, hope that helps a bit and good luck with the project.

Peter Hibbs.

Hi arnelgp,

Not sure I understand the question. I don't think the calendar will work in Datasheet mode and I cannot see that there is any advantage if it did.

I am also not sure what you mean about "going through all the fields just to get to the next row", perhaps you could explain in more detail.

Peter Hibbs.

Yes Mr.Hibb, on Weekly View, I would like to be able to use my down/up arrow key to move down/up one record but instead it hightligh moves right/left.
The navigation is not same as in Monthly View where I can use my up/down arrow keys to move around.

Expecting for your next revision.

Hi arnelgp,

OK, good point, I had not thought about that before.

It is easy enough to add that option though. Just open the form frmCalendarWeek in Design mode and add the code below to the form's On Key Down event and set the form's Key Preview property to 'Yes'. Actually, the exact same code is in the Key Down event of the form frmCalendarMonth so you could copy that to the weekly form instead. You may also want to set the form's Cycle event to 'Current Record' if you don't want the cursor to move to the next line each time that it gets to the end of the row.



I will include this option in the weekly calendar if I should ever update the demo in the UA Archive.

Peter Hibbs.

Thanks Mr.Hibbs.

Thanks so much Peter. I will give it a go.
Best wishes
Gilad