Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Code Archive _ Ms Outlook Style Calendar For Access Projects

Posted by: Peter Hibbs Jul 10 2011, 08:51 AM

The attached A2003 database file demonstrates a calendar form which is designed to simulate (as much as possible) the Calendar facilities in MS Outlook.
It can be used in three modes, Monthly, Weekly (see screen shots below) and Daily and allows users to enter appointments in any mode and to be able to easily switch between modes at any time.
It also has facilities to export appointments to or import appointments from MS Outlook, find scheduled appointments, print a monthly calendar, etc and it can be easily configured for different appointment time slot periods, different formats and so on.
A comprehensive PDF document file is included to show how to set up the system in an existing database project and how to configure the code for your different requirements.
It has been tested on Windows XP and Windows 7 and Access 2003/2007 (but not A2010).
If any bugs found please let me know and thanks to various UA members that have suggested amendments to version 1 which had been posted to this forum earlier.
Peter Hibbs.
(previous file download count: 879, replaced with Version3)
 Calendar_V3.zip ( 771.84K ): 3334

http://www.UtterAccess.com/forum/index.php?s=&showtopic=1969978&view=findpost&p=2314926 (in post #2 below)

This post has been edited by jleach: Sep 7 2012, 10:14 AM - Reason for edit: Replaced previous with Version3
Additionally edited by Cybercow: Dec 22 2012, 12:36 AM - Reason for edit: Added link to post #2 below for Access 2010 64 bit version

 

Posted by: dlathem Dec 17 2012, 05:10 PM

Hello again,
just wanted to leave a note for anyone that is using this on a 64-bit office 2010 system. I was running into an error "Please review and update Declare statements and then mark them with the PtrSafe attribute". I found that if you put PtrSafe right after the Declare statement it will eliminate this error.
And here's the 64 bit Access 2010 version tested and working! < (will work ONLY in the 64 bit version of Access 2010)

 64bit_2010_working_version.zip ( 874.42K ): 1525
 

Posted by: Peter Hibbs Dec 22 2012, 04:40 AM

Hi dlathem,
Thanks for the Calendar update for 64 bit Access, I am sure it will be useful for some users.
I haven't got this version myself but looking at your amendments it seems that it is only the API calls that need to be changed for use with the 64 bit version of Access. I am thinking that we could just add those declarations to the 32 bit version and then Rem them out so that anyone wanting to use the calendar with the 64 bit version could just un-Rem them or maybe there is some way for the start up code to detect which version of Access is being used and enable the appropriate declaration code automatically (or is it more complicated than that?). I will have to think about it some more.
If you have any thoughts on this then please let us know.
Peter Hibbs.

Posted by: DatabaseMN Jan 5 2015, 04:52 AM

Peter, I have worked with your calendar in the past and it is a very nice tool.

I have wondered about why each time I open it or switch views, there is a rapid series of flickering. Is this just my machine or has this come up before?

Have been meaning to ask over the past couple years, but finally got around to it.

Thanks again!!

Dana

Posted by: Peter Hibbs Jan 5 2015, 06:58 AM

Hi Dana and thanks,

Yes, it is a nuisance. I am pretty sure it is because of the Conditional Formatting on the date fields and I don't think there is any way of eliminating it and I have tried various methods (unless anyone else knows differently).

Peter Hibbs.

Posted by: jenotc Jan 6 2015, 05:26 PM

I get error message.

Could anyone help on that?

Error message says:

QUOTE
Syntax error in date in query expression `#2015.1.7 10:00`.


Thanks

 

Posted by: Peter Hibbs Jan 6 2015, 06:12 PM

Hi jenotc,

This is because your date format is different than the UK or US. Try the attached version below to see if that fixes the problem.

Peter Hibbs.

 Outlook_Style_Calendar_V3a.zip ( 234.19K ): 700
 

Posted by: EvilBert Mar 23 2015, 12:09 AM

Hi Peter,

Thanks again for posting this awesome calendar.

I have one question.

I've embedded the frmCalendarMain into another form (so now it's a subform), and everything works great except when clicking on day in the calendar. I get an error on; Call Forms("frmCalendarMain").CheckMode. Because my form structure is now "frmSwitchboard!frmCalendarMain" the reference doesn't work anymore.

I've tried changing the reference to point it to the right place, but not having any luck so far. Maybe I'm overlooking something obvious.

Can you advise how I need to change this to reflect that frmCalendarMain is now a subform in my frmSwitchboard form?

Thanks in advance.

Kind regards,
Michael.

Posted by: Peter Hibbs Mar 23 2015, 09:37 AM

Hi Michael,

Hmmm.. good question. Probably like you I tried changing the reference to the parent form by adding the name to the reference but I could not get it to work, maybe there is a limit on the number of forms a CALL function can be nested.

Anyway, the way round it is to move the CheckMode sub-routine to a Code Module (I used modCalendarCode) and then pass the main form object reference to the sub-routine so that the various commands in the sub-routine can make the required changes. Actually I think this is a better way of doing it as I never liked calling a form's sub-routine form another form.

For the calls from the main form (frmCalendarMain) you can just use the Me parameter and for the calls from the three sub-forms you can use Me.Parent. In the sub-routine itself you would simply replace the Me words with frm which is the alias used for the calling form. I have made the required mods in the demo file below so that you can see how it is done. To find the updates just search the VBA code for ### which I used to mark the changes made. One other change you may need to make is to make sure the Name property of the main sub-form (which was the original main form but is now a subform) is set to frmCalendarMain, otherwise the report title does not work (if you are using the report option).

HTH

Peter.

 Outlook_Style_Calendar_V3c.zip ( 165.91K ): 966
 

Posted by: robertocm Jan 8 2017, 01:26 PM

Dear Peter,

Many Thanks for your calendar and also for the detailed guide 'Outlook Calendar Demo Manual'

Let my just show my gratitude sharing a little 'exercise' of adapting some of your code to an excel week calendar connected to an Access table (instead of the original Access form).

I have used the attached files to try to learn something about date and time queries, crosstab queries and ADO
from my beginner perspective
don't expect any comparable tool

Kind Regards

 excel_week_calendar.zip ( 74.09K ): 348

Posted by: Peter Hibbs Jan 9 2017, 06:56 AM

Hi Robertocm,

OK, interesting idea, perhaps someone will find it useful.

Peter Hibbs.

Posted by: frm Apr 28 2018, 01:43 PM

Hello Mr. Peter Hibbs,

I saw his work and I found it excellent.

I wanted to ask if was foreseen add in the database the appointment recurrence management,

that is the repetition of the appointment:

Daily
Weekly
Monthly
Annual

as expected in Outlook 2007.

Posted by: Peter Hibbs Apr 29 2018, 02:16 AM

Hi frm,

I have added a 'recurrence' facility to this demo. Have a look at the new version in Post #7 http://www.UtterAccess.com/forum/index.php?showtopic=2034190&st=0&gopid=2576945&#entry2576945

Peter Hibbs.

Posted by: Peter Hibbs Apr 30 2018, 03:13 AM

Hi frm,

OK, try this version which may or may not work but I have an idea what the problem might be. We shall see!

Peter.

 Outlook_Style_Calendar_V3i.zip ( 342.46K ): 245
 

Posted by: frm May 3 2018, 01:29 PM

Hi,

I sent a post today on this Topic but I do not find it, as I do not find the other two pages of this topic either.

May I know, please, why?

Thank you

Posted by: doctor9 May 3 2018, 01:37 PM

frm,

Please check your New Messages. I have moved your discussion so it's in the appropriate forum. You can find your most recent posts by clicking on the "My Discussions" hyperlink in the upper right corner of the page.

Dennis
UA Editor

Posted by: frm May 3 2018, 02:25 PM

Hi Mr Dennis

Thanks for the explanation.

A question.
When the Topics are inserted in the appropriate forum: "My Discussions"?

Posted by: doctor9 May 3 2018, 04:01 PM

frm,

Yes, it's in the group of hyperlinks in the upper right corner. I've highlighted it in the attachment.

Hope this helps,

Dennis

 

Posted by: dulcmr7 Jun 26 2018, 08:30 AM

Hello Peter.
I am new here and basically signed up because of this great calendar example. Thank you very much for your work on this and making it available to the masses!
I have adapted your work to my project and all is working with the exception of 2 things (so far).

1) This is more of an annoyance than anything and I can easily work around it if needed. If I have the calendar on my primary screen everything works as it should, but if I have it on a second or third screen the date search button (right next to the "Monthly" button in the top right corner) doesn't work and gives an error "An expression you entered is the wrong data type for one of the arguments". I am guessing because it is trying to get the coordinate location, but it is getting confused with the multiple monitors. I am guessing either the Sub btnStartDate_Click or the Function FetchFormCoods needs to modified, but I am not sure how. Are you able to let me know what needs to be changed?

2) Because of a multi-user environment, I will have several instances where there are multiple appointments for the same time slots with each person's appointment on a separate line. I have been trying to figure out how to get the Month, Week, and Day calendars to each expand/grow if there are more appointments than space available so we can see everything. I realize the entire row will need to grow even if only one day has the extra rows, but that is acceptable. I have been changing several setting to Can Grow, but have not had luck with this yet. Any thoughts?

Thanks again!

Posted by: dulcmr7 Jun 26 2018, 01:34 PM

Peter,
While I'm at it, I've also had a request to bypass the weekends in the calendar view so that it basically just shows a work week. I am scouring through the code right now to see if I can interpret it enough to figure it out. I have my doubts I will be able to get it by myself, though.
Thanks for any help or insight you could provide.

Posted by: Peter Hibbs Jun 26 2018, 02:22 PM

Hi dulcmr7,

Thank you for your kind comments, they are much appreciated.

Regarding the positioning of the pop-up calendar form on multiple monitors, this is a problem. I don't have monitors like this so I can't really try to find a fix, although I am sure it must be possible. As you suggested, you probably need to add some offset values for the horizontal and vertical co-ordinates in the sub-routine to get the form to appear in the correct position but I don't know what these would be. I guess you could experiment with code in the btnStartDate_Click() event to see what happens. I would move the calendar to another screen and then add some extra twips to the x1 and y1 variables just to see if that will move the form but other than that I can't really help.

One possible option you could try is, if you are using Access 2007 or later, you could use the built-in Access pop-up calendar. If you make the btnStartDate control invisible, change the Enabled and Locked properties of the txtDate control to True and False respectively and make sure the Show Date Picker property is set to 'For dates', this might be acceptable. You would also need to add this line of code , CalendarUpdate to the AfterUpdate event of the txtDate control so that the calendar form updates when you choose a new date. You could probably also add some code to move the focus after the update to highlight the selected date, etc.

Since this calendar uses a Continuous type form for the dates, it is not possible to use the Can Grow parameter (that only works on reports) so the only thing you could do to show more appointments in each date cell is to make the controls taller on the sub-form which will make all the rows taller. I guess it depends on the size of your screens as to whether this will work OK.

To remove the Sunday and Saturday columns on the Monthly calendar would require a major change to the VBA code and would take a lot of effort. I suggest that you make those columns very narrow, perhaps just enough to show the day as Sun and Sat and then make the other days of the week wider. In this way you still have the display for the weekend dates, in case you ever needed them, but it would provide more space for the working days. Changing the control sizes will still be quite a bit of work but would be easier than rewriting a lot of code.

Sorry I can't be of more help but let us know how you get (especially if you solve the multiple screen problem as other users have mentioned the sam eissue to me).

Peter Hibbs.

Posted by: dulcmr7 Jun 26 2018, 06:57 PM

Thanks for the quick reply Peter and for your thoughts.
I will look at the btnStartDate_Click() code tomorrow to see if I can get the calendar pop-up to work correctly. I think you are correct, though, I could probably just use the Show Date Picker property that is now available and use the built-in options. That sounds a lot easier to me!

I'll have to give my dilemma with the form appearing correctly some more thought on how I want to handle that. Thanks for reminding about the Can Grow property not working in continuous forms.

I feel like I'm so close on the Weekday only thing. I was looking at that this afternoon.
Unless I'm misunderstanding something, I think what you propose would only hide the weekends, but data would still be written to the tables for those days. I am wanting to not even write anything to the tables for Saturday or Sunday.
Here is the code I have modified (in bold). This is in the Module modCalendarCode under the Sub ShowMonthAppts. I have added a variable called vOffset and if the new calendar entry lands on a weekend, it is supposed to offset that particular day and future ones by 1 or 2 days:

CODE
          vOffset = 0
            'Now copy appt info into each row in array
            Do                                                                  'do--
                vCol = Weekday(vDate, conFirstDay) - 1 + vOffset               'calc column No for current date
                vRow = (DateDiff("d", vFirstDate, vDate) \ 7)                   'calc row number for current date

               If vCol = 6 Then
                    vCol = 1
                    vRow = vRow + 1
                    vOffset = vOffset + 2
                ElseIf vCol = 0 And vOffset = 0 Then
                    vCol = vCol + 1
                    vOffset = 1
                End If
                
                If conMonthHide = 0 Then                                        'if current date <> current month then skip *** (hide appts for dates not in current month)
                    If vRow <= 6 Then
                        vArray(vCol, vRow) = vArray(vCol, vRow) _
                        & Nz(rst!Subject) & vbCrLf                                  'add appt data to array + CRLF
                    End If
                Else
                    If Month(vMonthStart) = Month(vDate) Then                   'if current date <> current month then skip *** (hide appts for dates not in current month)
                        vArray(vCol, vRow) = vArray(vCol, vRow) _
                        & Nz(rst!Subject) & vbCrLf                              'add appt data to array + CRLF
                    End If
                End If
                vDate = vDate + 1                                               'inc date ref
            Loop Until vDate = vDateStop                                        'until all cells filled
            rst.MoveNext
        Loop



It almost works. I think I know what's wrong, but I'm struggling to fix it. Maybe I'm taking the wrong approach altogether?
It actually does work for the actual month that the appointment takes place in. The weekends are blank and the rest of the appointment is moved out the 1 or 2 expected days. But if the appointment is in the "preview" or grayed out section for the next month (e.g. If the appointment is in the first weekend of March I can see it in the grayed out section at the bottom of February's calendar) then it gives me the warning that the "Subscript is out of range" (when viewing the February calendar in the previous example). I am guessing that is because I am moving days on the appointment forward that it is wanting to assign these values to the array beyond the set size of the array itself. I have been trying unsuccessfully to stop assigning values to the array within the Do loop, but no luck so far.
Sorry for the rambling. Hopefully something made sense.

Edit: I have tried setting conMonthHide=1 to hide the preview for the next month thinking that would solve my issue. It didn't.
My new theory is that I am having trouble with the modified code when the appointment spans over the end of the month in general (e.g. from 7/19/2018 - 8/7/2018).

Posted by: Peter Hibbs Jun 27 2018, 03:35 PM

Hi dulcmr7

Having looked at this again I think there is an easier way to do what you want regarding the requirement to only show the working week days. Basically you can just delete the Sunday and Saturday controls from the Month sub-form and change a few lines of code and this should work.

I have sent a demo version which shows the days Monday to Friday on the form (I haven't bothered to resize them but you can easily do that if you wish). Also, I haven't made any changes to the Weekly view so if you are using this as well then you will need to do something similar here as well. The Daily view should not need any changes.

So what happens is that the days of the week are shown as normal but in the pop-up Appointments form, you just need to prevent the users creating an appointment for a Sunday or a Saturday and that is pretty much it (unless there is some other factor that you have not told us about).

In the attached version I have marked any code changes or additions with ??? characters so you can search the VBA code for those to see the changes. The only change to the controls design you need to make is to the cmdDay button on the Month sub-form, it is a transparent button which runs along the top of the date cells (those colored green) so you will need to change the size of that button so that it covers the date cells for Monday to Friday. If you don't then you will probably show the wrong date when you click on a green date cell.

Anyway, try it out and let us know what you think.

Peter Hibbs.

 Outlook_Style_Calendar_V3n.zip ( 182.01K ): 116
 

Posted by: dulcmr7 Jul 2 2018, 07:32 AM

Hi Peter -
I just wanted to follow up and say THANK YOU for your suggestions and start of the new code. Your method was much better than how I was trying to manipulate it.
My only hangup was I was needing to calculate the number of working days an appointment was scheduled over. If it went multiple weeks, it is still putting data in for Saturday and Sunday. I just did a quick calculation in a query to remove those from the count and all is well in my world once again.

Thank you so much for your help and support!

Posted by: Peter Hibbs Jul 2 2018, 08:34 AM

Hi,

yw.gif

Peter.

Posted by: riti90 Sep 11 2018, 04:32 AM

Dear Peter,

I hope you are well,
I'm new to the forum and Access too, and I would like to ask if you could help me with this topic?
I'm trying to make it as a booking system for my company for booking Chamber times.
I've added a Combobox to filter it but I find it difficult to do so.



Could you please give me a hand with that.

And I also would like to ask if we can make it to stop overlapping the Chambers too.

Thank You in advance,
Margarit
 Outlook_Style_Calendar_V3_n_.zip ( 275.37K ): 39

Posted by: Peter Hibbs Sep 11 2018, 10:38 AM

Hi Margarit,

welcome2UA.gif

I would be happy to help you with this but before I start making a lot of changes to your version I have a couple of questions.

I have already made some modifications to the original version which does more or less what you are asking for which I have attached. I have used the name 'Category' for the extra field but you could change that to Chambers (or whatever) for your own database. Perhaps you could have a play with this version and see if it does everything you want.

I notice that on the database you posted you are using the version that allows for 'recurring appointments' which is not provided on the attached version, do you need this facility in your database (you haven't actually made any recurring appointments in your example)? If you do, then we will need to add that facility into your version which is bit more complicated.

You also want to 'stop overlapping appointments' which I guess means that you want to be able to schedule more than one appointment in the same time-slot! If so, then you should open the module modCalendarCode and change the constant called conMultiAppts at the top of code window from 0 to 1 (as explained in the User Manual). If this is not what you meant then please explain further.

Anyway, let me know your thoughts and we can go from there.

Peter.

P.S. As this post is not directly related to a problem with the original version you may find that the UtterAccess gnomes will move these posts to a separate thread.

 Outlook_Style_Calendar_V3m.zip ( 156.01K ): 49
 

Posted by: riti90 Sep 12 2018, 01:53 AM

Hi Peter,

Thank You so much, this is a great solution.
I was working yesterday on the first file and I ended up with something like this :

 Outlook_Style_Calendar_V3o.zip ( 447.31K ): 38


But Your one is much more completed.

I would like to ask if we can add the All Day event when booking a Chamber for example.
Because the most of the time they are booked by days, ans sometimes by hour.

Once again Thank You very much for your help,

Regards,
Margarit

Posted by: riti90 Sep 12 2018, 09:00 AM

Hi Again,

Sorry to bother again but I'm working on this calendar and trying to create a Yearly calendar.

If it's OK with you Could you please have a look at that one cause I'm stuck now and don't know how to proceed.


 Outlook_Style_Calendar_V3o.zip ( 764.99K ): 47


Thank You,
Margarit smile.gif

Posted by: Peter Hibbs Sep 12 2018, 01:26 PM

Hi Margarit,

WOW, it looks like you have put a lot of work into adding the Yearly calendar and it is quite impressive, the VBA code is all completely wrong but at least you have done the hard work with all the extra tabs, fields, etc.

You can't just copy the code for the Monthly view, change a few variables and then expect it to work for the Yearly view. For example, if you look at the dates you have for the Yearly view you can see that they all just follow on for each row of the form so by December they are about 9 days out. You have to reset each row after each month and start again from day 1. Having said that, there is a much easier way to do this - you already know the Date field names because they go from Day1Date to Day31Date and you know the row numbers because the row numbers are the same as the Month numbers (1 to 12) so you can create the individual field names from those variables and insert the dates from your yDate variable into the calculated field name. You just need to know the year so that you can stop the loop when the count reaches the following year. For example, the code below does just that (which you can test yourself if you want) :-

CODE
    yFirstDate = DateSerial(Year(yYearStart), 1, 1)                                 'calc first date on selected calendar DateSerial(Year(yYearStart), 1, 1)

'Calc cell dates from starting date and copy to table
    yDate = yFirstDate                                                              'vDate = first date on calendar
    CurrentDb.Execute "UPDATE tblYearData SET Day29Date = NULL WHERE RowNo = 2"     'clear date 29 Feb in case previous year was a leap year
    Do
        CurrentDb.Execute "UPDATE tblYearData SET Day" & Day(yDate) & "Date = #" _
        & Format(yDate, "yyyy/mm/dd") & "# WHERE RowNo = " & Month(yDate)           'insert current date into Day(nn)Date field
        yDate = yDate + 1
    Loop Until Year(yDate) = Year(yYearStart) + 1                                   'loop until yDate moves to the following year


However, the rest of the routine needs to be rewritten because it does not work how you have done it for all sorts of reasons. I will do that as it is quite an interesting project but it could take a while because I have other projects to attend to at this time. I propose to get the Yearly mode working first and I will upload it so that you can have a look and then if that is OK I will look at adding in the option to choose a Chamber as you requested initially. As a matter of interest, what is this database for, what are these chambers and will you ever want to add a Chamber 3 or 4 or whatever?

Anyway, I will get back to you when I can, just be patient but if you have any questions then feel free to post them here.

Peter.

Posted by: riti90 Sep 12 2018, 02:13 PM

Hi Peter,

I would like firs to thank you for your help and Thank You for getting back to me.

We use there chamber for some device testing we do, and we need to do device conditioning in those, so yes there are going to to more then 2 chambers added.
The one that you showed me with the Categories is just fine for me, the only think I'm on now is this yearly calendar.

I would be grateful if you can help me with that, whenever you've got some time.

Best Regards,
Margarit

Posted by: Peter Hibbs Sep 13 2018, 06:06 AM

Hi Margarit,

I have given some thought to this database and I am wondering if there is a better way to show the Yearly data.

With your current method you have very small fields for the data so you will only see part of one or maybe two records for any date and you will also need to scroll the form horizontally (and probably vertically) to see all the dates in the year.

My suggestion would be to show all the dates for the current year on the form (see screen shot below) and color the dates that have any data stored. This has the advantage that you can see all the dates together without having to scroll the form. In this example you can just click on a date to see the contents of the record in the List box and you can click on a record in the List box (if there is more than one) to see the data in the various fields in the Details box. Also you could use other colors to highlight certain dates if that would be useful in the database.

Anyway, let me know what you think. It is up to you to decide (it makes no difference to me, I will go along with whatever method you prefer).

Peter.

 

Posted by: riti90 Sep 13 2018, 06:45 AM

Hi Peter,

That one looks great, it's more clear than the other one.
we could go with that is it's OK with you. smile.gif

Best Regards,
Margarit

Posted by: riti90 Sep 14 2018, 09:08 AM

Hi Again Peter,

Sorry to be a pain.

I just wanted to let you know that I've been doing some small things on that file.
I'd be happy to hear any feedback from you about it. smile.gif

 Outlook_Style_Calendar_V3_WIP03.zip ( 307.19K ): 72


But I still like your last version the most really, the one that you showed me the picture from. smile.gif

Thank You,
Looking forward to hearing from you soon.

Regards,
Margarit

Posted by: Peter Hibbs Sep 14 2018, 01:28 PM

Hi Margarit,

Well, that looks pretty good, you have obviously managed to add in the options to have the Chamber categories showing separately or all together which works pretty well.

I see you have used Allen Brownes' pop-up calendar as well. Why is that? Do you prefer this instead of the Access built-in calendar (I assume you are using Access 2007 or later)? I usually replace the Access calendar with my own design (which is similar to Allen Brownes' version) so I can do that or I can use the Allen Browne version if you prefer. You can find a demo http://www.UtterAccess.com/forum/index.php?showtopic=2034523&hl=

You said earlier that you are new to Access but I suspect you know a lot more than you are saying, especially with the VBA programming side!

Anyway, I have been adding the Yearly view mode, as promised, which is nearly finished and I have added this facility to the Appointments version that also allows recurring appointments (I know you only wanted the option to set an All Day appointment but as this part is an integral part of the 'recurring' code it was easier to include the whole lot rather than try and extract that part).

Will get back to you when I have something to upload.

Peter.


Posted by: riti90 Sep 14 2018, 01:50 PM

Hi Peter,

Thank you very much for your feedback. It means a lot really.
Well I know a few things about access and VBA that I've been self taught, but nothing more.
Just trying to learn and improve more.
About the calendar, I just used it to make a difference rather than necessity, I had it in one of my databases. But if it's OK with you you can use your pop up calendar in the new file. smile.gif

Once again thank you so much for your help.

May I ask another question please, is it any chance we can color code the chambers so each chamber looks different? If it is an easy fix than ok, if not don't worry about it.

WlI wish you a great weekend,

Regards,
Margarit

Posted by: Peter Hibbs Sep 14 2018, 03:09 PM

Hi Margarit,

May I ask another question please, is it any chance we can color code the chambers so each chamber looks different? If it is an easy fix than ok, if not don't worry about it. Hmmm.. do you mean like http://www.UtterAccess.com/forum/index.php?showtopic=2035620&hl=

If so, do you also want the color coding on the Weekly and Daily views?

Do you even need the Weekly and Daily views?

Peter.

Posted by: riti90 Sep 14 2018, 03:33 PM

Hi Peter,

Thank you for your response.

Yes, that one is perfect. I'd be very happy if the Weekly and Daily views are included as we may need to book a chamber for only a few hours or days sometimes. It could be nice if color coding is in these views But it's not really a MUST. If it is too much of a hustle I'd just leave it. smile.gif smile.gif

Ps: I'm currently using Ms Access 365 x64 smile.gif

Regards,
Margarit smile.gif

Posted by: Peter Hibbs Sep 18 2018, 03:31 AM

Hi Margarit,

Just an update for you, I have nearly finished the modifications you requested (sorry for the delay, it took longer than I thought) and I will probably upload the new version in the next day or so.

I do have some questions though :-

(1) What sort of text will you be entering on the Monthly calendar? In your vesrion you have just put 'Chamber 01', 'Chamber 02', etc but is this what you will use in the working version or is it just an example?

(2) What will be the maximum number of chambers you will need eventually? You can show about 5 lines in a date field on the Monthly calendar but if you need more you might want to change the size of the fields to show more lines (depending on your screen size).

(3) I am wondering about the table scheme for this database, is the calendar going to be used only to show the chamber usage (for example, which chambers are in use for each date) or is going to be used to show details of the tests that are being done using these chambers? If you are storing details of the tests then I would think you would need another table to store that information (unless you are already doing that). It might be helpful if you could give us a bit more detail on how the whole database works.

(4) Can you confirm that you are using the 64 bit version of Access. If so then you may need to make some changes to the next version because it will use some API calls to show the Color picker utility which needs changing slightly for 64 bit versions.

(5) Is this database being used by more than one user on a different computers via a Network or is it being used on a single computer? In either case you will need to split the file into Front-End and Back-End files (or maybe you already have this set up).

Anyway, let me know the answers to the above when you can.

Peter.

Posted by: riti90 Sep 18 2018, 03:47 AM

Dear Peter,

Thank you for your response. smile.gif

The answers to your questions are as below:

(1) I've used Chamber 01 or Chamber 02 as examples really, the chambers have different names and conditioning options(I'm focused only on the names, not the conditioning options).

(2) There are more than 5 chambers and we may but more in the future but if I need to change the size of the fields then it's OK, I'll do it considering all the names.

(3) the Calendar will be mostly for chamber use only, and we can add notes of what is it being user for. I'm using different databases for the tests for the moment.

(4) Yes we are all using 64 bit version of Access but I can make the changes for the API calls.

(5) The database will be used on Network by different users so I'll have to split it up.

Thank you for your help,
Regards,
Margarit

Posted by: isladogs Sep 18 2018, 07:00 AM

Peter
In my own version of a monthly calendar which is very similar to yours, I add a More label for when there are more events than can be displayed in the available space.
E.g on 4th of the month below



Clicking that label opens the daily calendar where all items can then be seen

HTH

Posted by: isladogs Sep 18 2018, 12:28 PM

Sorry the image in my last post was very low resolution and I left it too late to edit the post.
Here's a better version of the monthly calendar:



And here are the related day & week calendars




Posted by: Peter Hibbs Sep 19 2018, 06:24 AM

Colin,

Thanks for the tip. On my calendar the user can click on the date field which opens a pop-up form that shows all the appointments for that date or if they click on the date above the text box it opens the daily calendar for that date so I have that option pretty much covered.

As a matter of interest, how do you show the different colours for each date? I am guessing you have three smaller Text boxes for each date with some VBA code to fill in the colours.

Peter.

Posted by: isladogs Sep 19 2018, 07:53 AM

Hi Peter

QUOTE
As a matter of interest, how do you show the different colours for each date? I am guessing you have three smaller Text boxes for each date with some VBA code to fill in the colours.


VBA code is used but not as you suggested
Each event is assigned to a category when it is created/edited



The colours for each category are allocated/edited using this form (by program admins only)



Finally, this colour converter form provides an easy method of determining the colour value required for any given colour



HTH


Posted by: Peter Hibbs Sep 19 2018, 03:04 PM

Hi Colin,

OK, looks good. Perhaps you should submit it to the UA Archive!

Peter.

Posted by: isladogs Sep 19 2018, 03:47 PM

Thanks. It's a small part of a very large commercial application used in UK schools.
As such it would take a lot of work to turn it into a standalone application.

However, I do have several other items that might be suitable for the archive.
Whilst I have done this at several other forums, not sure how to submit items here.
Is there an article explaining how?

Posted by: Peter Hibbs Sep 19 2018, 05:13 PM

Hi Colin,

I am not sure if there is a specific help page for posting the the UA Archive (I couldn't find anything) but here are two help pages which might help - http://www.UtterAccess.com/forum/index.php?act=boardrules and http://www.UtterAccess.com/forum/Help.html.

To post a new article to the Archive you can just start a new thread in the Archive section (in the same way as to any other section) except that it will not appear immediately on screen. The UA managers will assess it and decide if it is suitable for the archive and if it is it will appear a day or two later. If they decide it is not suitable I guess they will let you know but none of mine have been rejected so I don't know what the procedure is.

Just some advice on posting articles from my experience - you need to make sure it will work for any country, i.e. dates and currency formats, etc. I have many enquiries about my earlier efforts where the date fields do not work in some European countries because they use date formats like dd.mm.yyyy for example. Now I always use the code supplied by Allen Browne for date formats which should work in any country. I have questions from countries all over the world (like Brazil, Australia, USA, Spain, Greece, Poland, etc) about how to change a design to work in those countries so you should be prepared for any follow ups. And the language translations can be a bit of a challenge as well. If you scan through some of my posts you will see what I mean.

Also I believe that you should provide some comprehensive instructions on how to use the examples (unless they are very self evident) as a some new users only have a very basic knowledge of Access and/or VBA/SQL coding.

Anyway, good luck if you do post anything.

Peter.

Posted by: isladogs Sep 19 2018, 05:28 PM

Thanks Peter.
That's all fairly standard procedure in terms of posting to a moderated area.
As a moderator at another forum, I'm conversant with the idea.

For info, I have a large number of example apps at http://www.mendipdatasystems.co.UK/example-databases/4594365045 many of which have been posted at other forums.
I'm currently in the process of uploading a library of code samples to add to the website.
I'm also used to providing detailed documentation though I have to say the quality of your help files is always outstanding

Thanks again for your help

Posted by: riti90 Sep 21 2018, 07:18 AM

Hi Peter,

Sorry to disturb you again but I'd like to ask if you have any updates about the calendar?

Many thanks, smile.gif
Margarit

Posted by: Peter Hibbs Sep 21 2018, 08:09 AM

Hi Margarit,

Yes I do (sorry for the delay, it took longer than I anticipated). Anyway, see the attached zip file with the modified database, User Manual and the pop-up calendar icon (in case you should need it but you probably won't).

As you are using the 64 bit version of Access you will probably need to sort out the API calls before you can test it properly. Some information on that is at the end of the User Manual.

I have added the 'Chamber' facilities, as requested, although I have still used the name 'Appointments' for the records. I guess you might want to change that word to something more appropriate like 'Booking' or whatever but I expect you will be translating everything into Polish anyway.

Let me know how you get on or if you find any bugs or problems.

Peter.

 Outlook_Calendar_V3o.zip ( 905.33K ): 92
 

Posted by: isladogs Sep 21 2018, 09:16 AM

Hi Peter

I hope you don't mind but as I also have 64-bit Office, I've added conditional compiling to the two modules so it now works on both 32-bit & 64-bit
I've called it v3p but forgot to alter the app title which still reads v3o

I may steal the yearly calendar for my own apps. Thank you

 Outlook_Style_Calendar_V3p.zip ( 836.35K ): 105

Posted by: Peter Hibbs Sep 21 2018, 10:19 AM

Hi Colin,

OK, thanks, that could be useful for some people and yes, feel free to use the Yearly form or whatever else.

Peter.

P.S. For anyone downloading this demo, I forgot to hide the Header section on the Appointment Schedule form (but I expect you figured that out yourself!!).
Also you need to change the Public Const conDayLength = 3 value in module modCalendarCode to 1 or 2 (rather than 3 which I also forgot to restore).

Posted by: whybrow23 Sep 25 2018, 07:46 AM

This is great, thanks for sharing Peter.

Question, how easy if at all, would it be to have the colour of the chamber shown in each of the calendar views like it does on the monthly one.

So say I have 4 differnet chambers
1. confirmed - yellow
2. Possible - Blue
3. Change over 1 - Purple
4. Change over 2 - Pink

I then open the calendar in the year view and from that going by the colours, I can see when and where they are

Hope I've made sense :-)

Again, thanks again for this Peter


Posted by: Peter Hibbs Sep 25 2018, 01:24 PM

Hi whybrow,

Well I think it would be possible by using the same technique as that used for the Monthly view bu the problem with the Weekly and Daily views is what it would look like if you had appointments that run over multiple time slots and even more of a problem would be if you allowed multiple appointments for the SAME time slots. If the appointments were limited on the Weekly mode to one appointment per time slot (as shown in the screen shot in post #1) then it would work OK but if you have multiple time slots for appointments and with the ditto signs enabled as well, it could look a bit of a mess. I don't think the issue will arise for the Yearly mode because it is not possible to have more than one colour per date so unless you have only one appointment per day you can't really colour code those date boxes.

Anyway, I will give it some thought and if I can come up with a good solution I will upload it here (but it could be a while as I am very busy with other projects at the moment).

As a matter of interest, how would you plan to use the calendar in light of my comments above?

Peter.

Posted by: riti90 Sep 25 2018, 02:55 PM

Hi,
I did a small change on Peter's code just for trials and the weekly mode looks like this.



If it's of any help I could attach the file so you can have a look.

Regards,
Margarit

Posted by: whybrow23 Sep 26 2018, 02:31 AM

Hi Margarit

Thanks for geting back to me.

The whole database is great, but the bit I'm very interested in is the year view.

Only 1 appointment per day would also work for me.

So the idea abit like the attached, but in the format as per the database

with the 4 or more differnet chambers
1. confirmed - yellow
2. Possible - Blue
3. Change over 1 - Purple
4. Change over 2 - Pink

Many thanks
M

 

Posted by: whybrow23 Sep 27 2018, 02:23 AM

Sorry, I was ment of said thanks Peter :-)

But also a thanks to Margarit also. the work you two have put into the DB is fantastic

Posted by: Peter Hibbs Sep 27 2018, 10:53 AM

Hi whybrow,

OK, here is a new version with the Yearly mode date boxes colored in with the Chamber colors (I guess you will want to change the 'Chamber' labelling to 'Category'or whatever for your own purposes but I will leave that to you).

Changing the code for the color changes was relatively easy but it then made it necessary to change some other facilities. For example, on the previous version I colored the currently selected date box in blue but that would not work if you have an appointment set up for that date that is already colored blue so I have changed it slightly to fix that. When you click on any date on the Yearly calendar the date box border color is changed to black and made visible (note that the current date, i.e. today's date, still shows a red outline when it does not have the focus).

As before, you can double click on any date box and add a new appointment and when you close the pop-up form the calendar will color in that date box with the color of the categry. However, if the calendar is in '<<< Show All >>>' mode the boxes will all be colored grey because if you have more than one appointment for different categories on the same date, you cannot show the color for both categories. What would happen is that the date box will be colored with whichever appointment is processed last, usually the one entered last which would be very confusing for the users. To show the categories in the associated colors you need to select the relevant category (i.e. chamber) in the drop down box at the top of the form.

Hopefully this will do what you want but let me know if you find any bugs.

Peter.

 Outlook_Style_Calendar_V3o.zip ( 593.32K ): 77
 

Posted by: riti90 Sep 27 2018, 11:46 AM

Hi Peter/whybrow,

There is another model of Peter's Great Calendar. The Yearly View is different than the previous one but if you find it useful you can have a look.

For me Peter's last one work perfect and that's how I need it to work.

 Outlook_Style_Calendar_V3p_WIP04.zip ( 530.22K ): 116


Thank you,

Margarit

Posted by: Peter Hibbs Sep 27 2018, 01:19 PM

Hi Margarit,

Thanks for the new version, very good.

When I get some time to spare I will update my version of the Weekly and Daily view with the Week code from your last version (if that is OK with you). You never know that someone might need those facilities in the future.

Peter.

Posted by: riti90 Sep 28 2018, 02:07 AM

Hi Peter,

Thanks for the feedback.

I'm sure you will make it look much better that what mine looks. smile.gif smile.gif

I was just wondering if it is any way we can merge the rows on the Weekly View or Daily View so it will look more like Colin's one.

http://www.UtterAccess.com/forum/index.php?s=&showtopic=1969978&view=findpost&p=2696208

Regards,

Margarit

Posted by: whybrow23 Sep 28 2018, 03:13 AM

Margarit/Peter

You two are amazing.

Both DB versions are great.

Peter, it's a shame it's not easy to show the different Chambers on the Year view as the default, but it will still work for me.

Margarit, The yearly view looks great, but hard to read as it's small, I've tried changing the size, but I wasn't able to ???? :-(

Between the two I noticed on Peter's verion on the yearly view "Print Calendar" is not available, so I'm going to have a play with getting Margarit's yearly view print calendar to work with Peter's DB.

Before I look into the above, have you already done this?

Again guys, FANTASTIC thumbup.gif

Marc

Posted by: whybrow23 Sep 28 2018, 06:37 AM

Found a little bug

I'm in the Year calendar view with the "Select a Chamber" set to "Show All"

I can double click on any of the dates where an appointment has been entered and it will open the "Appointment Schdules" form and I'able to see and edit the appointments. I can also click once on one of the dates and the appointment will show in the field on the righthand side of the year view and I can double click the appointment and I'm able to view and edit the record again.

The issue I'm having is:

Again in the Year calendar view, if I have the "Select a Chamber" set to something other than "Show All" and I try to do any of the above, the "Appointment Schdule" form will appear, but the record data is not showing, so not able to view of edit the record

Hope this makes sense??

Thanks
Marc

Posted by: riti90 Sep 28 2018, 07:28 AM

QUOTE
Found a little bug

I'm in the Year calendar view with the "Select a Chamber" set to "Show All"

I can double click on any of the dates where an appointment has been entered and it will open the "Appointment Schdules" form and I'able to see and edit the appointments. I can also click once on one of the dates and the appointment will show in the field on the righthand side of the year view and I can double click the appointment and I'm able to view and edit the record again.

The issue I'm having is:

Again in the Year calendar view, if I have the "Select a Chamber" set to something other than "Show All" and I try to do any of the above, the "Appointment Schdule" form will appear, but the record data is not showing, so not able to view of edit the record

Hope this makes sense??

Thanks
Marc


Hi Marc,

That happens when you have booked a chamber and then you are trying to select another chamber and view the Schedule.
If you for example book chamber 01 and on the 'Select Chamber' you select chamber 01 then it work perfectly, but when you trying to select another chamber of course you won't be able to view chamber 01 and you'll see the form with blank records.

Hope this helps,

Regards,
Margarit

Posted by: whybrow23 Sep 28 2018, 08:17 AM

Hi Margarit

No, it doesn't work.

The only way I'm able to view/edit a record across any of the Chambers is to have the Year calendar view and "Show All" chambers selected. :-(

Thanks
Marc

Posted by: Peter Hibbs Sep 28 2018, 09:56 AM

Hi Marc,

Here is a new version with a Yearly calendar print out provided. The calendar report is basically the same as the form version with a few minor differences.

I don't know why you cannot edit any appointments unless you are in <<< Show All >>> mode, I don't see the problem here. Maybe there is some corruption with the version you are using so try this one to see if it works for you.

I think you also wanted to show the dates in the appropriate colors in the Yearly view instead of all grey. If you open the module modCalendarCode and change the constant conShowAll to a value of 1 then the Yearly calendar will show the color codes. However, this only really works if you never have two different catgories for the same date because then, of course, it will only show one of the colors. If your database does not have this problem then it should work.

Note that for the Calendar report I have hidden the date outlines so it emulates the form version but if you want to have each date box outlined then just select them all (maybe not the Days of the Wek and the Month titles though) and change the Border Style property to 'Solid' and the Border Color to grey (or whatever color you prefer).

Margarit,

I agree that the Weekly and Daily modes would look better in the way that Colin has done them but I don't know of any way to do this using a Continuous type form (which those two forms are). I don't know how Colin has done this but I suspect it must be with a separate Text box control for each row of the form and perhaps extra Text boxes for the colored boxes, Maybe he can enlighten us on the method he used.

Anyway, let me know if you have any problems.

Peter.

 Outlook_Style_Calendar_V3o.zip ( 679.8K ): 64
 

Posted by: isladogs Sep 28 2018, 11:44 AM

QUOTE
I agree that the Weekly and Daily modes would look better in the way that Colin has done them but I don't know of any way to do this using a Continuous type form (which those two forms are). I don't know how Colin has done this but I suspect it must be with a separate Text box control for each row of the form and perhaps extra Text boxes for the colored boxes, Maybe he can enlighten us on the method he used.


OK I'll do my best but I made this about 5 years ago and haven't looked at the code in a long time

NOTE: This is a small part of a commercial application for schools
At some point, I may add the yearly calendar then try to make this into a standalone application for upload as an example database

As stated in post #44, each event is colour coded according to the event type
Event can be all day (one day or multiple days) or have a start and end time. Part day events can start one day and finish on another e.g. overnight school trips to London.

All calendar events are stored for a (school academic) year are stored in one table in the SQL BE
However when the calendar form is in use, separate month, week and day event local tables are populated in order to show the relevant events for the selected period.
This of course means multiple users can each view different months (etc) at the same time as each has their own local copy of the FE.
These calendar forms are for display only ... except for a very limited number of users with editing rights.
The local data tables are emptied after the calendar is closed



Here is a typical week view


In design view, you can get a better idea of how its done


On each day, there can be up to 6 all day event slots (at the top) and 6 part day event slots.
The background grid is marked in half hourly blocks but these are for display purposes only
The position of each block is then adjusted using code depending on the start time and the block height is determined as a proportion of the full 24 hours e.g. 09:00 - 15:00 = 6 hours = 0.25 day
Next the code checks for overlapping events and adjusts the block width and horizontal positions depending on how many events overlap.
The small textboxes with numbers are just marker labels for each event ID

The data for each week is stored as temporary data in a

Nothing to it really!

The day view uses exactly the same idea but spread over a greater width





I expect this explanation will raise more questions than it answers so feel free to come back if you want further explanation

BTW coding these forms consisted of lots of near identical code for each event slot
Once I got the first event coded correctly using suitable functions, it was just a matter of copying and pasting many times with minor code changes!
Tedious to do and perhaps not very elegant but the code was endlessly repeatable.

HTH




Posted by: Peter Hibbs Sep 29 2018, 04:39 AM

Hi Colin,

OK, thanks for the extra info, I thought it would be something like that.

Peter.

Posted by: whybrow23 Oct 1 2018, 06:04 AM

Hi Peter

Thank you for the update.

The year view looks great now it shows all of the Chambers.

I've also worked out the problem with " I don't know why you cannot edit any appointments unless you are in <<< Show All >>> mode"

it happens if I go in and change the Chamber names

so where you have:
Chamber 01
Chamber 02
etc

I've changed them to:
Confirmed
Possible
Training

I then go in and create new appointments, select "Show All" and it works normally, but again if I change to only show "Confirmed" then try and click on one of the confirmed appointments, no data is shown on the frmCalendarAppt form

Any idea why this would be?

Thanks
Marc

Posted by: whybrow23 Oct 1 2018, 06:31 AM

OK, so playing around a bit more.

If I change the Chamber from Chamber 01 to Confirmed - I get the issues

but if I change it from Chamber 01 to Confirmed 01 - it all works fine.

I can work with this, but if it's an easy fix, it would be more idea for me

Thanks again
Marc

Posted by: isladogs Oct 1 2018, 07:42 AM

Peter appears to be offline

Suggest you try the following on a COPY of the application (untested)
Do a global search in the VBE for Chamber 01 & replace all with Confirmed

If it doesn't work, go back to the original version

Posted by: whybrow23 Oct 1 2018, 08:31 AM

Yeah, I did try that and I had no luck (couldn't find Chamber 01 in any of the VBA code)

Thanks
Marc

Posted by: Peter Hibbs Oct 1 2018, 09:25 AM

Hi Marc,

Sorry for the delay, was playing tennis this morning, got to keep fit at my age.

I see the problem you are having but at the moment I don't have a solution. It is very weird that changing the name should make it fail like that and I cannot see why at the moment. Leave it with me though and I will find a fix one way or another.

Peter.

Posted by: whybrow23 Oct 1 2018, 09:37 AM

No need to say sorry, I'd rather be playing tennis than being at work :-)

Like I said, no need to worry if it's not a quick fix, I can work with it.

But I do have another question (sorry) :-)

Your Year calendar report is great, but the one Margrit done would be more ideal. It's like the one I uploaded. see attached

I've tired replicating it, but with no joy. You able to help? no problem if it's too much bother.

and I really can't thank you eought thumbup.gif





Posted by: Peter Hibbs Oct 1 2018, 09:39 AM

Hi Marc,

OK, it is not that weird if I had done it properly. Make this change to fix it :-

Open the form frmCalendarAppt in Design mode and click on the list box lstAppts.
Open the query in the Row Source property in Design mode and move the criteria text (Like "*" & [Forms]![frmCalendarAppt]![txtChamberID]) from the ChamberRef column to the ChamberID column.
Close the query and form and save as normal and that should fix the problem.

The reason it worked if you added 01 to the name was that it found a match with any entries that had a 1 in them because that is the value in the ChamberID field.

Sorry about that but thanks for finding error in case anyone else has the same issue.

Peter.


Posted by: whybrow23 Oct 1 2018, 09:49 AM

that worked a treat, Thanks Peter

thanks.gif

Posted by: Peter Hibbs Oct 1 2018, 10:09 AM

Hi Marc,

OK, good.

About your Yearly chart, I guess it is possible but would need a bit of time to work out the code to do that. Have you seen Version 2 of the http://www.UtterAccess.com/forum/Holiday-Planner-Demo-t2001188.html in the UA Archive? The form layout would be similar in some ways to this I think.

What version of MS Access are you using?
What do the horizontal colored lines on your chart represent? Are they 'appointments' that cover multiple days or are they a lot of 'appointments' for single days or what (I guess that you are not using this database to make appointments as such)?
Do you really need a 15 month calendar (it would be easier in code to just show 12 months)?

I may be interested but I have other projects on at the moment so it could be a week or two before I can do this, can you wait that long or is this more urgent?

Let me know what you think.

Peter.

Posted by: whybrow23 Oct 2 2018, 03:50 AM

good morning Peter

I'm using Access 2016.
The horizontal colours are booking/appointments that cover multiple days, some might be 1 or 2 days, you can see that by the different colours.
No the 15 month calendar isn't needed, 12 month would do the job.

Of course it could wait, I understand you are busy, there is no rush at all.

No not looked at the Hoilday Planner Demo, will take a look.

Thank you
Marc

Posted by: Joan Oct 3 2018, 08:33 AM

Hello everyone,

Although this is my first post, I have been browsing for many days through the forum searching and learning a lot... Many thanks to all of you, this forum is a fountain of knowledge.

Lately I have been seeking the way to make and appointment calendar in order to assigning various workorders during the month, And regarding the sample of Mr. Hibbs (superb notworthy.gif ) I think that it could be a great solution. In this case I have changed the chambers names for the names of technicians and it works perfectly. Every technician has his workorders and I can select "all" with the combobox and I can see all the appointments during the month.

But in order to facilitate the everyday planning, I'm trying to make a change on the view of "frmCalendarMonth". I would like to show a view grouped by technician in a different line, and dynamically in function of number of technicians.
I have spent a lot of hours... but I have got stuck and I'm not able to find the solution confused.gif .

Any help will be most welcome.

And sorry for my English, I attached a picture (I suppose that will be more easy to understand me thumbup.gif )



Thank you and greetings from Spain!

Posted by: Peter Hibbs Oct 3 2018, 09:41 AM

Hello Joan,

welcome2UA.gif

Well, I am not sure what exactly you are trying to do. If you can select a technician name in the drop down box and show just his/her jobs, does that not do what you want? What would be the purpose of a second row for each week of the month?

I guess anything is possible but it sounds like it would mean making a lot of changes to the calendar design which would take some time. Perhaps you can explain in more detail what you are trying to achieve.

Peter Hibbs.

Posted by: Joan Oct 3 2018, 05:07 PM

I'm sorry, it's a bit difficult to explain it with my English, but I'll try iconfused.gif .

For example, if we have four technicians, the form would have to show four independent rows (one row for each technician). In other words, week 16 would have four rows, week 17 four more rows, and so on ...).

The main reason for this, is to see in the same place the availability of the technicians and the load of work for this month, and to be able to move or change the work orders from one technician to another if it is necessary. Where we work, we usually have many unexpected events and we need to be able to see everyone's situation at a single glance and do the changes very fast.

Because of the system is based on a continuous form, I thought that it would be more easy to make this kind of form. I think that this system is more flexible than the typical calendar form with 35/42 text box for days appointments (and at the same time to have a system that grows automatically - more technicians, more rows ...)

Attaching more pictures, I hope it helps.


VS


Thanks a lot for your time (and your patience with my english ...) thanks.gif

Posted by: wornout Oct 3 2018, 07:08 PM

Hi I am trying to make it so I can get the appointment into outlook so far I can get it so when you click save it opens the appointment screen in outlook with everything filled in but what I am really trying to do is save the appointment with the correct date as well as an reocurring ones with out opening the form and also to be able to delete from out look.
I probly have gone about this the wrong way attached is what I have so far



 Outlook_Style_Calendar_V3iMyONE.zip ( 237.11K ): 88
 

Posted by: Peter Hibbs Oct 4 2018, 03:30 AM

Hi wornout,

It would help if you could say what the problem is but I tested your database and the 'normal' appointments seem to import into the database OK but the 'recurring' appointments only import the starting date so I guess that is the problem you are having.

Unfortunately, I don't really know much about Outlook automation with Access, I just copied the code in the original version of my demo from an Internet site several years ago and that was before I added the 'recurring appointment' facility so I don't think I can help you much here. I suspect that recurring appointments have some special code in Outlook so that they are linked together in some way (as they are in my current version of the demo) but I don't know what that format is. All I can suggest is that you do a bit more research on the Internet to see if someone has posted any code which might help. I did find a couple of sites which might supply you with some more information which are https://www.experts-exchange.com/questions/26412045/Looping-through-Outlook-Calendar-Items-in-VBA-MS-Access.html and https://access-programmers.co.UK/forums/showthread.php?t=158713and I expect there are a few more.

It looks like you can import the recurring pattern code from Outlook (using the GetRecurrencePattern variable) but I suspect it will be nothing like the pattern string that I used because I could not use exactly the same facilities in Access as they use in Outlook so I had to make up my own system. I guess the challenge will be to see if you can decode the Outlook system and adapt it to work in the demo Calendar or maybe you could just save the recurring dates as 'normal' dates which would lose the extra 'recurring' facilities.

Anyway, sorry I can't be of more help but good luck with your project and let us know if get it working satisfactorily (it could help other developers).

Peter Hibbs.

Posted by: Peter Hibbs Oct 4 2018, 11:13 AM

Hi WhyBrow23,

OK, see attached zip with new version of your database. I have added an extra page to the calendar that shows the year's appointments in the format that you wanted and you can select that with the Chart button at the top of the main form. You still have the Yearly mode which I have changed so that it shows all the categories (or whatever you call them) at the same time or you can select one of the categories as before.

Bear in mind that this version ONLY works if you have just one appointment per day (as you requested).

You can change the colors and the category names (your examples were a bit confusing on this so you will probably need to do that anyway).

I haven't had time to test it thoroughly, I will leave that up to you, so if you find any problems then let me know.

Let me know how you get on or if you have any questions.

Peter.

 

 Outlook_Style_Calendar_V3o_WB.zip ( 722.05K ): 158
 

Posted by: wornout Oct 4 2018, 01:48 PM

Thanks peter Hibbs,
My main thing was getting to delete the appointment from outlook as well and to get reoccurring into access will post back here when I find a solution.
I love the calendar it is great and perfect for what I need. It is in a CRM database that I made and because I use the CRM everyday and its the first thing I open I thought it would be nice so I dont have to open outlook

Posted by: whybrow23 Oct 4 2018, 03:05 PM

Wornout

Not sure if the attached DB will help in anyway. But my DB was based on adding an all day appointment to an outlook calendar without it opening the Outlook appointment form and having to save/sent it.

On the home screen, click on Add New Team Member, Who - Put the Techs name and Email - put the email address of the calendar you want it to go to.

Close the form which will take you back to the home screen and you will be able to fill in an appointment for the person and it will go into the shared calendar.

Please note, you will need permissions to add to the calendar for it to work, if not an popup will appear.

Hope I've made sense and hope it may help a little.

I couldn't test your DB, just kept getting VB errors.

Thanks
Marc

 Outlook_Cal.zip ( 247.5K ): 84


Posted by: whybrow23 Oct 4 2018, 03:08 PM

Hi Peter

Thanks for do this. Getting a bit late now, so I'll sort sometime out tomorrow to take a look.
By the screenshots, it looks great.
Thanks again and I'll get back to you
Marc
thumbup.gif

Posted by: Peter Hibbs Oct 5 2018, 06:02 AM

Hi Joan,

I agree that for this type of form a Continuous type form would be best because, as you say, you can easily add more technicians (and rows) as required. I would think that this could be done with a bit of VBA coding (although I have not tried this myself), you would need to rewrite the code that converts the appointments data from the Appointments table into a format that could be copied to the temporary table (tblMonthData in this case) and I guess you would not need the Combo box at the top of the form to select a technician name since you are showing all the names on the form anyway.

One technical problem would be that you would need some extra code to add more records to the temporary table if you should add a new technician to the database but I don't think that would be too difficult. Also, unless you have a very big screen, I would guess that you would need to scroll the form down if there were more technicians than could fit on one screen (which is something I usually try and avoid on this sort of form) so would that be a problem for your situation? I suppose it would depend on how many jobs per day you would normally allocate. Also the color coding would need some thought but I think it could be done using the HTML coding that the demo already uses.

If you were to add this Monthly mode to my calendar demo, would you still need the Weekly and Daily modes or is this only being used for the Monthly mode? Have you tried to design this form yet and if so, how much have you done?

How are you planning to proceed with this project?

Peter.

Posted by: wornout Oct 5 2018, 07:52 PM

Thanks whybrow23 Have down loaded it and installed the missing calander control but it now stopes on the word me.Somefield so will have a play with it tonight

Posted by: whybrow23 Oct 8 2018, 02:51 PM

Hi Peter

The update looks great.

Another request:

I've never done it before and not sure if you have, but do you have any idea on having a login to the calendar (admin and user login) with the user only having read/view only access to it?

Many thanks
Marc


Posted by: Peter Hibbs Oct 8 2018, 03:57 PM

Hi Marc,

I haven't had to provide any log in facilities on my calendars before but I guess it would not be too difficult, you can probably find examples on the Internet.

Allowing some users access to all the facilties and other users to have 'read only' facilities would be a bit more involved as you would have to allow some of the buttons to work so they could navigate the various calendar modes but you would need to disable the buttons that allow appointments to be made/deleted or edited, etc.

Sorry I can't help more with that at the moment but good luck with your project.

Peter.

Posted by: Joan Oct 8 2018, 06:20 PM

Hi Peter,

I am so sorry for the long delay for the response ( very busy days ...)and many thanks for the interest and givin me some advice.

About screen, we are working with big screens because we need to deal with large excel files and construction plans.
Relating to color coding, I think that it works perfectly like demo shows.
And the main use would be on monthly mode, often we need to change work orders or projects from week to week and is more easy to have a wider vision of the different possibilities with monthly mode.

At the moment I'm focussed on understand how to put the technicians in the temporary table with an easy way, and how to create dinamically this table.

I suppose that from the qryAppointments I need to count the number of chambers (technicians in my case) in order to create or repeat the week rows in the tblMonth.
The main problem is that the tblMonth is pre created, and I'll need to create rows according the necesities of this moment(dinamically).
In "modCalendarCode" I'll try modify the variable "vArray", but is hard to understand code. I'll need a lot of time... crazy.gif

Any help will be most welcome.

Regards!!


Posted by: ixxo09 Oct 8 2018, 11:48 PM

Hello Peter:

Some bugs that I had found and some personal suggestions for your app.
Congratulations. Nice App and effort. Keep in touch!


*** In Colorcode Texbox******** at frmChamberEdidSub

note: the bug is when you press a letter or more than 8 digits

I made this function at frmChamberEdidSub:
Private Sub ColorCode_KeyPress(KeyAscii As Integer)
'Call Public Sub "KeyPressOnlyNumbers"
Call KeyPressOnlyNumbers(KeyAscii)
End Sub


at ModCalendarCode

Public Sub KeyPressOnlyNumbers(ByRef KeyAscii As Integer)

'Asegurarse que sólo sea introducido un valor numérico
Select Case KeyAscii
Case vbKey0 To vbKey9
'En caso de que control sea "ColorCode" máximo 8 dígitos
With Screen.ActiveControl
If .Name = "ColorCode" Then
If .SelLength > 0 Then Exit Sub
If Len(.Text) = 8 Then KeyAscii = 0: Beep
End If
End With
Case vbKeyBack, vbKeyClear, vbKeyDelete, vbKeyReturn, vbKeyEscape
Case vbKeyLeft, vbKeyRight, vbKeyUp, vbKeyDown, vbKeyTab
Case Else
KeyAscii = 0
Beep
End Select

'If ((KeyAscii < 48 Or KeyAscii > 57) And (KeyAscii < 45 Or KeyAscii > 45)) Then If (KeyAscii <> 8) Then KeyAscii = 0
'If Not IsNumeric(Chr(KeyAscii)) And Not KeyAscii = 8 Then KeyAscii = 0

End Sub


***Also your ModColorPicker for me it doesn´t work Access 2016 64Bits . I made this one in case that another person has the same problem

**** modColorPicker*****

Option Compare Database
Option Explicit


#If VBA7 And Win64 Then
Private Declare PtrSafe Function ChooseColorA Lib "comdlg32" _
(pChoosecolor As CHOOSECOLOR_TYPE) As Boolean

Private Type CHOOSECOLOR_TYPE
lStructSize As Long
hwndOwner As LongPtr
hInstance As LongPtr
rgbResult As Long
lpCustColors As LongPtr
Flags As eFlags
lCustData As LongPtr
lpfnHook As LongPtr
lpTemplateName As String
End Type
#Else
Private Declare Function ChooseColorA Lib "comdlg32" _
(pChoosecolor As CHOOSECOLOR_TYPE) As Boolean

Private Type CHOOSECOLOR_TYPE
lStructSize As Long
hwndOwner As Long
hInstance As Long
rgbResult As Long
lpCustColors As Long
Flags As eFlags
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type
#End If

Private Enum eFlags
CC_ANYCOLOR = &H100
CC_ENABLEHOOK = &H10
CC_ENABLETEMPLATE = &H20
CC_ENABLETEMPLATEHANDLE = &H40
CC_FULLOPEN = &H2
CC_PREVENTFULLOPEN = &H4
CC_RGBINIT = &H1
CC_SHOWHELP = &H8
CC_SOLIDCOLOR = &H80
End Enum


Public Function DialogColor(ColorValue As Long) As Long

Dim CC As CHOOSECOLOR_TYPE
#If Win64 Then
CC.lStructSize = LenB(CC)
#Else
CC.lStructSize = Len(CC)
#End If

With CC
.hwndOwner = hWndAccessApp
.Flags = CC_SOLIDCOLOR Or CC_FULLOPEN Or CC_RGBINIT
.lpCustColors = VarPtr(String$(16 * 4, 0))
.rgbResult = ColorValue
End With

If ChooseColorA(CC) Then
DialogColor = CC.rgbResult
Else
DialogColor = 0
End If

End Function


***Another bug when your delete an appointment it doesn´t update the calendar and it doesn´t delete the additional info of that appointment****
In mine I fixed will be easy for you to fix it so I don´t give the code*****

*** Another bus is here, I sort it out with exit line code***** at frmChamberEditSub

Private Sub ColorCode_Change()

Dim vHex As String

If IsNull(ColorCode) Then Exit Sub '***********HERE*****************
vHex = GetRGB(Me.ColorCode.Text) 'convert color code to HTML Hex value
Me.ChamberColor = "<font style=""BACKGROUND-COLOR:#" & vHex & """>" & " </font>" 'format text with back color

End Sub

***On my criteria in the tblChambers I restricted fields ChamberRef and ColorCode (index no duplicate and 0 long) ´cause
I presume is not good to repet colors chambers and No color asign without Chamber name


By the way your app is VERY NICE. CONGRATULATIONS I will continue follow your App. Excellent!! thumbup.gif

Posted by: Peter Hibbs Oct 9 2018, 05:23 AM

Hi ixxo09,

welcome2UA.gif

Thank you for your feedback on the calendar demo, very useful. Bear in mind that this version was done very quickly so I did forget to add a few checks here and there (that is my excuse anyway). Here are a few comments on your observations :-

Some bugs that I had found and some personal suggestions for your app.
Congratulations. Nice App and effort. Keep in touch!
Thanks.


*** In Colorcode Texbox******** at frmChamberEdidSub
note: the bug is when you press a letter or more than 8 digits


OK, good point, I normally limit input to text boxes to only the keyboard characters that are relevant for that text box but I think you can do this with a bit less code than your suggestion. For example you can change the code in the form frmChamberEditSub like this -

CODE
Private Sub ColorCode_Change()

Dim vHex As String

    If Nz(Me.ColorCode.Text) = "" Then Me.ColorCode.Text = 0: Beep              'if user deletes number then force value to 0
    If Me.ColorCode.Text < 16777216 Then                                        'if value is a valid Color Code then
        vHex = GetRGB(Me.ColorCode.Text)                                        'convert color code to HTML Hex value
        Me.ChamberColor = "<font style=""BACKGROUND-COLOR:#" & vHex & """>" & "                         </font>"     'and format text box with back color
    Else
        Me.ColorCode = Left(Me.ColorCode.Text, Len(Me.ColorCode.Text) - 1)      'if value is greater than 16777215 then delete last digit
        Me.ColorCode.SelStart = 9                                               'and move cursor to end of field
        Beep
    End If

End Sub

Private Sub ColorCode_KeyPress(KeyAscii As Integer)
    If Chr(KeyAscii) Like "[!0-9]" And KeyAscii <> vbKeyBack Then KeyAscii = 0: Beep    'allow user to press keys 0 to 9 only
End Sub

If you search the VBA code for KeyPress(KeyAscii As Integer) you will see a number of examples on how to restrict input to specific characters.
The first line forces the value to 0 if the user should delete the contents of the field so that there is always some value in that field (using the IsNull function didn't work for me although I don't know why).

***Also your ModColorPicker for me it doesn´t work Access 2016 64Bits . I made this one in case that another person has the same problem
Yes, you are correct. I don't have Access 64 bit so I tend to forget about this. However, Colin (Isladogs) did post a code sample to do that in Post #51 in this thread so I guess that any developers with the 64 bit version will have used that code but thanks for reminding me that I should mention this when using API calls in a demo.

***Another bug when your delete an appointment it doesn´t update the calendar and it doesn´t delete the additional info of that appointment****
In mine I fixed will be easy for you to fix it so I don´t give the code*****

Hmmm.. I don't see this problem here, whenever I delete an appointment the main calendar is updated when the Appointment Schedule form is closed. Perhaps you could explain in more detail what happens!

*** Another bug is here, I sort it out with exit line code***** at frmChamberEditSub
See comments above.

***On my criteria in the tblChambers I restricted fields ChamberRef and ColorCode (index no duplicate and 0 long) ´cause
I presume is not good to repeat colors chambers and No color asign without Chamber name

Yes, I agree that this is a good idea.

By the way your app is VERY NICE. CONGRATULATIONS I will continue follow your App. Excellent!!
OK, thank you for your interest.

Peter Hibbs.

Posted by: Peter Hibbs Oct 9 2018, 05:44 AM

Hi Joan,

I have sent you a private message concerning this database.

Peter.

Posted by: ixxo09 Oct 9 2018, 10:13 AM

***Another bug when your delete an appointment it doesn´t update the calendar and it doesn´t delete the additional info of that appointment****
In mine I fixed will be easy for you to fix it so I don´t give the code*****
Hmmm.. I don't see this problem here, whenever I delete an appointment the main calendar is updated when the Appointment Schedule form is closed. Perhaps you could explain in more detail what happens!

whwn you are at the form char and change to year mode iy is fine except that leave the aditional info of the erase appointment.
and when you are at the year mode eand erase an appointment check video upload.

see the update file...

Outlook_Style_Calendar_V3o_WB

Ill send it later on...

Posted by: ixxo09 Oct 9 2018, 02:02 PM

Hello peter sorry for the delay of sending you the file...here it goes....
thanks.

 

Posted by: Peter Hibbs Oct 9 2018, 03:20 PM

Hi ixxo09,

Yes, I see now. I don't know how you fixed this small problem but I think the simplest way is to add the following line of code -

CODE
    Me.txtDetails = ""

to the start of the Public Sub UpdateDetails(vApptID As Long) sub-routine on form frmCalendarYear.

And I liked the video showing the problem, very neat.

Thanks for your help,

Peter.

Posted by: ixxo09 Oct 10 2018, 01:28 AM

Hi peter good morning from San Sebastian jajajaja

Yes, yes, you are absolutely right of the simple way to fix it.

But I did like this..... hope I gave some ideas....

At the frmCalendarYear remove the public sub UpdateDetails and paste it in modCalendarCode

Public Sub UpdateDetails(vApptID As Long, Optional frm As Form)

'Display appointment details on screen
'Entry (vApptID) = ApptID of appointment to display

Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("SELECT * FROM qryAppointments WHERE ApptID = " & vApptID)
If rst.EOF And rst.BOF Then frm.txtDetails = vbNullString: Exit Sub
frm.txtDetails = "Appointment Times: " & Format(rst!ApptStart, "Short Time") & " to " & Format(rst!ApptEnd, "Short Time") & vbCrLf _
& "Location: " & rst!ApptLocation & vbCrLf _
& "Notes: " & rst!ApptNotes & vbCrLf _
& "All Day Event: " & IIf(rst!AllDayEvent = True, "Yes", "No")

rst.Close
Set rst = Nothing


End Sub


make little changes in the next subs... at the same form code marked with asterisks

Private Sub lstAppts_Click()
UpdateDetails Me.lstAppts, Me 'Update appointment details on screen *******
End Sub

Private Sub lstAppts_DblClick(Cancel As Integer)

'Opens Appointment Schedule form when user double clicks an date field
'Entry (Parent!txtCurrentDate) holds date of selected cell
'Exit Appointment form displayed and new appointments displayed when form closed

DoCmd.OpenForm "frmCalendarAppt", , , , , acDialog, CDate(Parent!txtCurrentDate) + conFirstTime & "," & Me.WindowTop + 400 & "," & Me.Parent!cboChamber 'open form to add or amend appointment
If gDummy = 1 Then 'if any appt changed or added or deleted then
Me.Parent.CalendarUpdate 'Run CalendarUpdate subroutine in parent form *****
UpdateDetails Me.lstAppts, Me 'Update appointment details on screen *******
End If

End Sub

and finally at the frmCalendarMain in the public sub "CheckMode()" in the textmode case 0

replace ...
Me.frmCalendarYear!lstAppts.Requery

with ....

With Me
.frmCalendarYear!lstAppts.Requery
If Not IsNull(.frmCalendarYear!lstAppts) Then _
Call UpdateDetails(.frmCalendarYear!lstAppts, [Form_frmCalendarYear])
End With

and put it down in and "CalendarUpdate()" in the textmode case 0 aswell

Public Sub CalendarUpdate()

'Update screen display in current mode
'Entry (txtMode) = 0 (Yearly), 1 (Monthly), 2 (Weekly) or 3 (Daily) or 4 (Chart)
' (txtDate) = Date to be displayed

Me.Painting = False
Select Case txtMode
Case 0 'if tab 1 (Year) then
EnterMonthDates Me.frmCalendarYear, Year(Me.txtDate) 'and add dates for selected year to form
ShowYearAppts Me.frmCalendarYear, Me.txtDate, Me.cboChamber 'show appts for month
If Year(Me.txtDate) = Year(Me.txtCurrentDate) Then 'if displayed year = current year then
HighlightSelectedDate Me.frmCalendarYear, Me.txtCurrentDate, Me.txtCurrentDate 'highlight selected date with black border
End If
With Me
.frmCalendarYear!lstAppts.Requery
If Not IsNull(.frmCalendarYear!lstAppts) Then _
Call UpdateDetails(.frmCalendarYear!lstAppts, [Form_frmCalendarYear])
End With

note: ´cause is isnull() is not working for you think and alternative and if your code is better let me know...thanks pale!!!..have a nice day.

Posted by: DBQueen Oct 10 2018, 11:36 PM

nice app.. Peter

Posted by: Peter Hibbs Oct 11 2018, 10:03 AM

Hi DBQueen,

Thanks very much.

Peter.

Posted by: whybrow23 Oct 13 2018, 04:02 AM

Hi Peter

Thanks for your feedback.

So I was thinking I could make a copy of the frmCalendarMain and calling it frmCalendarUser, but when I open it a window pops up with the attached.



I've tried added the new form frmCalendarUser to the code and the queries but with no luck. the pop still appears. any idea what I might be missing?

My thinking is I could then use this new form as the read only one for the users.

Many thanks
Marc

Posted by: isladogs Oct 13 2018, 05:39 AM

Hi Marc

Why are you duplicating the form? Your original idea in post #90 is the logical way to ensure a form is read only for some users and editable for admins.

Posted by: whybrow23 Oct 13 2018, 06:56 AM

Hi isladogs

Because I not sure on how to do it.

But after writing my last post, I'm thinking I could make a copy of the database, 1 for Admin and 1 for Users and then spilting the DB with both of the db pointing towards the same tables.

But if you know of a way I could use the password way and incorporte it with the db, help would be appreciated?

many thanks
Marc

Posted by: isladogs Oct 13 2018, 08:34 AM

You could do that but once again I wouldn't do so as it means duplicating any changes that are made in the future

There are lots of examples of user login forms available online including the the code archive at this forum
Have a look at the links in this thread as a starting point: http://www.UtterAccess.com/forum/login-form-t1996275.html

However if the form should be read only except for you as a developer you MAY have a simpler solution
It depends on checking the user name and workstation name

Add code similar to this in the Form_Load event of your calendar form

CODE
Private Sub Form_Load()

Dim ctrl As Control

'lock controls by default
For Each ctrl In Me.Controls
      If TypeOf ctrl Is TextBox Then
       ctrl.Locked = True
      End If
    Next

'alter the next line as appropriate
If Environ("UserName") = "YourUserName" And Environ("ComputerName") = "YourWorkstationName" Then

    For Each ctrl In Me.Controls
      If TypeOf ctrl Is TextBox Then
       ctrl.Locked = False
      End If
    Next
End If

End Sub


NOTE:
1. Its not a flexible solution in that if you swop PCs, the form will also be locked for you
The code above only locks textboxes. It will need modifying if you have combos/listboxes etc

2. You can also lock the form by default by setting Allow Edits/ Allow Additions / Allow Deletions to No in the form properties

3. Another approach uses the tag property to lock controls. Have a look at this example app: http://www.mendipdatasystems.co.UK/set-controls/4594398114 - you may be able to adapt that for your needs

Good luck

Posted by: whybrow23 Oct 13 2018, 08:48 AM

thank you

I'll have a look and read

Marc

Posted by: whybrow23 Oct 14 2018, 08:46 AM

Hi Peter

Think I've found a couple of bugs

If I click the Unbound field for the Year at the top of the Main form See attached



I get the following Run-time error '2465'
Can't find the field 'CalBtn' referred to in your expression.

and the other, when I go to enter in a new appointment and I go to enter a date, I click on the calendar date control icon and I get the follow error.

An expression in argument 2 has an invaliud value

Can you help please?

Thanks
Marc

Posted by: Peter Hibbs Oct 14 2018, 09:53 AM

Hi Marc,

I get the following Run-time error '2465'
Can't find the field 'CalBtn' referred to in your expression.
Did you delete (or change the name of) the CalBtn icon image in the Header section of the form?

and the other, when I go to enter in a new appointment and I go to enter a date, I click on the calendar date control icon and I get the follow error.
An expression in argument 2 has an invalid value
I don't get any errors for this, did you make any changes to this form (maybe the same icon control as above)?

Peter.

Posted by: whybrow23 Oct 14 2018, 10:31 AM

Thanks Peter, yes I didn't mean to but I deleted the CalBtn date icon.

I still get the 2nd error - An expression in argument 2 has an invalid value - when I click on that icon or when I try to pick a date on the appointment form.

It also happens on the last update DB you done for me and thats without me making any changes

Marc

Posted by: Peter Hibbs Oct 14 2018, 03:12 PM

Hi Marc,

I have no idea why the second error happens but it sounds like a problem with the pop-up date picker which I added to this application. I suggest you remove that option from the date fields on the frmCalendarAppt form and see if it works with the Access pop-up calendar. Just delete the contents of the On Got Focus and On Lost Focus properties of all the date type fields and also set the Show Date Picker property to 'For dates'.

If you want more info about my pop-up Date Picker Demo you can find it http://www.UtterAccess.com/forum/index.php?showtopic=2034523&hl=

Good luck,

Peter.

Posted by: whybrow23 Oct 15 2018, 03:39 AM

Topman Peter

That fixed it.

Sorry, another query.

Year view screen, the section Appointment Details where it displays/imports data entered, is it easy to display more data from the same table? I've added some new fields to tblAppointments and tried following your code with the new field (see below) but I get a Compile error: Expected: Line number or label or statement or end of statement.

I'm sure it's something simple

CODE
Public Sub UpdateDetails(vApptID As Long)

'Display appointment details on screen
'Entry  (vApptID) = ApptID of appointment to display

Dim rst As Recordset

    Set rst = CurrentDb.OpenRecordset("SELECT * FROM qryAppointments WHERE ApptID = " & vApptID)
    If rst.BOF = True Then Exit Sub
    Me.txtDetails = "Appointment Times: " & Format(rst!ApptStart, "Short Time") & " to " & Format(rst!ApptEnd, "Short Time") & vbCrLf _
    & "Location: " & rst!ApptLocation & vbCrLf _
    & "Notes: " & rst!ApptNotes & vbCrLf _
    & "All Day Event: " & IIf(rst!AllDayEvent = True, "Yes", "No")
    & "Contract Code: " & rst!"ApptContract Code" & vbCrLf
    
    rst.Close
    Set rst = Nothing


End Sub

Posted by: cheekybuddha Oct 15 2018, 05:18 AM

CODE
' ...
    & "All Day Event: " & IIf(rst!AllDayEvent = True, "Yes", "No")
    & "Contract Code: " & rst!"ApptContract Code" & vbCrLf
' ...

Missing the line continuation character (and probably a new line too)
CODE
' ...
    & "All Day Event: " & IIf(rst!AllDayEvent = True, "Yes", "No") & vbCrLf _
    & "Contract Code: " & rst!"ApptContract Code" & vbCrLf
' ...


hth,

d

Posted by: Peter Hibbs Oct 15 2018, 05:54 AM

Hi Marc,

OK, try this :-

CODE
Public Sub UpdateDetails(vApptID As Long)

'Display appointment details on screen
'Entry  (vApptID) = ApptID of appointment to display

Dim rst As Recordset
    
    Me.txtDetails = ""
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM qryAppointments WHERE ApptID = " & vApptID)
    If rst.BOF = True Then Exit Sub
    Me.txtDetails = "Appointment Times: " & Format(rst!ApptStart, "Short Time") & " to " & Format(rst!ApptEnd, "Short Time") & vbCrLf _
    & "Location: " & rst!ApptLocation & vbCrLf _
    & "Notes: " & rst!ApptNotes & vbCrLf _
    & "All Day Event: " & IIf(rst!AllDayEvent = True, "Yes", "No") & vbCrLf _
    & "Contract Code: " & rst![ApptContract Code]
    rst.Close
    Set rst = Nothing

End Sub

BTW, it is NOT a good idea to have spaces in a field name (which I why I had to wrap the ApptContract Code field name in square brackets) so I would suggest you change that if you can.

Peter.

Oops, I see David beat me to it!!

Posted by: cheekybuddha Oct 15 2018, 06:42 AM

Ahh.. but I completely missed the space in the field name! blush.gif

d

Posted by: whybrow23 Oct 15 2018, 07:20 AM

Thanks guys, that fixed it.

thanks.gif

Posted by: ixxo09 Oct 18 2018, 06:40 AM

Hello everybody:

At frmCalendarAppt in Public Sub FetchAppointmentData at the line:

DoCmd.OpenForm "frmRecurrenceSelect", , , , , acDialog, vText 'select option to open single appt or series

when it opens the form, the listbox items disapear.

Any ideas to sort it out?

Posted by: Peter Hibbs Oct 18 2018, 10:32 AM

Hi ixxo09,

I don't see that happening here, the data in the list does not change when I click on a recurring appointment in the List box (which is when that line is executed). Did you implement the fix described in post #75? Not sure if that could cause this problem but it might.

Have you made any changes to the database that you downloaded?

Are we talking about the version in post #84 (which should have that bug fixed anyway)?

If you still get the problem perhaps you could upload it here so I can check it out.

Peter.

Posted by: ixxo09 Oct 18 2018, 06:00 PM

Hello Peter:

Thanks to replay.

I downloaded the application in post 84 and it does the same. Look at the attached file and then you tell me.

 

Posted by: ixxo09 Oct 18 2018, 09:12 PM

Hi Peter:

The easy way I sorted it out was moving the code of the listbox click event to the doble-click event.

CODE
Private Sub lstAppts_DblClick(Cancel As Integer)
    'User clicks on an appt in List box
    
    Me.chkUpdate = True                                                                     'set chkUpdate if user selects an existing appt for modification
    If Not IsNull(Me.lstAppts) Then FetchAppointmentData Me.lstAppts                        'enable input fields and fill with data from list box, etc

End Sub


Any other idea will be welcome.

Joe

Posted by: Peter Hibbs Oct 19 2018, 07:36 AM

Hi Joe,

OK, very odd as I don't see that behavior, I guess it must be something to do with A2016. I don't have any other suggestions so I think your fix is good and it may help someone else one day.

Peetr.

Posted by: drakopp Oct 29 2018, 05:56 AM

Good day! How can I set the calendar if the work week starts on Monday?

Posted by: Peter Hibbs Oct 29 2018, 07:08 AM

Hi drakopp,

Read the manual, the Configuring the Calendar section shows how to do that under Change the Week Start Day paragraph.

Peter Hibbs.

Posted by: drakopp Oct 29 2018, 08:25 AM

Hi, Peter!
I downloaded the version from post 84. I changed the constant, but nothing changed in the monthly and yearly form. In the version from the starting post on a monthly basis, after changing the constant, everything changes.

Posted by: Peter Hibbs Oct 29 2018, 08:59 AM

Hi drakopp,

It works for me OK. You need to close the form and open it again to see the changes.

Peter Hibbs.

Posted by: drakopp Oct 29 2018, 09:20 AM

Sorry, but did not help.


Posted by: Peter Hibbs Oct 29 2018, 09:34 AM

Hi drakopp,

OK, I see the problem, you are changing the constant in the pop-up calendar code (which is correct) but you also need to do the same thing in the main calendar code which is in module modCalendarCode like this :-

CODE
Public Const conFirstDay = vbMonday     'Set to vbSunday if first day of week to show as Sunday. Change to vbMonday, vbTuesday, etc for other days.


Hopefully that will fix the problem.

Peter Hibbs.

Posted by: drakopp Oct 29 2018, 09:40 AM

Great. Thanks, Peter.

Posted by: dzoker Jan 29 2019, 04:48 PM

Peter Hibbs,

I tried your Database 'Outlook Style Calendar V3o WB' but I'm getting a Invalid use of Null when trying to open the frmCalendarAppt? Can you help please.

ty

Posted by: Peter Hibbs Jan 29 2019, 05:46 PM

Hi ty,

You can't open that form on its own, you must open it from the main calendar form (as explained in the notes).

HTH

Peter.

Posted by: dzoker Jan 30 2019, 09:35 AM

Peter,

QUOTE
You can't open that form on its own, you must open it from the main calendar form (as explained in the notes).

Duuh, it always help to read the manual. reading.gif

I know that in one of the previous posts you said that the chart was made for only one appointment per day (as requested), hence it shows only one color but I was wondering how difficult would be to have it show multiple color bars in the same day?

Awesome work Peter and everyone else!! cheers.gif

Thank you

Posted by: Peter Hibbs Jan 30 2019, 09:59 AM

Hi Ty,

OK, good (you are not the first to make that error though).

I know that in one of the previous posts you said that the chart was made for only one appointment per day (as requested), hence it shows only one color but I was wondering how difficult would be to have it show multiple color bars in the same day? Do you mean that you want multiple color bars on the Monthly view or one of the others?

Perhaps the version in Post #58 in this thread will do what you want (and there are various other variations of the calendar in other posts). If you want something a bit different then let me know as I may have already done something similar.

Peter.

Posted by: dzoker Jan 30 2019, 10:14 AM

Peter,

QUOTE
Do you mean that you want multiple color bars on the Monthly view or one of the others?

I was asking about the chart not the Monthly calendar.

See the attached file. In this case I have an appointment from 02/02/19 - 04/12/19 (Yellow) and 05/02/19 - 04/10/19 (Blue). as you can see in the days they overlap I can only see the blue one where I need to show blue and yellow (in all view)

Maybe I'm asking too much.

TY

 Chart.bmp ( 787.07K ): 52
 

Posted by: Peter Hibbs Jan 30 2019, 12:15 PM

Hi Ty,

Yes, I think you are asking a bit too much, how would you show a chart where appointments overlap? You would need extra rows for each color (I guess if there were only ever two overlapping you double up the rows and make them smaller in height but it would be a major redesign). I think you may have to rethink how you wan't to display your data to overcome this issue.

What do you think?

Peter.

Posted by: dzoker Jan 30 2019, 01:03 PM

Peter,

QUOTE
What do you think?

I mean, the Monthly calendar view works just fine I was just wondering if the chart can do it too (I like the chart view).

I will try to incorporate this into a student database that I build and maintain and I think it would be a great upgrade to it.

Thank you for your time.

Posted by: zcarloslopes Feb 23 2019, 06:22 AM

Hi everyone!

You made a good job! I am using Access 2003, i wonder if would be possible work in access 2003, i try to do that but unfortunately i have some code troubles in this lines:

CODE
vHex = GetRGB(Nz(rst!ColorCode, 8158332))
vString = "<font style=""BACKGROUND-COLOR:#" & vHex & """>" & rst!ApptSubject & "

can someone help me please (re)code this part to 2003 compatibility?

Thanks!

Posted by: Peter Hibbs Feb 23 2019, 12:11 PM

Hi zcarloslopes,

Unfortunately this part of the demo will not work in A2003 because it uses the Rich Text field format which is not available in A2003 and earlier. The only thing I can suggest is that you upgrade your database to a later version of Access or you can use the earlier version of this demo but that does not have the option to show the different types of appointments in different colors on the Monthly calendar.

Sorry I cannot be of more help but good luck with your project.

Peter Hibbs.

Posted by: zcarloslopes Feb 24 2019, 11:44 AM

Hi Peter Hibbs,

thank you very much for your attention...

I'm sad it's not possible because i can not currently upgrade access to 2007.

so I ask if it will be possible (re)code the chart view part, cause it does not imply color and appointments at the same time, but colors only.

CODE
            vHex = GetRGB(Nz(rst!ColorCode, 8158332))
            vString = "<font style=""BACKGROUND-COLOR:#" & vHex & """>" & "       </font><br>"      'format text with back color

can you (re)code this part to 2003 compatibility please?

Thanks!

Carlos Lopes

Posted by: Peter Hibbs Feb 24 2019, 01:57 PM

Hi Carlos,

I am not sure what you mean. You cannot have different colors in the same monthly date (for the reasons I gave earlier) but you can have two or more appointments for the same date if that is what you are asking about.

What exact facilities do you want for your calendar? There are a few differerent versions available and one of those may suitable for your application. If you can explain what the calendar will be used for I may be able to provide a version which will do what you want (no guarantees though).

Peter.

Posted by: zcarloslopes Feb 24 2019, 04:08 PM

Hi Peter Hibbs,

thank you very much (again) for your attention...

the exact facilitie i want for my calendar is the chart mode.

is there any way to make it work in the 2003 version?

Thanks!

Carlos Lopes

Posted by: Peter Hibbs Feb 24 2019, 05:51 PM

Hi Carlos,

OK, just to be clear, are you referring to the chart display on the left in Post #84 https://www.UtterAccess.com/forum/index.php?showtopic=1969978&st=80

Do you just need the chart facility or do you also need the calendar displays for Month, Week and Day?

If you only need the chart display then maybe this http://www.UtterAccess.com/forum/Holiday-Planner-Demo-t2001188.html might do what you want (with some changes perhaps) and this demo does work with A2003.

I will check back tomorrow as it is getting late here.

Peter.

Posted by: zcarloslopes Feb 25 2019, 04:01 AM

Hi Peter Hibbs,,

that´s right, i´m referring to the chart display on the left in Post #84.

I need all facilities, including the calendar displays for Year, Month, Week and Day. But the Yearly facility curiously works fine with A2003 (that's why i decided try make the others facilities work too), Month, Week and Day facilities i have found them in another demos, only remaining the chart facility (the best for me).

I already took a look at "Holiday Planner Demo" (great job too), the problem is that only shows 4 weeks instead a year as chart dispaly.

Thanks!

Carlos Lopes


Posted by: Peter Hibbs Feb 25 2019, 06:04 PM

Hi Carlos,

OK, I think the Chart view can be done using Conditional Formatting or OLE Object type fields (as used in the Holiday Planner V2). However, it depends on how many different categories (i.e. colors) you want to show on the chart since CF only has a maximum of three colors in A2003.

Can you tell me how many categories you will need in your application?

I can probably add the Chart view to the Calendar (using an old A2003 version) if that is what you want but it could take a while as I have other projects to look after at the moment. Perhaps also you could explain in more detail what this database will be used for so I have a better idea of what the form should look like.

Let me know what you think.

Peter.

Posted by: zcarloslopes Feb 26 2019, 04:00 AM

Hi Peter Hibbs,

Thank you very much, I'm very glad that you can help me on this project.

I need 5 categories in my application. Anything like the Chart view will be perfect.

Take the time you need. I've been working on other parts of the project.

Thanks!

Carlos Lopes

Posted by: Peter Hibbs Mar 1 2019, 09:40 AM

Hi Carlos,

OK, try the attached new version below and let us know what you think. The screen shots below show the Yearly view and the Year Chart view.

There are a few things that you should be aware of like :-
When you split the database into a Front-End and Back-End files (which you should before rolling it out to your users) you must keep the three temporary tables (tblChartDate, tblMonthData and tblWeekdata) in the front-end file, the other tables (tblAppointments and tblCategories) can go in the Back-End file, as normal.

I have actually provided 7 different categories but if you don't need them all, then just hide the references and controls of the ones you don't want.
I have named the categories Category 01 to Category 07 so you need to open the Categories table and change the CategoryName field to something more suitable.
You can also change the category colors if you wish. To do this, open the Categories table and double click on the LineImage field for the required category which should open the Windows Paint program. You can then change the color of the image (using the Paint Pot tool is easiest) and save and close the Paint program. Or you could even change the design of the image if you want something different on the chart.
Note, however, that the LineColor field (which is used to show the color cells on the Yearly view) holds the numerical value of the color but this is not directly linked to the LineImage field. In other words, if you change the color of the LineImage then you will also need to work out what the numeric code is for that color and insert that value into the LineColor field.

There are a number of settings that you can change that will affect the way that the calendar forms work and you can set those flags by opening the module modCalendarCode and changing the various parameters at the top of the module.

If you have any questions or problems, feel free to post back.

Peter.

 

 Outlook_Style_Calendar_V3p.zip ( 683.7K ): 77
 

Posted by: zcarloslopes Mar 1 2019, 12:08 PM

Hi Peter!

Thank you so much for the excellent work. I really appreciate it.

I took a quick look and there are two things I would like to ask you if it is possible.

In the Monthly view when "Select Category" the colors do not change, they always turn yellow..

and in the Year Chart view the remaining days of the months (i.e. 31) could be the background color gray?

I'll take a closer look...

Thanks!

Carlos Lopes

Posted by: Peter Hibbs Mar 1 2019, 06:02 PM

Hi Carlos,

Hmmm.. I thought you might come back with those two questions. I think this could be difficult but I will have a look and see if it is possible (or practical). Could take a few days though.

Peter.

Posted by: Peter Hibbs Mar 3 2019, 11:21 AM

Hi Carlos,

OK, try this version (I haven't been able to test it with A2003 but I think it should work OK).

Couple more things you should know -

I have added a new table called tblOLEObjects which holds just one OLE Object type field for the grey boxes on the Year Chart form. This table can also stay in the Front-End file when you split the database since the users would not normally be allowed to make any changes to the data in it.

I have added the category color codes to the Monthly calendar, as requested, using OLE Object type fields for each date box (i.e. an extra 7 fields in the tblMonthData table) which are positioned beneath the text fields on the calendar. This does make the form flicker a bit more but I don't think there is any way to stop that. I think that some of the colors may be a bit dark where you have text on them so it might be better to make them a bit lighter but I will leave that to you to do, if you feel it is necessary.

I have also added a Combo box for the categories to the Appointment Schedules form so that you can set the required category for a new appointment or change an existing one if you should need to.

Also note that I have not added the colors to the Monthly printed report as I did not know if you used that facility although I guess it could be done, if really required.

And, as I am sure you already know, this calendar only works properly if you have only one appointment category per day because it is not possible to have multiple colors displayed in the same date cell.

Anyway, let us know what you think and if you have any problems.

Peter.



 

 Outlook_Style_Calendar_V3p__V2_.zip ( 723.99K ): 81
 

Posted by: zcarloslopes Mar 4 2019, 07:02 AM

Hi Peter!

Thank you so much for the excellent work.

I took a quick look and everything seems to work well. I'll take a closer look today.

This demo will help me immensely in the work that I am developing at this moment. I really appreciate it.

I have no words to thank you.

Carlos Lopes

Posted by: Peter Hibbs Mar 4 2019, 04:41 PM

Hi Carlos,

yw.gif

Peter.

Posted by: K356 Mar 4 2019, 06:20 PM

Hi Peter,

Thank you very much for posting this demo. I customized it to use as a leave calendar for my department and I think it will work well. We only needed a monthly view so that simplified the customization process. The methods you used to generate the calendar views are impressive and I learned a great deal going through your code. All of your documentation made it easy to understand and modify. I think it's awesome that this thread is still going and you've been so responsive and helpful.

Thank you,
Arlie


Posted by: Peter Hibbs Mar 5 2019, 04:20 AM

Hi Arlie,

Thank you for your kind comments, it is always nice to hear from users that have found these demos useful.

I am just wondering if this https://www.UtterAccess.com/forum/index.php?showtopic=2001188 demo would fit your requirements better (or maybe you have already come across it and rejected it). Anyway, it is just a thought!

Peter.

Posted by: zcarloslopes Mar 9 2019, 04:10 PM

Hi Peter,

I have more extensively tested the demo and everything looks perfect. It's very interesting how you coding..

I have tried to understand the code to make my adaptations..

There is only one thing I would like to ask you.., to make the demo more appealing ..

It is possible, on the Year Chart form, backcoloring Saturdays and Sundays (i.e. light blue) as the remaining days of the months?

Thank you so much for everything.

Carlos Lopes

Posted by: Peter Hibbs Mar 10 2019, 12:29 PM

Hi Carlos,

I am very happy that you are finding the demo useful.

Regarding your request that you would like the week end days colored in light blue on the Year Chart, I have come up with a solution although it was quite complicated. Hopefully this will do what you want but you may need to make some small changes, see screen shot below.

To get this to work I had to add 31 new Text fields to the table tblChartData and on the form frmCalendarChart I positioned these new fields behind the 31 OLE Object type controls. The idea is that the 31 new Text controls will show the color of the weekend days and the OLE Object fields will show the color bars as before. I reduced the height of the OLE Object controls and postioned them centrally in each row so that the display looks much the same as the previous version. I also had to change the 7 images in table tblCategories so that the color image filled the control (rather than having a white strip at top and bottom of the image) and you may need to do the same if you copying thiese changes to your own database.

The 31 new Text controls are bound to the table tblChartData and the font for these controls is set to Terminal (14 point) which has a square block character which is used to fill the control on the form with a light blue block. The relevant controls are filled with these characters with some vBA code whenever a new month is selected, you can see the code if you search for the characters @@@.

If you have any questions or problems then post back here.

Good luck,

Peter.


 

 Outlook_Style_Calendar_V3p__V3_.zip ( 743.11K ): 53
 

Posted by: zcarloslopes Mar 11 2019, 01:38 PM

Hi Peter,

Thank you so much for the fast and amazing work. it's "almost" perfect...

I took a look and noticed that when you reduced the height of the OLE Object controls,
the gray controls (remaining days of the month) looks like an extra color of categories, instead as controls of the remaining days.

Is there any way to color grey the 31 new Text fields for the the remaining days of the month as did you do with
the weekend days instead the OLE Object controls?

Thank you

Carlos

Posted by: Peter Hibbs Mar 12 2019, 09:57 AM

Hi Carlos,

OK, try this one (I haven't tested it with A2003 so you will just have to see if there are any problems with that version, but hopefully not).

What I have done with this version is - I have used Conditional Formatting to color the foreground and background colors of the text boxes in blue or grey (see screen shot below). What the VBA code does is this - for any weekend date fields it copies the character 'b' to the chart table and for any unused dates for each month it copies the character 'g' to the field. Then in the Conditional Formatting form it uses those codes to color the controls blue or grey (A2003 can use up to 3 colors so you should be OK with this). I have also changed the font back to Tahoma so you don't need the Terminal font any more. It actually looks better using this method because there are no white outlines around the colors but it does make the screen flicker a bit more during screen updates, but I don't think there is anything we can do about that.

Anyway, let me know what you think.

Peter.

 

 Outlook_Style_Calendar_V3p__V4_.zip ( 741.73K ): 96
 

Posted by: zcarloslopes Mar 13 2019, 01:58 PM

Hi Peter!

Thank you so much for the new version. It works fine with A2003. Great work. I do not care about the screen flicker.

This demo is extremely useful for my object.

I'll give you feedback if I detect any problems.

Carlos

Posted by: Peter Hibbs Mar 13 2019, 04:09 PM

Hi Carlos,

yw.gif

Let me know if you get any problems.

Peter.

Posted by: wornout Mar 14 2019, 12:09 PM

Hi Peter is there anyway to format the text entries to red if they contain the word birthday.
I have also got Peter Hibb's birthday through out the calendar and would like to make the word birthday red would be really good if I could do the 2 words before it so the words of Peter Hibb's birthday is in red.
The entries come from my outlook calendar when I import from outlook to calendar

Posted by: Peter Hibbs Mar 14 2019, 06:13 PM

Hi wornout,

Which version of the calendar are you referring to (there have been several) and which calendar view would you want this on, Monthly, Weekly or Daily?

Will have a look tomorrow, if I can.

Peter.

Posted by: basehumax Mar 14 2019, 08:51 PM

Hi

i am getting this i dont why ambiguous name detected : quote see the screenshot please help thanks

 

Posted by: Peter Hibbs Mar 15 2019, 11:43 AM

Hi basehumax,

Well I don't know but I would guess that you have the constant QUOTE defined twice in the database. Try searching the VBA modules for the word QUOTE and see if it appears twice as a Constant declaration at the top of two different VBA modules.

If it does, and the definition is the same for both (i.e. Public Const QUOTE = """") then you can delete one of them. If it does but they are different then you will need to change one of them to a different name and then change the code to match that new name where it applies.

HTH

Peter Hibbs.

Posted by: basehumax Mar 16 2019, 12:33 AM

Hi peter

Thanks for you replay i am still confused please help here is the codes

Option Compare Database
Option Explicit

Dim vDateTime As Date

Private Sub cboCategory_AfterUpdate()
Me.lblCategory.BackColor = Me.cboCategory.Column(2)
End Sub

Private Sub cboEndTime_AfterUpdate()

If Me.cboEndTime = 0 Then 'if user selects midnight as End Time then @@@ Add these 3 lines @@@
Me.txtEndDate = Me.txtEndDate + 1 'inc Date to next day
End If
Me.chkSave = True

End Sub

Private Sub cboEndTime_KeyPress(KeyAscii As Integer)
If Chr(KeyAscii) = ";" Or Chr(KeyAscii) = "," Or Chr(KeyAscii) = "." Then KeyAscii = Asc(":") 'allow users to enter ; or , or . instead of :
If Chr(KeyAscii) Like "[!0-9:]" And KeyAscii <> vbKeyBack Then KeyAscii = 0
End Sub

Private Sub cboStartTime_AfterUpdate()

Me.cboEndTime = DateAdd("n", conPeriod, Me.cboStartTime)
Me.chkSave = True

End Sub

Private Sub cboStartTime_KeyPress(KeyAscii As Integer)
If Chr(KeyAscii) = ";" Or Chr(KeyAscii) = "," Or Chr(KeyAscii) = "." Then KeyAscii = Asc(":") 'allow users to enter ; or , or . instead of :
If Chr(KeyAscii) Like "[!0-9:]" And KeyAscii <> vbKeyBack Then KeyAscii = 0
End Sub

Private Sub chkAllDay_AfterUpdate()

If Me.chkRecurSingle = False Then 'if chkRecurSingle flag = True then keep time combos hidden
Me.cboStartTime.Visible = Not Me.chkAllDay
Me.cboEndTime.Visible = Not Me.chkAllDay
End If
Me.chkSave = True

End Sub

Private Sub cmdClose_Click()

Dim vRet As Long

If Me.chkSave = True Then
vRet = MsgBox("Do you want to save changes?", vbExclamation + vbYesNoCancel, "Save Appointment")
If vRet = vbCancel Then Exit Sub
If vRet = vbYes Then
Exit Sub 'save appt, abort if error
End If
End If
DoCmd.Close acForm, Me.Name

End Sub

Private Sub cmdDelete_Click()

'Delete selected appointment record

If Nz(Me.txtPattern) <> "" And Me.chkRecurSingle = True Then 'if recurring appt and sequence mode then
If MsgBox("Are you sure you want to delete this appointment and ALL the associated recurring appointments?", vbQuestion + vbYesNo, "Delete Recurring Appointments") = vbNo Then Exit Sub
CurrentDb.Execute "DELETE FROM tblAppointments WHERE RecurrenceID = " & Me.lstAppts.Column(9) 'delete all recurring records in group
Else
If MsgBox("Are you sure you want to delete this appointment?", vbQuestion + vbYesNo, "Delete Appointment") = vbNo Then Exit Sub 'request confirmation
CurrentDb.Execute "DELETE FROM tblAppointments WHERE ApptID = " & Me.txtAppointmentID 'delete session record
End If
gDummy = 1 'return 1 (refresh screen on return)
DoCmd.Close acForm, Me.Name 'and close form

End Sub

Private Sub cmdRecur_Click()

'Open form frmRecurrences and select a recurring pattern

Dim vDate As Date
Dim vDuration As Long

gDummy = Me.txtPattern 'save current pattern (if any)
DoCmd.OpenForm "frmRecurrences", , , , , acDialog, Me.txtStartDate & " " & Me.cboStartTime & "," & Me.txtEndDate & " " & Me.cboEndTime & "," & Me.lstAppts.Column(9)
If gDummy <> Nz(Me.txtPattern) Then 'if recurrence pattern changed then
Me.txtPattern = gDummy 'update txtPattern with new pattern
If gDummy <> "X" Then 'if user chose to keep recurrences then
vDate = Mid(Me.txtPattern, 1, 16) 'fetch new Start Date & Time
Me.txtStartDate = DateValue(vDate) 'and set Start Date
vDuration = Mid(Me.txtPattern, 29, 5) 'fetch appt duration (in minutes)
Me.txtEndDate = DateValue(DateAdd("n", vDuration, vDate)) 'calc End Date of appt
Me.cboEndTime = TimeValue(DateAdd("n", vDuration, vDate)) 'calc End Time of appt
Me.chkRecurSingle = True 'set Recurring mode
End If
Me.chkSave = True 'set Save flag
RecurringMode 'set up recurring/normal conditions
End If

End Sub

Private Sub cmdNew_Click()

'Create a new appointment record

Me.chkUpdate = False 'clear chkUpdate flag if user is starting a new appt
ClearControls 'reset main controls
EnableFields True 'enable input fields
Me.txtAppointmentID = 0 'reset AppointmentID
Me.lstAppts = Null 'clear any selections in list box
Me.txtStartDate.SetFocus 'move focus to Start Date

Me.cmddelete.Enabled = False
Me.cmdSave.Enabled = False

End Sub

Private Sub cmdSave_Click()

Dim vCategoryID As Long


If Nz(Me.cboCategory) = "" Then 'if 'All Categories' mode then
DoCmd.OpenForm "frmCategorySelect", , , , , acDialog 'open frmCategorySelect form for user to select a Category
If gDummy = 0 Then Exit Sub 'abort if user cancels
Me.cboCategory = gDummy 'copy CategoryID to vCategoryID
End If






' If Me.txtCategoryID = "*" Then 'if 'All Categories' mode then
' If Me.chkUpdate = False Then 'if user starting new appt then
' DoCmd.OpenForm "frmCategorySelect", , , , , acDialog 'open frmCategorySelect form for user to select a Category
' If gDummy = 0 Then Exit Sub 'abort if user cancels
' vCategoryID = gDummy 'copy CategoryID to vCategoryID
' Else 'if user is changing an existing appt then
' vCategoryID = Me.lstAppts.Column(13) 'fetch CategoryID for selected appt
' End If
' Else 'if Category specified then
' vCategoryID = Me.txtCategoryID 'copy Me.txtCategoryID to vCategoryID
' End If

If SaveAppointment(Nz(Me.cboCategory)) = True Then Exit Sub 'save appt, abort if error
DoCmd.Close acForm, Me.Name 'and close form

End Sub

Private Sub Form_Open(Cancel As Integer)

'OpenArgs = (Date & Time) + (Offset from top of calling form) + (CategoryID)

Dim vArray() As String
Dim vEndTime As Date

On Error GoTo ErrorCode

vArray = Split(OpenArgs, ",")

vDateTime = CDate(vArray(0)) 'fetch appointment date/time from OpenArgs
Me.Move Me.WindowLeft, vArray(1) 'adjust form vertical posn to fit on Calendar form
Me.txtCategoryID = vArray(2) 'fetch CategoryID (or * if all categories)

ComboBoxTimes Me.cboStartTime, "00:00:00", conPeriod 'set up time slot times for StartTime
ComboBoxTimes Me.cboEndTime, "00:" & conPeriod & ":00", conPeriod 'and EndTime (+ conPeriod for end times)

vEndTime = DateAdd("n", 1, vDateTime) 'set vEndTime to EndDate + 1 second
Me.txtStartDate = DateValue(vDateTime) 'show default appt start date
Me.txtEndDate = DateValue(vDateTime) 'show default appt end date
Me.cboStartTime = TimeValue(vDateTime) 'and default Start Time
Me.cboEndTime = DateAdd("n", conPeriod, Me.cboStartTime) 'and default End Time
Me.txtApptDate = Format(Me.txtStartDate, "dddd") & " " & Format(Me.txtStartDate, "Long Date") 'update main date field
Me.lstAppts.Requery

If vArray(2) = "*" Then
Me.txtApptDate = Me.txtStartDate & " for All Categories"
Else
Me.txtApptDate = Me.txtStartDate & " for " & DLookup("CategoryName", "tblCategories", "CategoryID Like '" & Me.txtCategoryID & "'") 'show date AND Category name
Me.cboCategory = vArray(2) 'set default Category to current category selection
End If

gDummy = 0 'return 0 (no changes made)
Exit Sub

ErrorCode:
MsgBox err.Description

End Sub

Private Sub lstAppts_Click()

'User clicks on an appt in List box

Me.chkUpdate = True 'set chkUpdate if user selects an existing appt for modification
FetchAppointmentData Me.lstAppts 'enable input fields and fill with data from list box, etc

End Sub

Public Sub FetchAppointmentData(vApptID As Long)

'Copy selected appt data from list box to text fields

Dim vText As String

Me.txtAppointmentID = vApptID 'update txtAppointmentID
EnableFields True 'enable input fields
Me.cmdSave.Enabled = True 'enable Save btn
Me.cmddelete.Enabled = True 'enable Delete btn
Me.cmdRecur.Enabled = True 'enable Recurrence btn

Me.txtStartDate = DateValue(Me.lstAppts.Column(1)) 'and Start Date
Me.txtEndDate = DateValue(Me.lstAppts.Column(2)) 'and End Date
Me.cboStartTime = TimeValue(Me.lstAppts.Column(1)) 'and Start Time
Me.cboEndTime = TimeValue(Me.lstAppts.Column(2)) 'and End Time
Me.txtSubject = Me.lstAppts.Column(4) 'and subject
Me.txtLocation = Me.lstAppts.Column(5) 'and location
Me.cboCategory = Me.lstAppts.Column(13) 'and Category
Me.lblCategory.BackColor = Me.lstAppts.Column(14) 'and Category color

Me.txtNotes = Nz(DLookup("ApptNotes", "tblAppointments", "ApptID = " & Me.txtAppointmentID)) 'fetch notes from table (use DLookup coz List box cannot store > 255 chrs)
Me.chkSave = False 'reset Save flag if changing appts

If Me.lstAppts.Column(10) = False Then 'if NOT All Day Event then
Me.chkAllDay = False 'clear chk box
Else 'if All Day Event then
Me.chkAllDay = True 'tick chk box
Me.cboStartTime.Visible = False 'hide Time combos
Me.cboEndTime.Visible = False
End If

If Me.lstAppts.Column(9) > 0 Then 'if user selects a recurring appt
vText = "This is one appointment in a series." & vbCrLf & "What do you want to open?"
DoCmd.OpenForm "frmRecurrenceSelect", , , , , acDialog, vText 'select option to open single appt or series
If gDummy = 1 Then 'select type
Me.chkRecurSingle = True 'flag as Series
Else
Me.chkRecurSingle = False 'flag as Single
Me.cmdRecur.Enabled = False 'disable Recurrence btn for a Single appt of a recurring series
End If
Me.txtPattern = Me.lstAppts.Column(8) 'fetch recurring pattern from table
Else
Me.txtPattern = "" 'set Normal appt mode
Me.chkRecurSingle = False 'reset flag if not Recurring Series appts
End If

Me.Label34.Visible = False
RecurringMode 'enable Recurring mode

End Sub

Public Sub EnableFields(vMode As Boolean)

'Reset input fields to default values and disable
'Entry (vMode) = True to enable controls, = False to disable controls

Me.cboStartTime.Enabled = vMode 'enable (and unhide) main input controls
Me.cboStartTime.Visible = vMode
Me.cboEndTime.Enabled = vMode
Me.cboEndTime.Visible = vMode
Me.txtStartDate.Enabled = vMode
Me.txtStartDate.Visible = vMode
Me.txtEndDate.Enabled = vMode
Me.txtEndDate.Visible = vMode
Me.txtSubject.Enabled = vMode
Me.txtLocation.Enabled = vMode
Me.txtNotes.Enabled = vMode
Me.chkAllDay.Enabled = vMode
Me.chkAllDay.Visible = vMode
Me.cboCategory.Enabled = vMode
If Nz(Me.cboCategory.Column(2)) <> "" Then Me.lblCategory.BackColor = Me.cboCategory.Column(2)

End Sub

Private Sub txtEndDate_AfterUpdate()
Me.chkSave = True 'reset Save flag if changing appts
End Sub

Private Sub txtLocation_AfterUpdate()

Me.cmdSave.Enabled = True
Me.txtLocation = Replace(Me.txtLocation, """", "''") 'replace any double quotes with two single quotes
Me.chkSave = True 'reset Save flag if changing appts

End Sub

Private Sub txtNotes_AfterUpdate()

Me.cmdSave.Enabled = True
Me.txtNotes = Replace(Me.txtNotes, """", "''") 'replace any double quotes with two single quotes
Me.chkSave = True 'reset Save flag if changing appts

End Sub

Private Sub txtStartDate_AfterUpdate()
Me.chkSave = True 'reset Save flag if changing appts
End Sub

Private Sub txtSubject_AfterUpdate()

Me.txtSubject = Replace(Me.txtSubject, """", "''") 'replace any double quotes with two single quotes
Me.cmdSave.Enabled = True
Me.chkSave = True 'reset Save flag if changing appts

End Sub

Public Function CheckDates() As Boolean

'Check if the selected end date/time is later than the selected start date/time, return True if error

Dim vNewStart As Date, vNewEnd As Date

vNewStart = Me.txtStartDate & " " & Me.cboStartTime 'combine new start date and time
vNewEnd = Me.txtEndDate & " " & Me.cboEndTime 'combine new end date and time
If vNewEnd <= vNewStart Then 'if End Date/Time <= Start Date/Time then
Beep
MsgBox "ERROR. The Start Date and Time must be earlier than the End Date and Time. Please enter new values for the date/time fields.", vbCritical + vbOKOnly, "Invalid Date/Time Fields"
CheckDates = True 'return True if dates/times in error
Exit Function
End If
Me.cmdSave.Enabled = True 'enable Save btn if dates & times valid

End Function

Public Function SaveAppointment(vCategoryID As Long) As Boolean

'Save new or amended appointment data to tblAppointments table
'Entry (vCategoryID) = CategoryID of category for the appt

Dim vAppointmentID As Long, vRecurID As Long, vDuration As Long, vCount As Long
Dim vNewStart As Date, vNewEnd As Date
Dim vApptInfo As String

On Error GoTo ErrorCode

'Check if appt start time is earlier than appt end time
If CheckDates = True Then SaveAppointment = True: Exit Function 'return True if error found

'Check if Subject field empty and error if it is
If Nz(Me.txtSubject) = "" Then 'if no Subject text then
Beep
MsgBox "ERROR, you must enter some text in the Subject field before saving the appointment.", vbCritical + vbOKOnly, "Invalid Subject Field"
Me.txtSubject.SetFocus 'move focus to Subject field
SaveAppointment = True
Exit Function 'and exit
End If

If Me.chkAllDay = False Then 'if normal event (not All Day) then
vNewStart = Me.txtStartDate + Me.cboStartTime 'combine new start date and time
vNewEnd = Me.txtEndDate + Me.cboEndTime 'combine new end date and time
Else 'if an All Day Event then
vNewStart = Me.txtStartDate + TimeValue(conFirstTime) 'set Start Date to Start Date + conFirstTime
vNewEnd = Me.txtEndDate + TimeValue(conLastTime) 'set End Date to End Date + LastTime
vNewEnd = DateAdd("n", conPeriod, vNewEnd) 'and add appt period in conPeriod
End If

'Check if new (or amended) appointment overlaps an existing appointment
If conMultiAppts = 1 Then 'if multiple appts for a time slot is NOT allowed then
If Nz(Me.txtPattern) = "" Or Me.chkRecurSingle = False Then 'if normal Single appt or recurring appt in single mode then
vAppointmentID = AppointmentsCheck(vNewStart, vNewEnd, Me.txtAppointmentID, 0, vCategoryID) 'fetch ID of overlapped appt (if any)
If vAppointmentID > 0 Then 'if function returns value >0 then
vApptInfo = DLookup("ApptSubject & Chr(13) & Chr(10) & 'Date/Time = ' & ApptStart & ' to ' & ApptEnd", "tblAppointments", "ApptID = " & vAppointmentID) 'fetch info on existing appt
MsgBox "ERROR. This new appointment overlaps an existing appointment :-" & vbCrLf & "Subject = " & vApptInfo & vbCrLf & vbCrLf & "Please amend dates and/or times and try again.", vbCritical + vbOKOnly, "Invalid Appointment Times"
SaveAppointment = True
Exit Function
End If
End If
End If

If Me.txtPattern = "X" Then 'if user Removes recurring appt then convert recurring appt back to single appt
vRecurID = Me.lstAppts.Column(9) 'fetch current RecurrenceID value
vApptInfo = Me.lstAppts.Column(8) 'fetch original recurrence pattern
CurrentDb.Execute "DELETE FROM tblAppointments WHERE RecurrenceID = " & vRecurID 'delete all recurring appts first
vNewStart = Mid(vApptInfo, 1, 16) 'and fetch original Start Date/Time
vDuration = Mid(vApptInfo, 29, 5) 'and original duration (in minutes)
vNewEnd = DateAdd("n", vDuration, vNewStart) 'add duration to vNewStart to get original End Date/time

CurrentDb.Execute "INSERT INTO tblAppointments (ApptSubject, ApptLocation, ApptStart, ApptEnd, ApptNotes, CategoryID) VALUES (" _
& QUOTE & Me.txtSubject & QUOTE & ", " _
& QUOTE & Me.txtLocation & QUOTE & ", " _
& Format(vNewStart, "\#mm\/dd\/yyyy hh\:nn\#") & ", " _
& Format(vNewEnd, "\#mm\/dd\/yyyy hh\:nn\#") & ", " _
& QUOTE & Me.txtNotes & QUOTE & ", " _
& vCategoryID & ")" 'and insert new record into tblAppointments
Else
'If txtAppointmentID = 0 then add a new appointment, if txtAppointmentID > 0 then amend the existing appointment
If Me.txtAppointmentID = 0 Then 'if no existing appointment record found then
If vNewStart < Now Then 'if new appt time is earlier than Now() then
Beep
If MsgBox("WARNING. This appointment is in the past, do you really want to create this appointment now?", vbQuestion + vbYesNo, "Invalid Appointment Time") = vbNo Then Exit Function
End If

If Nz(Me.txtPattern) = "" Then 'if normal Single appt then
CurrentDb.Execute "INSERT INTO tblAppointments (ApptSubject, ApptLocation, ApptStart, ApptEnd, AllDayEvent, ApptNotes, CategoryID) VALUES (" _
& QUOTE & Me.txtSubject & QUOTE & ", " _
& QUOTE & Me.txtLocation & QUOTE & ", " _
& Format(vNewStart, "\#mm\/dd\/yyyy hh\:nn\#") & ", " _
& Format(vNewEnd, "\#mm\/dd\/yyyy hh\:nn\#") & ", " _
& IIf(Me.chkAllDay = True, -1, 0) & ", " _
& QUOTE & Me.txtNotes & QUOTE & ", " _
& vCategoryID & ")" 'and insert new record into tblAppointments
Else 'if Recurring appt then
vCount = InsertRecurringAppts(Me.txtPattern, vRecurID, False, vCategoryID) 'count number of appts that will clash
If vCount > 0 Then 'if any found then
SaveAppointment = True
Me.chkSave = False
Beep
If MsgBox("WARNING. There are " & vCount & " scheduled appointments which will clash with the new recurring appointments, do you want to schedule the new appointments anyway?", vbQuestion + vbYesNo, "Overlapping Appointments Warning") = vbNo Then Exit Function
End If
vRecurID = Nz(DMax("RecurrenceID", "tblAppointments"), 0) + 1 'calc next RecurrenceID value
InsertRecurringAppts Me.txtPattern, vRecurID, True, vCategoryID 'add new recurring appt records
End If
Else 'if changing an existing appointment record then
If Nz(Me.txtPattern) = "" Or Me.chkRecurSingle = False Then 'if normal Single appt or recurring appt in single mode then
CurrentDb.Execute "UPDATE tblAppointments SET " _
& "ApptSubject = " & QUOTE & Me.txtSubject & QUOTE & ", " _
& "ApptLocation = " & QUOTE & Me.txtLocation & QUOTE & ", " _
& "ApptStart = " & Format(vNewStart, "\#mm\/dd\/yyyy hh\:nn\#") & ", " _
& "ApptEnd = " & Format(vNewEnd, "\#mm\/dd\/yyyy hh\:nn\#") & ", " _
& "ApptNotes = " & QUOTE & Me.txtNotes & QUOTE & ", " _
& "AllDayEvent = " & IIf(Me.chkAllDay = True, -1, 0) & ", " _
& "CategoryID = " & vCategoryID & " " _
& "WHERE ApptID = " & Me.txtAppointmentID 'update existing appointment with amended data
Else 'if updating Recurring appt then
vRecurID = Me.lstAppts.Column(9) 'fetch current RecurrenceID value
If vRecurID = 0 Then 'if there is no RecurrenceID (i.e. non-recurring appt converted to recurring appt) then
vRecurID = Nz(DMax("RecurrenceID", "tblAppointments"), 0) + 1 'calc next RecurrenceID value
CurrentDb.Execute "DELETE FROM tblAppointments WHERE ApptID = " & Me.txtAppointmentID 'and delete existing appt
End If

vCount = InsertRecurringAppts(Me.txtPattern, vRecurID, False, vCategoryID) 'count number of appts that will clash
If vCount > 0 Then 'if any found then
SaveAppointment = True
Me.chkSave = False
Beep
If MsgBox("WARNING. There are " & vCount & " scheduled appointments which will clash with the new recurring appointments, do you want to schedule the new appointments anyway?", vbQuestion + vbYesNo, "Overlapping Appointments Warning") = vbNo Then Exit Function
End If
CurrentDb.Execute "DELETE FROM tblAppointments WHERE RecurrenceID = " & vRecurID 'first delete all existing recurring appts
InsertRecurringAppts Me.txtPattern, vRecurID, True, vCategoryID 'add new recurring appt records
End If
End If
End If
gDummy = 1 'return 1 (refresh screen on return)
SaveAppointment = False
Exit Function

ErrorCode:
MsgBox err.Description
SaveAppointment = False

End Function

Public Function InsertRecurringAppts(vPattern As String, vRecurrenceID As Long, vMode As Boolean, vCategoryID As Long) As Long

'Insert new appt record for each recurring appt required
'Entry (vPattern) defines recurring pattern sequence
' (vRecurrenceID) = RecurrenceID to be used
' (vMode) = Check or Insert mode. (Check mode = False - Returns number of overlapping appts. = True - Inserts new appointments)
' (vCategoryID) = CategoryID of category for the appt
'Exit (InsertRecurringAppts) = If in Check mode then returns number of overlapping appts.

Dim vCount As Long, vMax As Long, vDuration As Long, vDays As Long, vAppts As Long, vApptID As Long
Dim vNewStart As Date, vNewEnd As Date, vTime As Date

vMax = Mid(vPattern, 35, 3) 'fetch number of appts to create from pattern string
vNewStart = Mid(vPattern, 1, 10) 'and fetch Start Date
vTime = Mid(vPattern, 12, 5) 'fetch Start Time
vDuration = Mid(vPattern, 29, 5) 'and duration (in minutes)
If vDuration < 1440 Then 'if appt duration is less than 1 day then
If Me.chkAllDay = True Then 'if also All Day Event then
vNewStart = Mid(vPattern, 1, 10) 'and fetch Start Date
vNewEnd = vNewStart + TimeValue(conLastTime) 'add LastTime to End Date
vNewEnd = DateAdd("n", conPeriod, vNewEnd) 'and add appt period in conPeriod
vNewStart = vNewStart + TimeValue(conFirstTime) 'add start time to start date
Else
vNewStart = vNewStart + vTime 'add start time to start date
vNewEnd = DateAdd("n", vDuration, vNewStart) 'add duration to vNewStart to get End Date/time
End If
Else 'if appt duration is 1 day or longer then
Me.chkAllDay = True 'and set All day mode
vNewStart = vNewStart + TimeValue(conFirstTime) 'add conFirstTime to start date
vNewEnd = DateAdd("n", vDuration, vNewStart) 'add duration to vNewStart to get End Date/time
vNewEnd = DateValue(vNewEnd) + TimeValue(conLastTime) 'set End Time to conLastTime
vNewEnd = DateAdd("n", conPeriod, vNewEnd) 'and add appt period in conPeriod
vNewEnd = vNewEnd - 1 'subtract 1 day
End If

vAppts = 0 'zero appt counter
For vCount = 1 To vMax 'for each recurring appt
If vMode = False Then 'if in Check mode then
If conMultiAppts = 1 Then 'if multiple appts for a time slot is NOT allowed then
vApptID = AppointmentsCheck(vNewStart, vNewEnd, 0, vRecurrenceID, vCategoryID) 'fetch ID of overlapped appt (if any)
If vApptID > 0 Then vAppts = vAppts + 1 'inc appt counter
End If
Else 'if in Insert mode then
CurrentDb.Execute "INSERT INTO tblAppointments (ApptSubject, ApptLocation, ApptStart, ApptEnd, AllDayEvent, ApptNotes, RecurrenceID, Pattern, CategoryID) VALUES (" _
& QUOTE & Me.txtSubject & QUOTE & ", " _
& QUOTE & Me.txtLocation & QUOTE & ", " _
& Format(vNewStart, "\#mm\/dd\/yyyy hh\:nn\#") & ", " _
& Format(vNewEnd, "\#mm\/dd\/yyyy hh\:nn\#") & ", " _
& IIf(Me.chkAllDay = True, -1, 0) & ", " _
& QUOTE & Me.txtNotes & QUOTE & ", " _
& vRecurrenceID & ", " _
& "'" & vPattern & "', " _
& vCategoryID & ")" 'and insert new record into tblAppointments
End If
vDays = CalcNextDate(DateValue(vNewStart), vPattern) 'fetch No of days until next date
vNewStart = vNewStart + vDays 'add days count to start date
vNewEnd = vNewEnd + vDays 'and end date
Next
InsertRecurringAppts = vAppts 'return overlapping appt count

End Function

Public Sub RecurringMode()

'Activate/Deactivate Recurring mode
'Entry (Me.txtPattern) = holds recurrence pattern or NULL
' (Me.chkRecurSingle) = False (Single pattern) or = True (Series Appt)

Dim vDuration As Long

If Nz(Me.txtPattern) = "" Or Nz(Me.txtPattern) = "X" Then 'if Pattern = '' or 'X' then
Me.txtStartDate.Visible = True
Me.cboStartTime.Visible = True
Me.txtEndDate.Visible = True
Me.cboEndTime.Visible = True
Me.chkAllDay.Visible = True
Else
If Me.chkRecurSingle = True Then
Me.txtStartDate.Visible = False
Me.cboStartTime.Visible = False
Me.txtEndDate.Visible = False
Me.cboEndTime.Visible = False
Else
Me.txtStartDate.Visible = True
Me.cboStartTime.Visible = True
Me.txtEndDate.Visible = True
Me.cboEndTime.Visible = True
End If
End If

If Nz(Me.txtPattern) = "" Or Nz(Me.txtPattern) = "X" Then 'if Pattern = '' or 'X' then
Me.Label34.Visible = False 'hide recurrence info
Else
Me.Label34.Visible = True 'show recurrence info
Me.Label34.Caption = "Recurrence: " & SetRecurringCaption(Me.txtPattern) 'display recurring pattern on form
vDuration = Mid(Me.txtPattern, 29, 5) 'fetch duration (in minutes)
If vDuration < 1440 Or Me.chkRecurSingle = False Then Me.chkAllDay.Visible = True Else Me.chkAllDay.Visible = False 'if appt duration is less than 1 day then display All day Event box
End If

If Me.chkAllDay = True Then 'if All Day Event then
Me.cboStartTime.Visible = False 'hide Time combos
Me.cboEndTime.Visible = False
End If

End Sub

Public Sub ClearControls()

'clear main controls

Me.txtAppointmentID = 0 'reset AppointmentID
Me.lstAppts = Null 'clear any selections in list box
Me.txtSubject = ""
Me.txtLocation = ""
Me.txtNotes = ""
Me.txtPattern = ""
Me.cmddelete.Enabled = False
Me.cmdSave.Enabled = False
Me.cmdRecur.Enabled = True
Me.chkSave = False 'reset Save flag if starting new appt
Me.chkAllDay = False
Me.chkRecurSingle = False
RecurringMode 'disable recurring mode

End Sub




Posted by: Peter Hibbs Mar 16 2019, 04:00 AM

Hi basehumax,

Well, that does not really help as I wrote all that code anyway.

Did you check the 'QUOTE' declarations in the VBA modules as I suggested?

I think the best option is for you to upload your (zipped) database here so I can have a look at it.

Peter.

Posted by: basehumax Mar 16 2019, 11:02 AM

hi peter

can you please tell easy way to check duplicate via modules thanks
my file is very big i need to strip if i have to upload let know if something i can do myself to lookup cheers

Posted by: basehumax Mar 16 2019, 01:53 PM

Hi Peter

Thank you so much got now and is working i really appreciated your help mate

Posted by: Peter Hibbs Mar 16 2019, 04:18 PM

Hi,

yw.gif

Peter.

Posted by: basehumax Mar 16 2019, 09:10 PM

Hi Peter,

i am getting strange error when click on day the calendar but everything else works fine?

 

Posted by: Peter Hibbs Mar 17 2019, 04:43 AM

Hi basehumax,

I don't know but my guess is that you have an appointment record that has a double quote character in the Subject field. Perhaps you could check that for the date that the error appears on and if so, I suggest you replace the double quote character with two single quotes which look the same to the user but will not generate an error. If that is not the problem then you will need to supply more details of the problem.

Peter.

Posted by: basehumax Mar 17 2019, 10:22 AM

Hi Peter

is only when click day view from the calendar, when you get the error also you can create appointment from day view but will not show that appointment on day view screen but will show other views like month or year i checked "" but i dont have any appointment set up at all do you know what it can be?

Posted by: Peter Hibbs Mar 17 2019, 11:27 AM

Hi basehumax,

Hmmm, very odd!

Have you made any changes to the version that you downloaded (I don't have any problems with my original copy)?

I think the first thing to try is to open the VBA Module modCalendarCode, scroll down to the Public Sub ShowDayAppts(vDate As Date, vCategoryID As String) routine and REM out this line On Error GoTo ErrorCode at the top of the routine. Then open the Daily calendar again and when it errors out, it should stop on the line of code that is causing the problem. This might give a clue to what is happening (or it might not) but either way, perhaps you could show us a screen shot of the code window when it does.

If that does not show up anything useful then maybe you could upload a copy of the database so we can have a closer look.

Peter.

Posted by: basehumax Mar 17 2019, 01:30 PM

Hi Peter

bit strange i know this error comes on when click day from the calendar main but if i open the file direct frmCalendarDay no error.
i have taken off Error GoTo ErrorCode but the error was still showing up, see screenshots please thanks

 

Posted by: Peter Hibbs Mar 17 2019, 02:57 PM

Hi basehumax,

It is not possible to say what the problem is just from screen shots so all I can suggest is that you upload a copy of the database so that I can have a look. I am also wondering if you have made some changes to the code that has caused this problem. It looks like you have made some changes so perhaps you need to single step through the code when you click the Daily Mode button to see what happens.

Peter.

Posted by: cmohanc Mar 18 2019, 10:58 AM

Thank Yoy!

Posted by: basehumax Mar 18 2019, 06:09 PM

Hi Peter

Its Strange i have just rib Calendar code the module and paste again and now it works no more error thanks Peter

Posted by: Peter Hibbs Mar 19 2019, 12:16 PM

Hi basehumax,

OK, excellent (these things happen sometimes) and good luck with the project.

Peter.

Posted by: basehumax Mar 19 2019, 08:30 PM

cheers peter

Posted by: basehumax Mar 25 2019, 09:22 PM

Hi Peter,

i dont know if its possible is there way to show any appointments outstanding on another form like if have appointment it shows 1 or 2 whats ever outstanding something like that if possible thanks

Posted by: Peter Hibbs Mar 26 2019, 04:19 AM

Hi basehumax,

Well, I guess anything is possible.

What I have done in the past for something like this is to create a query which returns a list of outstanding appointments (using whatever criteria you need to define an 'outstanding appointment') and then write a small function in VBA which opens the query and if it returns one (or more) results, open a pop-up form based on the same query which lists the outstanding appointments information. Then you can just call the function from the Open event of the calendar form (or maybe some other form that is always opened at start up, etc). If there are no outstanding appointments then nothing happens as far as the user is concerned and if there are appointments outstanding, the form will open and the user can take whatever further action is then required.

Anyway, good luck and let us know how you get on.

Peter.

Posted by: basehumax Mar 26 2019, 08:34 PM

thanks peter

Posted by: marinko888 Apr 10 2019, 07:39 AM

Does anybody know how to change a dark blue background color that is selected in the red rectangle? I haven't found a way yet.
Thanks!


Posted by: Peter Hibbs Apr 10 2019, 09:06 AM

Hi marinko888,

welcome2UA.gif

Yes, there are two ways to do that :-

Option 1. Make sure the main calendar form is not open and then open the form frmCalendarWeek in Design mode.
Drag the Detail section down so that the Form Header section is about three times the height.
Click on one of the date controls in the Form Header section which will select the transparent button called cmdDate which has been placed over the date controls.
Move this button down a few centimetres so that you can then select the date controls with the mouse.
Drag the mouse over all the date controls (take care not to move them) so that they are all selected (that is Text box controls txtSun to txtSat and txtSunday to txtSaturday).
Change the Back Color property so that the background color is whatever you want.
Now move the button cmdDate back over the text boxes so that it is in its original position (and perhaps click on the Bring to Front icon on the Arrange tab to make sure it is above the text boxes).
Move the Detail section back up to its original position and then close and save the form.

Option 2. Select the Text box controls txtSun to txtSat and txtSunday to txtSaturday) one after another in the Property Sheet drop down box and change the Back Color of each control in turn.

Note also that you may need to change the Back Color property of the Form Header section to your new color (depending on how you want it to look).

This will change the Weekly calendar colors but you may need to do something similar on the Month and Day calendars using the forms frmCalendarMonth and frmCalendarday.

HTH and let us know if you have any problems,

Peter Hibbs.

Posted by: marinko888 Apr 11 2019, 04:08 AM

Hi Peter,
first of all thanks for your help, I have made changes as suggested. Can you please help me to change the color of today's selected day from pink to something else?


Posted by: Peter Hibbs Apr 11 2019, 05:58 AM

Hi,

OK, this facility uses Conditional Formatting to change the header colors and the procedure to change them may vary slightly depending on which version of Access you are using, but hopefully this will help.

As before, open the form frmCalendarWeek in Design mode and select each of the 14 Text box controls mentioned above in turn (note that, unfortunately, you cannot do this by selecting all the controls together and changing the colors in one operation).

With a control selected, click on the Conditional Formatting icon on the ribbon to open the Conditional Formatting Rules Manager pop-up form.
Click the the Edit Rule button which opens the Edit Formatting Rule pop-up form and then change the foreground and background colors using the Background color and Font color drop down boxes, as required. Note that on my version of Access (A2013) the colors don't always change immediately and it has to be done two or three times to make it work properly).
Click OK to save and close both forms and repeat for each of the 14 Text box controls.

Note also that on later versions of Access you can change all the controls without having to close the Conditional Formatting form each time by using the drop down box at the top of the Conditional Formatting Rules Manager pop-up form to select the next control and using the Apply button.

HTH,

Peter Hibbs.

Posted by: marinko888 Apr 11 2019, 06:31 AM

Thanks for your reply!

I have figured that out but the selection of current day in month and day views is most probably made somewhere in VBA code...





Can you please help me with those two?

Thanks for all your help,
Marinko

Posted by: Peter Hibbs Apr 11 2019, 08:12 AM

Hi Marinko,

The daily calendar is pretty much the same as my first reply except that the Text box control name is called txtToday and that also uses Conditional Formatting (although you may need to enable the control on the Edit Formatting Rule pop-up form with the Enable button to see the colors).

The monthly calendar (frmCalendarMonth) also uses Conditional Formatting to show today's date and the control names in this case are called Day1Date to Day7Date. However, as with the Weekly calendar, there is a transparent button (cmdDay) placed over the controls which you may need to move out of the way first if you use that method to select the controls (probably easier to use the second option of selecting the first control name in the Property Sheet section and then change each of the 7 controls from within the Conditional Formatting form). Also you may need to click on the Enable button to display the colors on the Edit Formatting Rule pop-up form (as mentioned above).

Let us know if you have any further problems.

Peter Hibbs.

Posted by: marinko888 Apr 11 2019, 09:40 AM

Hi Peter,
Thanks for your help, I didn't know about the Enabled Formatting property so the issue has been resolved.

I have another problem. I have spotted a little bug in the calendar logic - when you go back in a Previous year and select one date you get Appt details in the list box:



and when you click again, this time on a Next year button, Appt details info remains in a list box. The list box should be empty...



I have tried to resolve this with the List box Requery when the Next year button is pressed but haven't got any results.

Thanks,
Marinko

Posted by: Peter Hibbs Apr 11 2019, 11:13 AM

Hi Marinko,

OK, this is not really a bug. If you look at the date at the top of the list of appointments you will see that this date does not change when you move to the next year and so the list of appointments for that date will remain in the list.

If you want to change that date to the same date in the next year (so, for example, 01/02/2019 would change to 01/02/2020) then the only way to do that is to add one year to the Current Date field when you change to a different year. If you find the sub-routine called CalendarUpdate (see below) in the VBA code module for form frmCalendarMain and add the three lines marked below that may do what you want. It may also have other unwanted effects that I haven't discovered so keep a back up copy of the database file as well.

HTH,

Peter Hibbs.

CODE
Public Sub CalendarUpdate()

'Update screen display in current mode
'Entry  (txtMode) = 0 (Yearly), 1 (Monthly), 2 (Weekly) or 3 (Daily) or 4 (Chart Mode)
'       (txtDate) = Date to be displayed

    Me.Painting = False
    Select Case txtMode
        Case 0                                                                          'if tab 1 (Year) then
            Me.txtCurrentDate = DateSerial(Year(Me.txtDate), Month(Me.txtCurrentDate), Day(Me.txtCurrentDate))                      '<=== ADD THIS LINE
            EnterMonthDates Me.frmCalendarYear, Year(Me.txtDate)                        'and add dates for selected year to form
            ShowYearAppts Me.frmCalendarYear, Me.txtDate, Me.cboCategory                'show appts for month
            If Year(Me.txtDate) = Year(Me.txtCurrentDate) Then                                  'if displayed year = current year then
                HighlightSelectedDate Me.frmCalendarYear, Me.txtCurrentDate, Me.txtCurrentDate  'highlight selected date with black border
            End If
            Me.frmCalendarYear!lstAppts.Requery                                                                                     '<=== ADD THIS LINE
            Me.frmCalendarYear!txtDetails = ""                                                                                      '<=== ADD THIS LINE

        Case 1                                                                          'if tab 1 (Month) then
            Me.txtDate = Me.txtDate - Day(Me.txtDate) + 1                               'force txtDate to 1st day of month
            ShowMonthAppts Me.txtDate, Me.cboCategory                                   'show appts for month
            Me.frmCalendarMonth.Requery                                                 'and refresh subform
        
        Case 2                                                                          'if tab 2 (Week) then
            Me.txtDate = Me.txtDate - Weekday(Me.txtDate, conFirstDay) + 1              'force txtDate to 1st day of week
            ShowWeekAppts Me.txtDate, Me.cboCategory                                    'update weekly appts
            Me.frmCalendarWeek.Requery                                                  'and refresh subform
        
        Case 3                                                                          'if tab 3 (Day) then
            ShowDayAppts Me.txtDate, Me.cboCategory                                     'update daily appts
            Me.frmCalendarDay.Requery                                                   'and refresh subform
        
        Case 4                                                                          'if tab 4 (Year Chart) then
            ShowChart Me.frmCalendarChart, Year(Me.txtDate), Me.cboCategory             'update Chart form
    End Select
    Me.Painting = True

End Sub

Posted by: marinko888 Apr 11 2019, 12:01 PM

Hi Peter,

that has helped me to resolve the issue. Thanks again for all your help! The app is great and I can see that you have put a lot of effort to create it.

Have a good one today...and tomorrow of course! thumbup.gif thanks.gif

Marinko

Posted by: marinko888 Apr 12 2019, 04:07 AM

Hi Peter,

is there any way I can use just frmCalendarYear, with all of its functionality and without rest of the forms (and buttons), or it would be impossible to achieve? I have tried but it seems to be a hard task to do due to interconnections with the Parent form.

Thanks,
Marinko

Posted by: Peter Hibbs Apr 12 2019, 08:41 AM

Hi Marinko,

Is there any way I can use just frmCalendarYear, with all of its functionality and without rest of the forms (and buttons).

I guess it is possible but, as you say, it would require quite a bit of work to move some of the controls from the main form to the Yearly form and fix all the references to the other forms and the VBA modules.

There have been several different versions of this calendar and the procedure will probably be different, depending on which version you are using. Can you post a zipped copy of your current version here and I will see what I can do (I think that will be easier than me explaining what you would need to do to get it working). Having said that it would probably be a lot easier to keep the main form - subform design and just remove the Weekly and Daily calendar subforms (and I think it would look better for the users), is there any reason that you need the Yearly form on its own, rather than on a main form?

Peter Hibbs.

Posted by: marinko888 Apr 12 2019, 09:38 AM

Hi Peter,
Thanks for your reply! I would use the frmCalendarYear Form as a subForm for my app (Dairy farm handling). When the user opens up the app it would show the current task list in the Calendar Form, inside the start Form, and the user could make a new task in the Calendar subForm. The frmCalendarYear would come instead of the PieChart.
When the user clicks on the menu icon it would open the full Outlook Style Calendar app.



Any help concerning that is appreciated.

Thanks,
marinko

Posted by: Peter Hibbs Apr 13 2019, 05:37 AM

Hi Marinko,

OK, the Pie Chart looks very good (I think I would keep that if it were me).

I have sent you a Private Message about the facilities you need on the calendar.

Peter.

Posted by: Peter Hibbs Apr 15 2019, 08:31 AM

Hi Marinko,

OK, here is a Year calendar which you can use in your project. Just import the forms, tables, queries and module into your project and then copy the calendar form (frmCalendarYear) on to your main form as a subform and it should work OK (you will need to amend the query qryAppointmentList to add in the main form name to the criteria on columns SD and ED. Something like Forms!YourMainFormName!frmCalendarYear!txtCurrentDate) where YourMainFormName is the name of your parent form.

I don't know which facilities you wanted to keep or remove so you will just have to try it out and see how you get on. You can, of course, change the Category names and colors in the table tblCategories if you need different names and colors for them.

I have also removed some of the fields in the appointments table (tblAppointments) so you may need to do the same if you are using your own table for this data.

Hopefully this will work for you but let me know how you get on.

Peter.

 Outlook_Year_Calendar.zip ( 531.59K ): 77
 

Posted by: marinko888 Apr 15 2019, 09:10 AM

Hi Peter,

thanks for doing that it will for sure improve my app a lot. It has all of the functionality I have pictured myself. I will put it as a subForm due to its necessity.

Thanks again and have a great one today! thanks.gif woohoo.gif

Marinko

Posted by: marinko888 Apr 15 2019, 12:28 PM

Hi Peter,

what do I have to change and where to localize this:



Thanks!
Marinko

Posted by: Peter Hibbs Apr 15 2019, 12:45 PM

Hi,

You should not be getting that message as I removed the 'recurrence' facilities in this new version. You must use the new forms and code I posted for this version to work correctly, it looks like you are using the original forms which will not work.

Peter.

Posted by: marinko888 Apr 15 2019, 04:20 PM

OK. Will do. thumbup.gif

Thanks.
Marinko

Posted by: marinko888 Apr 23 2019, 04:23 PM

Hi Peter,

I have tried to import the whole calendar (all forms, queries, and tables) into my application which is made in MS Access 2019 and when I do that it becomes useless because it doesn't load any data into the calendar from tables. What should I do to make it work?

To be more specific it doesn't load data here:



but loads here:



and when I double-click on a date window pop-ups and also doesn't show any data:



Thanks for your answer,
Marinko

Posted by: marinko888 Apr 24 2019, 07:03 AM

When I use the reverse process, import app made in MS Access 2019 into Calendar that is made with previous versions, everything works fine but the Ribbon and everything else looks outdated. Is there any solution to this issue?
Appreciate any suggestion.

Thanks,
Marinko

Posted by: Peter Hibbs Apr 24 2019, 08:15 AM

Hi Marinko,

You don't need to use the new table in the zip file, you can still use your table with your data in it (I assume it has the same name as the original version, i.e. tblAppointments). If that works then you can just delete the fields from the table that are not used in this version (you can see which ones are needed by looking at the new table).

Not sure about your second question though, I would try my first suggestion to see if you can get that working.

Peter.

Posted by: marinko888 Apr 24 2019, 12:06 PM

Hi Peter,

Thank you for your answer. I have managed to find a solution from one of my backups. I have had a problem due to the constant changing of the VBA code.

Thanks and have a good day today!
Marinko

Posted by: marinko888 Apr 24 2019, 05:04 PM

Hi Peter,

something weird definitively happens during the conversion of Outlook Style Calendar to MS Access 2019. At first, everything seems to be fine:



but after I close and reopen my application then I get this:



As you can see info has been somehow distorted. Because of that, when I double click on the specific date, window pops-up but without any info:




Most probably the conversion causes that. Don't see any other reason.

Marinko

Posted by: marinko888 Apr 25 2019, 03:08 AM

I have to mention that this happens only when I IMPORT all elements of Outlook Style Calendar (tables, queries, VBA code) into another Application made in MS Access 2019. When I just open the Calendar everything is working fine.

Posted by: Peter Hibbs Apr 25 2019, 03:18 AM

Hi Marinko,

Sorry, I have no idea without seeing the database. You could check the query that is bound to that List box control which is qryAppointmentList to see if anything has changed there. It could be a problem with the table or the form or the VBA code or just about anything. I think you will need to look at everything in more detail to track the problem down, I don't think that just converting it to Access 2019 would cause that problem (unless there are some obscure bugs in that version of Access).

Peter.

Posted by: marinko888 Apr 25 2019, 03:28 AM

Hi Peter,

I have attached the zipped database. Thanks for your help!

Marinko

 CalendarImported.zip ( 522.98K ): 92

Posted by: marinko888 Apr 25 2019, 05:32 AM

I have tried with changing the query but haven't succeded...

Posted by: Peter Hibbs Apr 25 2019, 07:06 AM

Hi Marinko,

First off, I like the way you have changed the appearance of the forms, much better looking than my designs, I think.

OK, there are two main problems (there may be more but let's sort these out first).

(1) Open query qryAppointmentList in Design mode and add the table tblChambers to the query (it should automatically create a link to table tblAppointments).
Add the field ChamberRef to the query and then move it into column 3 (i.e. in the fourth column) so it should then look like ApptID - TM: Format... - Subj: Trim(ApptSubject... - ChamberRef - etc..
Save the query and that should fix the Yearly list problem.

(2) This one is a bit trickier and I believe this has happened because you are using Access 2019 which, I am guessing, defaults to SQL-92 Compatibility Mode (see this http://codevba.com/msaccess/sql_alike.htm#.XMGZP9h7laQ for details).
What you need to do is switch your database mode back to SQL-89 mode (as shown on that Web site) and then open the form frmCalendarAppt in Design mode, click on the lstAppts List box control and open the query in the Row Source property. Change the criteria for the ChamberID column from :-
ALike "*" & [Forms]![frmCalendarAppt]![txtChamberID]
to
Like "*" & [Forms]![frmCalendarAppt]![txtChamberID]
That should then show the appointments in the Calendar Appointments pop-up form.

One alternative you might like to try first though is this - SQL-92 uses the % character for matching instead of the * character. You could try changing the * for a % in the criteria above to see if that works. If it does then you may also need to go through the VBA code to do the same in the various routines that use the * as a matching symbol and also it would mean that your database would not work properly with earlier versions of Access but it is up to you (it would be interesting to see if it does actually work, even if you don't use it).

Anyway, let me know how you get on.

Peter.

Posted by: marinko888 Apr 25 2019, 08:45 AM

Hi Peter,

The first issue has been resolved but with the frmCalendarAppt and the query for the lstAppts...It seems to be like Mission Impossible because for the rest of my App I am using linked tables from SQL database and that's why I am getting this error. It pushes another syntax that is more suitable for the SQL database.
Don't know how to resolve this issue but maybe something eventually comes up.

Thanks for your help to clear things up!
Marinko

Posted by: Peter Hibbs Apr 25 2019, 09:09 AM

Hi Marinko,

That is tricky then, I have no experience with SQL Server databases so I can't help with that problem. I suppose you could remove that criteria from the query altogether so that it always shows all categories but whether that will affect the rest of the database, I am not sure.

If you cannot resolve it yourself I can only suggest you start another thread on UtterAccess to see if anyone else can help with using the Like/ALike function in a query with SQL Server.

Good luck,

Peter.

Posted by: marinko888 Apr 25 2019, 09:35 AM

Your suggestion has been very helpful. I have deleted the column (criteria) and almost everything functions now except I cannot change Chamber in a lstAppts for the Appointment but that is not a big issue. It is actually possible to have linked SQL database tables and the Access database tables when there is no conflict in syntax...

Thank you, Peter, have a good one.
Marinko

Posted by: marinko888 Apr 25 2019, 12:07 PM

Hi Peter,

do you know how to formulate raw source for lstAppts in VBA code? I have tried but the SELECT statement is too long...Maybe this is the way around for ALike that causes the problem.

Marinko

Posted by: marinko888 Apr 25 2019, 03:15 PM

Hi Peter,

forget about my last comment. I have somehow managed to change the code and make it work.

Thanks for your help!
Marinko

Posted by: marinko888 May 3 2019, 10:04 AM

Hi Peter,
I am trying to INSERT values into tblAppointments and getting error formating Now:



and my date and time format in the table looks like this: 26.4.2019. 6:00:00
but with this statement, I am unable to INSERT new values in the table:

DoCmd.RunSQL "INSERT INTO tblAppointments (ChamberID, ApptSubject, ApptStart, RecurrenceID) VALUES (5,'Transfer No." & Me.TransferNo.Value & "'," & Format(Now, "dd:mm:yyyy hh:mm:ss") & ",1)"

What am I doing wrong? How should I format the Now?

Thanks,
Marinko

Posted by: marinko888 May 3 2019, 11:14 AM

Hi Peter,

ignore my last question. I have searched through your code and have found the solution that you have already applied.

Have a good one,
Marinko

Posted by: cheekybuddha May 3 2019, 11:25 AM

For anyone using a non-US date format (eg dd/mm/yyyy) then use this line:

CODE
' ...
DoCmd.RunSQL "INSERT INTO tblAppointments (ChamberID, ApptSubject, ApptStart, RecurrenceID) VALUES (5,'Transfer No." & Me.TransferNo.Value & "'," & Format(Now, "\#yyyy\-mm\-dd hh:mm:ss\#") & ",1)"
' ...


hth,

d

Posted by: Peter Hibbs May 3 2019, 01:17 PM

Hi Marinko,

OK, glad you found a solution.

Incidentally, it is also possible to simplify the date function in an SQL string, for example, to save the current date and time to a table you can use this in the VALUES part of the function :-

CODE
.... VALUES (5,'Transfer No." & Me.TransferNo.Value & "',Now(),1)"

You can also do the same for the current date or current time with Date() or Time() which has the advantage that you don't need to format it for the US date format.

Peter.

Posted by: slynch Jul 5 2019, 02:27 PM

Hi There this is my first time on any message board. This Calendar is awesome but how do I adjust it so that my week starts on a Saturday? my company work cycle is Sunday-Friday.

Posted by: Peter Hibbs Jul 5 2019, 05:15 PM

Hi S Lynch

In the code module modCalendarCode, change the line near the top of the code from -

CODE
Public Const conFirstDay = vbSunday

to
CODE
Public Const conFirstDay = vbSaturday

as explained in the User Manual.

Peter Hibbs.

Posted by: slynch Jul 8 2019, 11:13 AM

I'm so sorry...I found it right after.

ty

SL

Posted by: Peter Hibbs Jul 8 2019, 01:24 PM

Hi ty,

OK, don't worry about it, you are not the first to ask this question and welcome to UtterAccess BTW!

Peter.

Posted by: Rogerk Aug 4 2019, 06:21 AM

Hello Mr. Peter Hibbs, and thanks,
Thanks for posting this awesome calendar.
You have been supporting this project for almost seven years.

Congratulations.

Members can read this topic and use whatever calendar version they want.

I think that it might be beneficial for everyone to create a new version, complete with all the changes that have occurred over the years.
For example:
1. At latest versions export / import to Outlook command button is missing. I put a command button at the New Appointment form (frmCalendarAppt) to export only one Appointment to Outlook when I make a new one.
2.The small calendar (frmCalendar), I think it would be useful on the main screen (constantly visible on a frmSwitchboard) for a quick switch to dates.
3.The list (qryAppointmentList) can be inserted in other calendars (month / week) on the main screen also.
4.You might be thinking about a process to export an appointment at Google Calendar.
However I will use one of your basic versions in my project.
And sorry for my English.

Kind regards,
Kostas

Posted by: Peter Hibbs Aug 5 2019, 01:31 PM

Hi Kostas,

Thank you for your kind comments, it is always nice to know that someone is finding the demos useful.

I think that it might be beneficial for everyone to create a new version, complete with all the changes that have occurred over the years. I agree and I am now in the process of creating a new version which combines the facilties that have been most requested by users over the last few years. However, I have only just started this project so it will be several weeks before it is ready for publication but I will post it here when it is finished. I have also answered your other comments below.

For example:
1. At latest versions export / import to Outlook command button is missing. I put a command button at the New Appointment form (frmCalendarAppt) to export only one Appointment to Outlook when I make a new one. The reasons that I dropped that facility on the later versions are (a) nobody really needed it because the Access calendar did pretty much what the wanted and (b) the addition of recurring appointments made the export/import code more complicated and I have not yet worked out how to export or import those types of appointments. When I get time I will look again to see if it is possible to add this feature in at a later date.

2.The small calendar (frmCalendar), I think it would be useful on the main screen (constantly visible on a frmSwitchboard) for a quick switch to dates. I guess you could add a calendar to a separate switchboard but I think that will be up to the developer to do themselves.

3.The list (qryAppointmentList) can be inserted in other calendars (month / week) on the main screen also. Not sure what you mean here, are you saying that it would be useful to have the list of appointments for a selected date that is used on the Yearly calendar should also be available on the Monthly and Weekly calendars? I guess this could be done but it would make the main form much bigger and I am not sure that the form would then fit on any size computer screen.

4.You might be thinking about a process to export an appointment at Google Calendar. I have thought about this in the past but I have not done anything about it because I do not have access to a Google calendar so I have no way of testing it and it looks pretty complicated to do in code so it would require a lot of work. Not sure just how many users would use this facility since you are the only one that has mentioned it. Other people have already done some work on this, see http://www.UtterAccess.com/forum/index.php?showtopic=2041518&hl= for more details, so if you want to try it out yourself then please do and let us know how you get on.

Anyway, thanks for your interest and watch this space!

Peter.

Posted by: Rogerk Aug 5 2019, 01:58 PM

Hi Peter,
I'd like to say a big thank you again.
I would like a great modern calendar (white-gray, a cbo for year, month, week, day, arrows instead of buttons for navigation, a small calendar on the same main screen for quick access and the list of appointments to every calendar form.)
Outlook is important in my opinion because with a small freeware program (DeskTask) I keep all reminders on my computers screen and with another utility (G Suite Sync) that synchronizes Outlook with Google Calendar I keep track of my reminders from my smartphone.
I'll try it with your demo.
Keep walking Peter

Kostas

Posted by: perseas Aug 13 2019, 03:51 AM

Hello Mr. Peter Hibbs,
Many Thanks for your calendar
I am new here and basically signed up because of this great calendar Demo.

I have one question.
I want to use in the Main form (frmCalendarMain) a listbox (lstChambers) for Chambers and Check Boxes to select a Chamber.
I put a List Box (lstChambers) to the Details of the main form (frmCalendarMain) with row source from tblChambers and a option Group of CheckBoxes (oGroupChambers) with chk0,chk1,chk2,chk3,chk4,chk5, CheckBoxes. CheckBox 0 (chk0) is to select all Chambers.

I am wondering if it is possible to use Check Boxes instead of the Combo box (cboChamber)
Is there any easy and fast method?

 

Posted by: edugorilla Aug 13 2019, 04:07 AM

good.



Posted by: Peter Hibbs Aug 13 2019, 11:14 AM

Hi perseas,

welcome2UA.gif

Yes, it is quite possible to use Check boxes instead of the Combo box control, I have uploaded a version that does that. If you are modifying your own version then this is what you need to do :-

Change the Combo box control into a Text box control, change the Name property from cboChamber to txtChamber and set the Visible property to 'No' (I have left it visible for this demo so you can see what happens in this control).
Delete the code in the cboChamber_AfterUpdate and cboChamber_KeyPress events (I have just remmed them out in the demo so that you can see where they are). Also delete the last line of code in the button cmdEditChambers_Click event.
Find and Replace every occurrence of cboChamber with txtChamber in the VBA code (all modules), there are 23 occurrences of that name.
Since you can only select one chamber at a time (apart from the 'Show All' option) you would normally use an Option box system to select the chamber (which you seem to have done) except that you are using Check boxes instead of the normal Option symbols. You can do this, if you wish, but you need to make sure they work as Option boxes and not the normal Check box mode. See the attached demo database to see how it works. When you have added the Frame control for the option boxes you should rename it fraChambers. You should also set the Default property value of the Frame control to 1 to set the default option to 'Show All Chambers'.
In the AfterUpdate event of the fraChamber control you should add this code:-

CODE
    If Me.fraChambers = 1 Then Me.txtChamber = "*" Else Me.txtChamber = Me.fraChambers - 1
    Call CheckMode

Note that this assumes that the ChamberID values of the five chamber records are set as 1 to 5, if you should add and delete other chamber records then you will need to rewrite this bit of code so that the Frame control selects the correct ID number for each chamber check box.

I am not sure what the List box is that you have used but I don't think that is relevant for this modification.

Anyway, try it out and let us know if you have any problems.

Peter.





 Outlook_Style_Calendar_V3o1.zip ( 711.56K ): 34
 

Posted by: perseas Aug 13 2019, 02:16 PM

Hi Peter,

It works without any problems. That's exactly what I wanted.
Thanks so much for quick response, your help, your time*, your detailed instructions and the new Demo Version.
Excellent work

perseas

*The greatest gift you can give someone is your time, because when you give your time you are giving a portion of your life that you will never get back*

Posted by: perseas Aug 13 2019, 02:51 PM

Peter,
Finally I found a small problem.
When I put in some test appointments do not appear on the list (lstAppts) of the year (frmCalendarYear).

pesreas

Posted by: Peter Hibbs Aug 13 2019, 03:11 PM

Hi perseas,

Yes, you are correct, sorry about that (I didn't have time to test everything, that's my excuse anyway).

Open the query qryAppointmentList in Design mode and change the criteria for column ChamberID to :- Like [Forms]![frmCalendarMain]![txtChamber].

Peter.

Posted by: perseas Aug 13 2019, 03:18 PM

Yes Thats it. Thanks

Posted by: perseas Aug 16 2019, 01:51 AM

Hi Peter
May I ask another question please..
I read your guide 'Outlook Calendar Demo Manual'(Calendar_V3) and i try to connect Employees with appointments, for Multiple Calendars for Multiple Users.
I have o "Compile error: Variable not defined". Because i am not a VBA expert i ask for you help.

 Outlook_Style_Calendar_V3o1_Employees.zip ( 767.73K ): 23
 

Posted by: Peter Hibbs Aug 16 2019, 12:59 PM

Hi perseas,

OK, what exactly are you trying to do? The changes you have made do not make any sense and when you Compile the code you now get dozens of errors.

If you are just adding a user name for each appointment then I would suggest that you use the 'Chamber' references as your user names. In other words, just change the Chamber names (Chamber 01, Chamber 02, etc) to your user names and change a few labels and that should work. If you need 'Chamber' names (which are really just Categories) and User Names, then that will be a lot more complicated.

Peter.

Posted by: perseas Aug 16 2019, 01:09 PM

I want to use the 'Chamber' (categories) as well. Each employee will have his / her own calendar so he / she will enter with his / her username and view his / her own calendar and the calendars of other employees.

Posted by: Peter Hibbs Aug 16 2019, 04:54 PM

Hi perseas,

OK, I will have a think about it but it could take a while because I am a bit busy at the moment.

Peter.

Posted by: perseas Aug 16 2019, 06:12 PM

I'll wait for that

Posted by: frm Sep 11 2019, 12:53 AM

Hi Peter,

I ask you if it is possible to include on the appointment summary of the day, week, month the Start Time - End Time, Subject and Location of the appointment with the color of the Category as an attached outlook image.

Thank you


Posted by: Peter Hibbs Sep 11 2019, 08:39 AM

Hi frm,

Yes you can but it might depend on which version of the calendar you are using. I assume that you are using Version 3.0o (or maybe 3.0g) which shows the category colors on the Monthly calendar.
What you should do is open module modCalendarCode , find sub-routine ShowMonthAppts and then find the line shown below :-

CODE
            vString = "<font style=""BACKGROUND-COLOR:#" & vHex & """>" & rst!ApptSubject & "                                  </font><br>"        'format text with back color

and replace it with this line :-
CODE
            vString = "<font style=""BACKGROUND-COLOR:#" & vHex & """>" & Format(rst!ApptStart, " h:nn") & "-" & Format(rst!ApptEnd, "h:nn") & " " & rst!ApptSubject & "; " & Trim(rst!ApptLocation) & "                                  </font><br>"      'format text with back color

If you need to do the same on the Weekly and Daily calendars you should do a similar change except that those calendars do not show the category colors. Just find the sub-routines ShowWeekAppts and ShowDayAppts in the same code module and replace this line :-
CODE
                vArray(vCol, vRow) = vArray(vCol, vRow) & rst!ApptSubject & "  "

with this :-
CODE
                vArray(vCol, vRow) = vArray(vCol, vRow) & Format(rst!ApptStart, " h:nn") & "-" & Format(rst!ApptEnd, "h:nn") & " " & rst!ApptSubject & "; " & Trim(rst!ApptLocation) & "  "

Note that there are two lines like this in each sub-routine.

The only problem I see with this idea is that if the Subject and/or Location fields have more than about 5 or 6 characters in them, the text will wrap around to the next line (but I guess you have allowed for that).

If you need the category colors to be shown on the Weekly and Daily calendars you will need to wait for the next version (3.0q).

Hope that helps but let me know if you have any problems.

Peter Hibbs.

Posted by: frm Sep 11 2019, 02:15 PM

Hi Peter,

I made the recommended changes but I made a mistake about Weekly and Daily I didn't succeed but I don't know where.

Please can you explain to me how to do it?




Posted by: frm Sep 11 2019, 02:40 PM

Hi Peter,

This is the db with the changes you suggested.
I wanted to ask another thing.
Is it possible to do with the mouse on the single appointment do what you see on September 11th?

Thank you

 Outlook_Style_Calendar_V3o_Settembre_2018.zip ( 332.99K ): 8


 Outlook_Style_Calendar_V3o_Settembre_2018.zip ( 332.91K ): 2
 

Posted by: Peter Hibbs Sep 11 2019, 03:16 PM

Hi frm,

OK, try this (you forgot to add the new line twice for each routine as I mentioned).

For the Weekly routine use :-

CODE
        'Now copy appt info into each row in array for current col
        Do Until DateDiff("n", vDate, vDateStop) <= 0                                       'until all appts have been processed  
            vCol = Weekday(vDate, conFirstDay) - 1                                          'calc column number for current Date
            vRow = (((Hour(vDate) * 60) + Minute(vDate)) \ conPeriod)                       'calc row number for curent date
            If conDayDitto = 0 Then                                                         'if conDayDitto = 0 then show appt in all time slots
                vArray(vCol, vRow) = vArray(vCol, vRow) & Format(rst!ApptStart, " h:nn") & "-" & Format(rst!ApptEnd, "h:nn") & " " & rst!ApptSubject & "; " & Trim(rst!ApptLocation) & "  "
            Else                                                                            'if conDayDitto = 1 then show appt in first time slot and " in subsequent time slots
                If rst!ApptID <> vTemp Or vRow = 0 Then                                     'if temp store different than data or start of new day then
                    vArray(vCol, vRow) = vArray(vCol, vRow) & Format(rst!ApptStart, " h:nn") & "-" & Format(rst!ApptEnd, "h:nn") & " " & rst!ApptSubject & "; " & Trim(rst!ApptLocation) & "  "
                    vTemp = rst!ApptID                                                      'update temp store
                Else                                                                        'else
                    vArray(vCol, vRow) = "                ''"                               'show " char
                End If                                                                      'end if
            End If
            vDate = DateAdd("n", conPeriod, vDate)                                          'inc time ref by 30 mins
        Loop


and for the Daily routine use this :-
CODE
        'Now copy appt info into each row in array for col 0
        Do Until DateDiff("n", vDate, vDateStop) <= 0                                       'until all cells filled  
            vRow = (((Hour(vDate) * 60) + Minute(vDate)) \ conPeriod)                       'calc Row No from date & time & period
            If conDayDitto = 0 Then                                                         'if conDayDitto = 0 then show appt in all time slots
                vArray(0, vRow) = vArray(0, vRow) & Format(rst!ApptStart, " h:nn") & "-" & Format(rst!ApptEnd, "h:nn") & " " & rst!ApptSubject & "; " & Trim(rst!ApptLocation) & "  "
            Else                                                                            'if conDayDitto = 1 then show appt in first time slot and " in subsequent time slots
                If rst!ApptID <> vTemp Then                                                 'if temp store different than data or start of new day then
                    vArray(0, vRow) = vArray(0, vRow) & Format(rst!ApptStart, " h:nn") & "-" & Format(rst!ApptEnd, "h:nn") & " " & rst!ApptSubject & "; " & Trim(rst!ApptLocation) & "  "
                    vTemp = rst!ApptID                                                      'update temp store
                Else                                                                        'else
                    vArray(0, vRow) = "                ''"                                  'show " char
                End If                                                                      'end if
            End If
            vDate = DateAdd("n", conPeriod, vDate)                                          'inc time ref by 30 mins
        Loop
        rst.MoveNext
    Loop

HTH,

Peter,

Posted by: frm Sep 12 2019, 02:07 AM

Hi Peter,

Changes made in the Outlook Style Calendar V3 or September 2018 version work.

How to do Outlook Version Calendar V3.op September 2018?

Is there yet another V3.oq version?

Thank you

Posted by: frm Sep 12 2019, 04:20 AM

Hi Peter,

sorry I made a mistake in the previous post.

Please replace this: -

How to do Outlook Version Calendar V3.op September 2018?

With this:-

Outlook Style Calendar V3.0p of February 2019?

Is it possible to do with the mouse on the single appointment do what you see on September 11th?

For example, passing over an appointment with the mouse I can open a message like the one you find by hovering over the date
11 sets (green color):

Show Daily calendar for this day

with this text:

8.00-8.30 Nuovo appuntamento; Al mare

Thank you

Posted by: Peter Hibbs Sep 12 2019, 08:30 AM

Hi frm,

OK, so on the Monthly calendar form, you want to show the details of any appointments in the pop-up Tool Tip label for a date when the mouse cursor hovers over the green date header label for that date?

If so, then I agree that this would be quite useful for the users but unfortunately I don't think that it is possible. I have done a few tests to see if it could be done but as I am using a Continuous type form for the calendars, there is no easy way to calculate which of the 6 rows on the calendar the user has selected (there is one method that I have used in the past but this will not work with the calendar design I am using here). If we had used 42 separate text box controls for the date fields (as some similar calendars do) then it would probably be relatively easy.

Sorry I can't be of more help but good luck with the project anyway.

Peter Hibbs.

Posted by: frm Sep 12 2019, 09:07 AM

OK.

One last thing:

the changes we made to the version

Version 3.0o By Peter Hibbs September 2018

can also be done on the version

Version 3.0p By Peter Hibbs February 2019?

Thank you

Posted by: Peter Hibbs Sep 12 2019, 01:19 PM

Hi frm,

Exactly what changes in V3.0o did you want to add to V3.0p? If you mean the option to show appointments in different colors on the Monthly calendar then I guess it could be done but I don't have the time to do that at the moment. However, I am working on a new version (3.0q) which has all the facilities of the earlier versions (but not the Year Chart form as that was a bit specialised) so maybe this new version will do what you want. It will be a few more weeks before I will have finished it though.

If you meant something else then post back with a more details.

Peter.

Posted by: frm Sep 12 2019, 01:49 PM

Hi Peter,

In Version 3.0o I replaced this series of code in the modCalendarCode module, to the ShowMonthAppts sub-routine I replaced this line: -

vString = "<font style =" "BACKGROUND-COLOR: #" & vHex & "" ">" & rst! ApptSubject & "</font> <br>" 'format text with back color

with this:-

vString = "<font style =" "BACKGROUND-COLOR: #" & vHex & "" ">" & Format (rst! ApptStart, "h: nn") & "-" & Format (rst! ApptEnd, "h: nn ") &" "& rst! ApptSubject &"; "& Trim (rst! ApptLocation) &" </font> <br> "'format text with back color

In Version 3.0q this line: -

vString = "<font style =" "BACKGROUND-COLOR: #" & vHex & "" ">" & rst! ApptSubject & "</font> <br>" 'format text with back color

I did not find her.

How can I do?

For the other changes I expect the new version.

Thank you

Posted by: frm Sep 13 2019, 01:26 AM

Hi Peter,

can you make this change?

When you click on the Weekly button the Text Box

txtDate = whole week

Example:

Sunday 8 September 2019 - Saturday 14 September 2019

Thank you

Posted by: Peter Hibbs Sep 13 2019, 04:56 AM

Hi frm,

You can't show the full week dates in the txtDate text box because that control is used to define the current date and so it must hold a valid date. All I can suggest is that you could add a new text box control to the top of the frmCalendarWeek form (let's call it txtWeek) and then add one line of code to the sub-routine ShowWeekAppts in module modCalendarCode like this :-

CODE
    On Error GoTo ErrorCode

'Calc week numbers and dates from starting date and copy to table
    vFirstDate = vWeekStart                                                                 'set first date of week (Sunday)
    vDate = vFirstDate                                                                      'vDate = first day of week

    Forms!frmCalendarMain!frmCalendarWeek!txtWeek = Format(vFirstDate, "dddd d mmmm yyyy") & " - " & Format(vFirstDate + 6, "dddd d mmmm yyyy")  'add this line here


To be honest, I can't really see a lot of point in doing this since the Weekly calendar shows all the days and dates for the week being displayed anyway, but I guess you have your reasons.

In your previous post, I don't understand what you are asking so please explain in more detail if you still need any advice.

Peter.

Posted by: frm Sep 13 2019, 05:21 AM

Hi Peter,

sorry if I'm not clear, but it's a translation problem.

What I'd like to do is display the appointment in this month's calendar:

8.00-8.30 New appointment; At the sea

Posted by: Peter Hibbs Sep 13 2019, 08:06 AM

Hi frm,

QUOTE
What I'd like to do is display the appointment in this month's calendar:
What does this mean? What do you want to display and where do you want to display it (the Monthly calendar already displays the appointment details)?

Please explain in full what you want.

Peter.


Posted by: frm Sep 13 2019, 08:35 AM

Hi Peter,

Example.

The appointment that is inserted in the calendar and weekly calendar as in the image can also be carried in the monthly calendar in this way:

8.00-8.30 New appointment; At the sea

 Calendar_Weekly.zip ( 2.58K ): 15


Thank you

Posted by: Peter Hibbs Sep 13 2019, 09:58 AM

Hi frm,

OK, just to clarify - it looks like you have made the Weekly rows taller (which is OK) and you want to insert the times and subject and location fields into the calendar fields automatically, in other words you would set up the start and end times and the Subject field and maybe the Location fields on the Appointment Schedule form as normal and the program would then combine those four fields into one text string and display it on the Weekly calendar. Is this what you wanted?

If so, then I think I explained how to do this in post #240 earlier. Did you try that?

Peter.

Posted by: frm Sep 13 2019, 10:17 AM

Hi Peter,

what is in post # 240 applies to Weekly e Daily.

How can I do for Monthly?

Thank you

Posted by: Peter Hibbs Sep 13 2019, 01:23 PM

Hi frm,

OK, I see. You want to be able to show the Start and End Time + Subject + Location on the Monthly calendar, is this correct?

The code to do this is pretty simple but it depends on which version of the calendar you are using. If you are using Version 3.0o then I already explained how to do that in post #237, if you are using a version that does NOT show category colors on the Monthly form then the code is slightly different. So which version are you using?

Peter.

Posted by: frm Sep 13 2019, 01:47 PM

Hi Peter,

You want to be able to show the Start and End Time + Subject + Location on the Monthly calendar, is this correct? Yes

I'm using your latest version

Appointments Calendar Version 3.0p By Peter Hibbs February 2019

Thank you

Posted by: Peter Hibbs Sep 13 2019, 03:10 PM

Hi frm,

OK, try this - find sub-routine ShowMonthAppts and replace the section of code that starts 'Now copy appt info....' with the code below.

CODE
        'Now copy appt info into each row in array
        Do                                                                      'do--
            vCol = Weekday(vDate, conFirstDay) - 1                              'calc column No for current date
            vRow = (DateDiff("d", vFirstDate, vDate) \ 7)                       'calc row number for current date
            If conMonthHide = 0 Then                                            'if current date <> current month then skip  (hide appts for dates not in current month)
                vArray(vCol, vRow) = vArray(vCol, vRow) & Format(rst!ApptStart, "h: nn") & "-" & Format(rst!ApptEnd, "h: nn ") & " " & rst!ApptSubject & "; " & Trim(rst!ApptLocation) & vbCrLf
                vArray(vCol + 7, vRow) = rst!MonthColor
            Else
                If Month(vMonthStart) = Month(vDate) Then                               'if current date <> current month then skip  (hide appts for dates not in current month)
                    vArray(vCol, vRow) = vArray(vCol, vRow) & Format(rst!ApptStart, "h: nn") & "-" & Format(rst!ApptEnd, "h: nn ") & " " & rst!ApptSubject & "; " & Trim(rst!ApptLocation) & vbCrLf
                    vArray(vCol + 7, vRow) = rst!MonthColor
                End If
            End If
            vDate = vDate + 1                                                   'inc date ref
        Loop Until vDate >= vDateStop                                           'until all cells filled

Actually, you only need to replace the two lines that start 'vArray(vCol, vRow) =' but I have shown the extra lines so that you can see where they should go.

Good luck,

Peter.

Posted by: frm Sep 13 2019, 04:55 PM

Hi Peter,

I made the changes on

Outlook Style Calendar V3p (V3) February 2019.

It works perfectly.

On

Outlook Style Calendar V3p (V4) February 2019 gives an error message:

"Unable to add the desired amount of data. Insert or paste less data."

There is a difference between

Outlook Style Calendar V3p (V3) February 2019

and

Outlook Style Calendar V3p (V4) February 2019?

If there is no difference I continue to work on

Outlook Style Calendar V3p (V3) February 2019.

Thank you


Posted by: frm Sep 14 2019, 01:38 AM

Hi Peter,

I made all the changes you suggested on

Appointments Calendar Version 3.0p By Peter Hibbs February 2019

I entered two appointments on September 14:

First appointment:

Start Time: 6.00
End Time: 6.15
Subject: New Appointment
Location: To the sea

Second appointment:

Start Time: 6.15
End Time: 6.30
Subject: Next Appointment
Location: To the station

The inclusion of the two appointments works in Weekly and Daily.

Does not work in Monthly.

I enclose the db and the image of the error.

 Outlook_Style_Calendar_V3p__V3__February_2019.zip ( 490.71K ): 8



Posted by: Peter Hibbs Sep 14 2019, 05:28 AM

Hi frm,

You are correct, there was a bug in this version. To fix it I deleted all the appointments and then Compacted and Repaired the database. I think the problem was that the appointments table had somehow got corrupted which prevented more than one appointment being displayed in a Monthly date field. I have no idea why but try the attached version and see if you get any more problems.

Having said that, I think you would be better using the other version that shows the appointments for different categories in different colors (Version 3.0o) as Version 3.0p uses OLE Object controls which is a bit limited. Anyway, that is up to you.

Good luck with the project and hopefully it should work now.

Peter.

 Outlook_Style_Calendar_V3p__V3__February_2019.zip ( 317.11K ): 11
 

Posted by: frm Sep 14 2019, 05:55 AM

Hi Peter

I'm sorry, but it still doesn't work.

I enclose the two images with the errors.

 Outlook_Style_Calendar_V3p__V3__February_2019___New.zip ( 541.08K ): 20


Posted by: frm Sep 14 2019, 07:18 AM

Hi Peter,

I wanted to ask if it is possible to make this change.

Example.

Enter multiple appointments on the same day.

First appointment:

Start Time: 6.00
End Time: 6.15

Second appointment:

Star Time: 6.15
End Time: 6.30

Third appointment:

Star Time: 6.30
End Time: 6.45


When I enter the second appointment from 6.15 am - 6.30 am, do this:

Click on day 14 in the frmCalendarMain

I open the frmCalendarAppt

Click on the NewAppointment button and automatically:

Star Time: 6.15
End Time: 6.30

When I enter the third appointment from 6.30 - 6.45 am, do this:

Click on day 14 in the frmCalendarMain

I open the frmCalendarAppt

Click on the NewAppointment button and automatically:

Star Time: 6.30
End Time: 6.45

And so for every subsequent appointment on the same day.

Thank you

Posted by: Peter Hibbs Sep 14 2019, 08:46 AM

Hi frm,

OK, I have found the problem (my error) which is easy enough to fix.

Open the table tblMonthData in Design mode and change the field types of fields Day1Data to Day7Data from Short Text to Long Text.
The problem was that for days 2 to 7 the Field Size property was set to only 50 characters which was not enough to hold the extra text that the last modification caused. For some reason Day1Data was set to 255 which was big enough for the tests that we did. You could leave the fields types as Short Text and just change the Field Size property to 255 for all the days but that might not be enough if you had a lot of text in the Subject and Location (plus the two date) fields so using a Long Text type is probably safer.

Also, you could get the same problem with the Weekly and Daily calendars if you have a lot of text to display so it is probably a good idea to change the same fields in the table tblWeekData to Long Text as well.

Hopefully that will fix the problem and thanks for finding this obscure bug for us.

Peter.

Posted by: frm Sep 14 2019, 08:56 AM

I'm glad I was helpful.

With your latest changes of fields Day1Data to Day7Data in the table tblMonthData and in the table tblWeekData the error no longer gives it.

OK

It works

Thank you

Posted by: frm Sep 15 2019, 12:37 PM

Hi Peter,

please can you advise me on how to do what I wrote in Post # 261?

Thank you

Posted by: Peter Hibbs Sep 15 2019, 01:44 PM

Hi frm,

I am not sure what you are asking for here! Do you mean that when you open the Appointment Schedules form from the calendar you want the Start Time to automatically default to the next free time slot for that day? If so, then I guess it could be done but an easier way would be to open the Appointment Schedules form using the Daily (or Weekly) calendar and then just double click on the next free time slot, then the form will open with the Start and End times set up for the time slot that you clicked on.

If that is not what you meant then please explain in more detail.

Peter.

Posted by: frm Sep 15 2019, 02:50 PM

Hi Peter,

Exactly this:

when I open the Schedule Appointments form from the calendar
the start time must automatically pass to the next free time slot for that day.


Posted by: Peter Hibbs Sep 15 2019, 03:05 PM

Hi frm,

OK, so will my suggestion of using the Weekly or Daily calendar work for you?

Peter.

Posted by: frm Sep 15 2019, 03:20 PM

Hi Peter,

the use of the Weekly or Daily calendar work,
but I wanted if you could make the change I proposed, if possible.

Thank you

Posted by: Peter Hibbs Sep 15 2019, 03:52 PM

Hi frm,

OK, I will think about it tomorrow, if I have time.

Peter.

Posted by: frm Sep 15 2019, 04:11 PM

Hi Peter,

OK

I think they are changes that could be useful.

Thank you for your time and availability.

Posted by: Peter Hibbs Sep 16 2019, 10:19 AM

Hi frm,

OK, try this. Open the form frmCalendarAppt in Design mode and open the VBA code window. Find the Private Sub cmdNew_Click() event and add the bit of code between the double lines to the event code. The whole event should then look something like this :-

CODE
Private Sub cmdNew_Click()

'Create a new appointment record

    Me.chkUpdate = False            'clear chkUpdate flag if user is starting a new appt
    ClearControls                   'reset main controls
    EnableFields True               'enable input fields
    Me.txtAppointmentID = 0         'reset AppointmentID
    Me.lstAppts = Null              'clear any selections in list box
    Me.txtStartDate.SetFocus        'move focus to Start Date

    Me.cmdDelete.Enabled = False
    Me.cmdSave.Enabled = False

'============== Insert the code below here =============
Dim vStartTime As Date
Dim vIndex As Long
    
    vIndex = Me.lstAppts.ListCount - 1                              'calc last entry in list box
    If vIndex > -1 Then                                             'skip of no entries
        vStartTime = Me.lstAppts.Column(2, vIndex)                  'fetch last date/time from list box
        Me.cboStartTime = TimeValue(vStartTime)                     'copy last time to cboStartTime
        Me.cboEndTime = DateAdd("n", conPeriod, Me.cboStartTime)    'and calc new EndTime
    End If
'====================================================

End Sub

Your code above may look slightly different to the above, depending on which version of the calendar you are using, so just copy and paste the code between the double lines into the event immediately above the End Sub line.

If you add an appointment to a date that has no appointments already, the Start and End times will default to whatever you have set up as the earliest working appointment time.

Hope this helps and good luck.

Peter.

Posted by: frm Sep 16 2019, 10:45 AM

Hi Peter,

that's what I wanted to do.

If I have other changes, can I ask for your help?

I hope that those proposed by me are useful things.

If you consider the changes made useful, can they be included in the next version of the calendar?

Thank you

Posted by: Peter Hibbs Sep 16 2019, 12:46 PM

Hi frm,

OK, good.

QUOTE
If I have other changes, can I ask for your help?
Well, you can try but I think it would be better to send me a private message and if I am not too busy I will consider it.

QUOTE
If you consider the changes made useful, can they be included in the next version of the calendar?
I have included some of your suggestions but some are a bit too specialised for a general use demo, it would be up to the individual developers to add their own facilities. as required.

Peter.

Posted by: frm Sep 16 2019, 01:40 PM

Hi Peter,

this change could also affect other people.

For example:

how to color the holidays and Sundays in the three calendars.

Thank you

Posted by: Peter Hibbs Oct 1 2019, 03:31 AM

The attached zip file contains the latest version of the Outlook Style Calendar (V3.0q) for anyone who is interested.

Note that this version uses HTML coding to color the appointments for the 10 different categories and so it will only work with Access 2007 and later. The enclosed PDF document explains how to use and set up the calendar.

Let me know if any bugs are found with full details of the problem.

Peter Hibbs.

 Outlook_Style_Calendar_3q.zip ( 1.06MB ): 35
 

Posted by: Rogerk Oct 11 2019, 03:00 PM

Hi Peter
In your last version (Outlook Style Calendar V3q) , when I tried to rename the category names from the Default names (Category 01,Category 02, Category 03 ...), with a different name (like Hobbies,Vacation, Family.. ) then to the main calendar in daily mode does not display the appointments in the list (frmCalendarAppt), when clicked.
Am I doing something wrong?

Posted by: Peter Hibbs Oct 11 2019, 05:22 PM

Hi Roger,

No you aren't, it looks like I messed up that bit. I will upload a fix tomorrow. Sorry about that.

Peter.

Posted by: Peter Hibbs Oct 12 2019, 05:32 AM

Hi Roger,

OK, here is a fixed version (V3.1). Don't know how I missed those two errors, must be getting sloppy in my old age.

One was that the Save button did not get enabled if you changed just a Category name and nothing else on the Calendar Settings form and the other was an error in the query bound to the Calendar Appointments form which did not show the appointments if the Show All Categories option was enabled.

Anyway, hopefully everything works OK now and thanks for letting me know about the errors.

Peter.

 Outlook_Calendar_V31.zip ( 1.06MB ): 12