Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    24

    Unanswered: Calendaring Stuff

    Hi guys,

    I am trying to make a month display calendar in ms access, that will show the subject line of each appt in the one month view.
    So, if you have 7 appts in one day, i want it to show up on the one month calendar with a one liner of text and then you can click on that day and see the expanded view for the day...
    any ideas ?

    thanks,
    kevin

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    What I did was I created the calendar for the year, and each box behind the number gets a different colour. Each colour corresponds to a different number. For example, yellow could mean 1 appointment. The days outlined in black are statutory holidays.

    This just one idea. If this solution is of interest to you, I could post more info on it.
    Attached Thumbnails Attached Thumbnails cal.jpg  

  3. #3
    Join Date
    Oct 2003
    Posts
    24

    ok..

    and then does the description show up on the text area down and to the right ?

    if so, I think that might work...

    kevin

  4. #4
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Pointing to a specific day will let you know how many appointments there are. I have it set such that if you double click on a day, a report comes up, but you could easily make a form pop up showing the appointment details.

    You asked for any ideas. Did you want any other ideas?

  5. #5
    Join Date
    Oct 2003
    Posts
    24

    yes

    how did you make your form ?
    the one with the calendar ?

    kevin

  6. #6
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    1. I created a form with a bunch of unbound fields.
    2. Each month needs 37 boxes since the first month will not
    always be January on my form. I get the start date from
    another form.
    3. I named each box for month 1 m1d1 to m1d37
    4. I have a single label for the SMTWTFS with spaces to line
    up with the columns (I had to do it this way since I reached
    the maximum number of controls on a form.
    5. Each month has an unbound box for the name of the month
    which are named m1 through to m12
    6. I have a module to figure out which days of the year are
    holidays for my country.
    7. On open of the form:

    Private Sub Form_Open(Cancel As Integer)
    Dim StartDate As Date, iBox As Integer, iD As Integer, iM As Integer, en As Integer, iDays As Integer, iDay As Integer, dt As Date, tx1 As String, tx2 As String
    Dim rs As Recordset, qdf As QueryDef, Colour, test As Integer, MD As String, rh As Recordset, rl As Recordset
    Dim STx1 As String, STx2 As String, PTx1 As String, PTx2 As String, intHolYrs As Integer, StartMonth As Integer, Mth As Integer, stTmp As String

    intHolYrs = 1 'default to getting the holidays for only one year
    AllYears = [Forms]![YrCalSel]![AllYears] 'Birthdays that occur each year on same day are all years. Sales figures change each year
    frmSDate = [Forms]![YrCalSel]![StartDate]
    Me.Caption = "Full Year Calendar: " & Forms!YrCalSel!cmbReport.Column(0) 'user can choose between birthdays of employees or sales figures
    If Month(frmSDate) <> 1 Then intHolYrs = 2 'if the chosen start month is not january, get 2 years of holidays
    stTmp = Format(frmSDate, "MM/DD")
    If AllYears Then
    PopHolidays Year(Date), intHolYrs
    StartDate = CDate(Year(Date) & "/" & stTmp) 'Fiscal Start Date?
    Else
    PopHolidays Year(frmSDate), intHolYrs 'inserts records into the Holidays table for upto 2 years
    StartDate = CDate(frmSDate) 'Fiscal Start Date?
    End If
    Set rs = CurrentDB().OpenRecordset("tblYrCal")
    If AllYears Then
    Do While Not rs.EOF
    If rs!theDate >= stTmp Then Exit Do
    rs.MoveNext
    Loop
    End If
    StartMonth = Month(StartDate)
    Set rh = CurrentDB().OpenRecordset("SELECT FromDate FROM Holidays WHERE FromDate >=#" & Format(StartDate, "yyyy/mm/dd") & "#")
    For iM = 1 To 12
    Mth = StartMonth + iM - 1
    If Mth > 12 Then Mth = Mth - 12
    iBox = Weekday(StartDate, 1) '1 = vbSunday which is the default, but if we want to change it...
    iDays = DateAdd("m", 1, StartDate) - StartDate 'DaysInMonth(StartDate)
    Me.Controls("m" & iM) = Format(StartDate, "MMMM YYYY")
    en = 0
    For iD = 1 To 37
    If iD = iBox Then en = -1
    If iD = iBox + iDays Then en = 0
    Me.Controls("m" & iM & "d" & iD).Enabled = en
    If en Then
    Me.Controls("m" & iM & "d" & iD) = iD - iBox + 1
    iDay = iD - iBox + 1

    If rh.EOF Then GoTo SkipHolidays
    If AllYears Then
    MD = Right("00" & Mth, 2) & "/" & Right("00" & iDay, 2)
    test = MD = Format(rh!FromDate, "MM/DD")
    Else
    dt = DateSerial(Year(StartDate), Mth, iDay)
    test = dt = rh!FromDate
    End If


    If test Then
    Me.Controls("m" & iM & "d" & iD).BorderColor = 0
    Me.Controls("m" & iM & "d" & iD).BorderWidth = 2
    rh.MoveNext
    Else
    Me.Controls("m" & iM & "d" & iD).BorderColor = 9868950
    Me.Controls("m" & iM & "d" & iD).BorderWidth = 1
    End If
    SkipHolidays:
    If rs.EOF Then
    If AllYears Then
    rs.MoveFirst
    Else
    GoTo Skip
    End If
    End If

    If AllYears Then
    MD = Right("00" & Mth, 2) & "/" & Right("00" & iDay, 2)
    test = MD = rs!theDate
    dt = DateSerial(Year(Date), iM, iDay)
    Else
    test = dt = rs!theDate
    dt = DateSerial(Year(StartDate), Mth, iDay)
    End If

    If test Then
    Me.Controls("m" & iM & "d" & iD).BackColor = rs!Colour
    Me.Controls("m" & iM & "d" & iD).ControlTipText = rs!ControlTipText
    rs.MoveNext
    Skip:
    End If
    End If
    Next iD
    StartDate = DateAdd("m", 1, StartDate)
    Next iM
    rs.Close
    End Sub

  7. #7
    Join Date
    Oct 2003
    Posts
    24

    wow...

    thank you very much..
    you have certainly given me a lot of good information.

    I was wondering, on the monthly layout of your form..
    how many days do you have laid across in each week horizontally ?

    and also, did you put all the months into one form, or is ther 12 forms to hold all the months ?

    and one more question , What is the code that goes in the module you said that you have ?
    I am guessing that the code you listed is in the form.


    kevin
    Last edited by kevinrea; 10-20-03 at 11:59.

  8. #8
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    I put 7 boxes per line with the last line having 2 boxes to total 37.

    All 12 months are on one form. I was planning on having this as a launching point for other forms, either monthly, weekly or daily, but for now I just lauch reports.

    My PopHolidays report clears a temporary table called Holidays then recalculates and inserts a record for each holiday based on the starting date. If January first is not the start date, I get it to insert 2 years worth of holidays. This program is pretty useless unless you have the same holidays as Canada. Easter was the fun one! I have included the fully commented code and the quick-and-dirty-as-long-as-it-works code. I got the algorithm off the internet somewhere...

    Public Function EasterLong(Y As Integer) As Date
    'This will calculate the date of easter for a given year
    Dim G As Integer 'Golden number
    Dim S As Integer 'solar correction
    Dim L As Integer 'lunar correction
    Dim P As Integer 'Paschal full moon, the full moon which Easter is the Sunday after
    'Dim P2 As Integer 'uncorrected date for the Paschal full moon
    Dim D As Integer 'Dominical number
    Dim D2 As Integer 'the date on which the first Sunday of the year falls
    Dim P3 As Integer 'first date in the year which falls on the same day of the week as the Paschal full moon
    Dim x2 As Integer 'difference between d2 and p3
    Dim E As Integer 'the number of days Easter falls after 21st March
    Dim tmp As Integer

    'The tables which appear here and, in more detail, in the Book of Common Prayer
    'can be used to determine the date of Easter. Underlying these tables is a relatively
    'simple algorithm which is described here. This description assumes a limited mathematical
    'knowledge, particularly of integer division and remainders, or modulo arithmetic.
    'The algorithm applies to any year since the introduction of the Gregorian Calendar,
    'which in Britain was in September 1752.

    'We refer to the year number as y, and use it to calculate the Golden number, g:
    G = (Y Mod 19) + 1

    'Next we calculate the date of the Paschal full moon, that is, the full moon which Easter
    'is the Sunday after. This is done in several stages. First we calculate two values
    'called the solar correction, s, and the lunar correction, l.
    S = (Y - 1600) \ 100 - (Y - 1600) \ 400
    L = (((Y - 1400) \ 100) * 8) \ 25

    'Next we calculate an uncorrected date for the Paschal full moon, p2; then we apply a
    'minor correction, to get the exact date, p, as the number of days after 21st March.
    P = (3 - 11 * G + S - L) Mod 30
    If P < 0 Then P = P + 30
    If (P = 29) Or (P = 28 And G > 11) Then
    P = P - 1
    Else
    P = P
    End If

    'Now we need to determine the date of the following Sunday. First we calculate
    'the 'Dominical number', d:
    D = (Y + (Y \ 4) - (Y \ 100) + (Y \ 400)) Mod 7
    If D < 0 Then D = D + 7

    'Note that this is the number from which the Dominical letter is determined, and we
    'calculate d2, which is the date on which the first Sunday of the year falls:
    'd2 = (8 - d) Mod 7

    'We already have p, the date of the Paschal full moon in days after 21st March.
    'Next we determine p3 the first date in the year which falls on the same day of the
    'week as the Paschal full moon. First we determine the 'day number' of p with respect
    'to 1st January. This is 31 + 28 + 21 + p = 80 + p. (Note that we can disregard possible
    'occurences of 29th February, because the calculation of d has already taken this into
    'account, and we shall see that these two values will cancel each other out.) p3 is
    'then given by the formula:
    'p3 = (80 + p) Mod 7
    'p3 = (3 + p) Mod 7

    'The difference between d2 (the first Sunday in the year) and p3 (the day of the week
    'when the Paschal full moon falls) gives us the number of days that must be added to p
    'to get the date of the following Sunday, which is Easter Day. There is one further
    'subtlety. This number must lie in the range 1-7, rather than 0-6, since Easter is not
    'allowed to fall on the same day as the Paschal full moon. We first determine x2, the
    'difference between d2 and p3:
    'x2= d2 - p3
    'x2= (8 - d) mod 7 - (3 + p) mod 7
    'x2= (8 - d - (3 + p)) mod 7
    'x2 = (5 - d - p) Mod 7

    'To force this to lie in the range 1-7, we calculate x
    'x = (x2 - 1) Mod 7 + 1
    'x = (4 - d - p) Mod 7 + 1

    'We can now calculate e, the number of days Easter falls after 21st March:
    'E = p + x
    tmp = (4 - P - D) Mod 7
    If (tmp < 0) Then tmp = tmp + 7
    E = P + 1 + tmp

    EasterLong = DateAdd("d", E, CDate(CStr(Y) & "/03/21"))

    End Function

    Public Function GoodFriday(Yr As Integer) As Date
    'This will calculate the date of Good Friday for a given year
    'This is the same as the EasterLong above without the comments and less 2 days
    Dim golden As Integer 'Golden number
    Dim solar As Integer 'solar correction
    Dim lunar As Integer 'lunar correction
    Dim pfm As Integer 'Paschal full moon, the full moon which Easter is the Sunday after
    Dim dom As Integer 'Dominical number
    Dim tmp As Integer
    Dim E As Integer 'the number of days Easter falls after 21st March

    golden = Yr Mod 19 + 1
    solar = (Yr - 1600) \ 100 - (Yr - 1600) \ 400
    lunar = (((Yr - 1400) \ 100) * 8) \ 25
    pfm = (3 - 11 * golden + solar - lunar) Mod 30
    If pfm < 0 Then pfm = pfm + 30
    If (pfm = 29) Or (pfm = 28 And golden > 11) Then pfm = pfm - 1

    dom = (Yr + (Yr \ 4) - (Yr \ 100) + (Yr \ 400)) Mod 7
    If dom < 0 Then dom = dom + 7

    tmp = (4 - pfm - dom) Mod 7
    If (tmp < 0) Then tmp = tmp + 7

    E = pfm + 1 + tmp

    GoodFriday = DateAdd("d", E - 2, CDate(CStr(Yr) & "/03/21")) 'the -2 is for good friday
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •