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 ): 3035

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 ): 1361
 

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 ): 610
 

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 ): 904
 

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 ): 299

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 ): 120
 

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 ): 73
 

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 ): 14

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 ): 14
 

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 ): 10


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 ): 19


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 ): 20


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 ): 20
 

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 ): 30

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 ): 16
 

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 ): 25


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 ): 19
 

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 ): 19
 

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 ): 29
 

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 ): 21


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.