Results 1 to 8 of 8
Thread: Calendaring Stuff

101703, 17:37 #1Registered User
 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

101703, 18:09 #2Registered User
 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.

101703, 18:23 #3Registered User
 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

101703, 19:08 #4Registered User
 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?

101703, 19:14 #5Registered User
 Join Date
 Oct 2003
 Posts
 24
yes
how did you make your form ?
the one with the calendar ?
kevin

102003, 10:17 #6Registered User
 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

102003, 11:54 #7Registered User
 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.
kevinLast edited by kevinrea; 102003 at 11:59.

102003, 13:00 #8Registered User
 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 quickanddirtyaslongasitworks 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 17, rather than 06, 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 17, 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