locked
Access Calendar Form RRS feed

  • Question

  • So a long standing objective is to create a calendar in Access.

    I have made good progress and can generate date information using a SQL query and display the dates as a cross tab or a table (indexed by year, month and weekno).

    To make it functional (and open up a data entry form for appointments on a given date) I have considered forms/subforms continuous forms and my latest idea is a listbox which can be addressed and loaded as an array.

    Is this a practical solution or are there better options out there?

    Thankyou

    Monday, July 22, 2019 8:01 PM

All replies

  • No need to re-invent the wheel unless you just want the practice coding. There are all kinds of canedar controls here

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, July 22, 2019 10:08 PM
  • thanks for responding Bill.

    it's not a calendar control that I want but a calendar application to display data and allow functionality.

    i am an experienced programmer learning Access so the challenges that I have are how best to organise the basic data, how best to organise the form and and how to make individual days/events clickable.

    I can generate date data with a query (cartesian product) and use a crosstab or table to build my data.

    After that I am tempted to use a list box which as an array to present the data but can i make each array element clickable?

    I have seen an alternative which uses 42 (6x7) individual text boxes but I feel this approach is not as elegant as it might be.

    If you might indulge my suggestion, given a similar requirement, how would you suggest organising the form?


    Tuesday, July 23, 2019 6:30 AM
  • I have seen an alternative which uses 42 (6x7) individual text boxes but I feel this approach is not as elegant as it might be.

    Hi Richard,

    For all my calendars I use a Form with as many subforms as I need, e.g. 7 for the sevens days per week.

    Each subform contains the necessary controls that are used for the individual days, that are tuned depending of how you want your calender look like. As the tuning is done by code, you have all kind of possibilities to change the display, zoom in, shift over weeks, months or years, etc.

    I extended this ways of working to far more areas than only calenders, and use it now all over my applications when I need "some" form.

    Imb.

    Tuesday, July 23, 2019 10:53 AM
  • I misunderstood your request. Have you looked at the templates to find one close to what you want like one of the event databases? Once you "install" a template you can modify everything, even the code. At lease, you might get a better idea of how to design your own.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Tuesday, July 23, 2019 3:35 PM
  • There are various paid solutions, I have one on my website, but a good free option can be found at https://www.utteraccess.com/forum/index.php?showtopic=1969978

    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Tuesday, July 23, 2019 3:38 PM
  • Imb,

    the problem is that I cannot put a subform on a continuous form so presenting a twelve month calendar is not possible that way. Perhaps I can put seven list boxes on a continuous form to avoid the 6x7 individual cell issue?

    I do believe that I can display the calendar dates as a report grouped by year, month and week no. I can probably also add a subreport for each date. 

    Thanks Bill.  I will check out the event template.

    Wednesday, July 24, 2019 5:29 AM
  • the problem is that I cannot put a subform on a continuous form so presenting a twelve month calendar is not possible that way. Perhaps I can put seven list boxes on a continuous form to avoid the 6x7 individual cell issue?

    Hi Richard,

    I don't the know the design and the logic of your applications, so I only can tall how I use to work.

    When I use a continuous form, it is just to display a set of records, e.g. appointments sorted in time. On that form there is a button to display the same information as a calender. A new (unbound) form is opened displaying the calender structure in the subsequent subforms. During opening all relevant controls on the subform are filled with some specific information on that appointment, whereas the PK of the appointment is used as Tag for that control.

    Doubleclicking on any control in the calender form will display (in a single record form) all the data of that record, if there was any.

    Of course, it is not necessary to start with a continuous form, you can also start with the calender form. You can navigate form one form to another to get the information interactively that you need.

    Reporting is a different thing. For me reports are static, a kind of a temporary print out. They don't need all these fancy things. In fact I never use Access reports, but I generate the output directly in an RTF-file.

    I hope you understand my way of working a little bit. Perhaps you can give me a small example of what you want.

    Imb.

    Wednesday, July 24, 2019 6:50 AM
  • thanks Imb, that makes sense.

    I really want to see the continuous form display calendar dates for a year/month/week together with associated information eg) bank holiday and any appointments for the day and be able to click/double click to drill down and update the appointments.

    Thanks to the feedback here I can now see a number of possible way to achieve this and I will have to experiment so see how they look and feel.

    It seems clear to me that a continuous form or report is required at the top level with each day (together with its associated events) being a clickable control that drills down to a more detailed form.

    Wednesday, July 24, 2019 3:47 PM
  • Hi Richard1604,

    The Utter Access site that Daniel Pineault mentioned earlier has several examples of exactly what you are describing, that is a monthly, weekly and daily calendar that uses Continuous type forms to show the appointments with Public Holidays highlighted among a host of other facilities.

    It does mean that you will need to join the Utter Access forum to download the demos but that is completely free anyway and only takes a minute or two. If there is some reason that you can't do that then I would be happy to email you are copy of the demo if you can provide an email address.

    Peter Hibbs.

    Wednesday, July 24, 2019 4:07 PM
  • I really want to see the continuous form display calendar dates for a year/month/week together with associated information eg) bank holiday and any appointments for the day and be able to click/double click to drill down and update the appointments.

    Hi Richard,

    If you want to use continuous forms, you will probably need a Day_tbl with all the specific attibutes for each day, with a LEFT JOIN to your Appointment_tbl. Filtering on a specific date range will give the different results.

    Just for the discussion, I want to show you one of my calender-examples. Instead of a continuous form, It uses the different subforms with the possibilitie to "horizontal scroll" the time with the autorepeat buttons "Volgende dag" (Next day), "Vorige dag" (Prev day), "Volgende week" (Next week), "Vorige week" (Prev week). But any other time shift can be used as well:

    It is just one example of the many, many possibilities.

    Imb.


    • Edited by Imb-hb Wednesday, July 24, 2019 9:36 PM typo
    Wednesday, July 24, 2019 9:25 PM
  • As noted there is a nice working example on uttter Access.

    And you likely will not use a continues form.

    Here is a calendar example I made:

    In above, I dropped in sub forms for each of the squares for the date. And they are clickable.

    The code to fill the above looks like this:

          Do While rstTours.EOF = False
             dateptr = rstTours!TourDate
                If dateptr >= dtStart And dateptr <= dtEnd Then
                   strTemp = Nz(rstTours(0), "")
                   Call Me.frmCalander.Form.SetDayTextAdd(strTemp, CDate(dateptr))
                End If
             rstTours.MoveNext
          Loop

    So, each sub form is named C1 to C42, and thus you can address the squares.

    And really nice, is that you only make ONE sub form for the date, and then repeat the (drop again) the day sub form over and over (42 times).

    The setDayText is a routine like this:

          

    Public Sub SetDayText(strText As String, dtDate As Date)

       Dim intPtr     As Integer
       Dim strC       As String

       intPtr = dtDate - m_DisplayStart + 1

       strC = "c" & intPtr

       Me(strC).Form.txtText1 = strText

    End Sub

    So, you can translate the given date into the control strC, and then referance that day  square.

    For a daily view, then I would likely drop in 7 sub forms that are continues to show events or whatever for each day.

    And this video of mine also gives some ideas:

    https://www.youtube.com/watch?v=AU4mH0jPntI

    It not all hard to create, but you want to cook up a design in which you can pull some data, loop over the data and send it to the calendar form. And once done, you can covert the form to a report and thus can even print out the calendar.

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada

    Friday, July 26, 2019 9:06 PM
  • ok thanks to all for very helpful suggestions and contributions.

    I have decided to go for a report with seven subforms/subreports or listboxes attached to the dayno of a date.

    I will also take a look at the suggestions to see what else I can learn!

    Sunday, July 28, 2019 9:14 AM
  • I have decided to go for a report with seven subforms/subreports or listboxes attached to the dayno of a date.

    I will also take a look at the suggestions to see what else I can learn!

    Hi Richard,

    Starting with a form will give you a flexible user interface. Reports are to static in my opinion, as they are just for output.

    If you want to continu the discussions on dynamical form/subform systems, please feel free.

    Imb.

    Sunday, July 28, 2019 7:30 PM