Annual Calendar

  • Thread starter Thread starter Lori
  • Start date Start date
L

Lori

I've downloaded and modified Duane Hookom's amazing Annual Calendar and am
using it in my Access 2003 database for employees to enter in their vacation
schedules. Now, I have two questions and I hope these are possible.

1. Is it possible to set these fields, since they are unbound, to prevent
changes after a data has passed?

and,

2. from the resulting report, I have conditional formatting set up to
highlight certain category "types" if type="v" then the background is yellow,
if "H" then the background is blue, etc. But of course conditional formatting
only allows three conditions. I have NINE!

I've modified the original form to include a total of 10 (white bg for
present) categories, is there a way to apply this same type of coding to the
report?

here is the VB Code used on the form:

Sub RefDates()
Dim D1 As Variant, D2 As Integer, D3 As Integer, TypeAttend
If IsNull(Me![scrEmployee]) Then
MsgBox ("Displaying calendar data can only be done for a specific " _
& "Employee. Select a Employee and continue.")
Exit Sub
End If
Me![scrMonth] = Format(Me![scrCDate], "mmmm")
Me![scrYear] = Format(Me![scrCDate], "yyyy")
D1 = DateSerial(year(Me![scrCDate]), Month(Me![scrCDate]), 1)
D2 = DatePart("w", D1, vbSunday)
Do Until DatePart("w", D1, vbSunday) = 1
D1 = DateAdd("d", -1, D1)
Loop
Me![scr1Date] = D1
D3 = 1
Do Until D3 > 42
Me("C" & Format(D3, "00")) = day(D1)
If Month(D1) <> Month(Me![scrCDate]) Then
Me("C" & Format(D3, "00")).ForeColor = 8421504
Else
Me("C" & Format(D3, "00")).ForeColor = 0
'If Me(strt).ForeColor = -2147483634 Then
End If
TypeAttend = DLookup("AttType", "Attend", "[AttEmp] = " &
Me![scrEmployee] & " AND [AttDate] = #" & Format(D1, "mm/dd/yy") & "#")
If IsNull(TypeAttend) Then
TypeAttend = 0
End If
Select Case TypeAttend
Case 0
Me("C" & Format(D3, "00")).BackColor = 16777215
Case 1
Me("C" & Format(D3, "00")).BackColor = 65280
Case 2
Me("C" & Format(D3, "00")).BackColor = 255
Case 3
Me("C" & Format(D3, "00")).BackColor = 16752543
Case 4
Me("C" & Format(D3, "00")).BackColor = 16362747
Case 5
Me("C" & Format(D3, "00")).BackColor = 16777164
Case 6
Me("C" & Format(D3, "00")).BackColor = 16751052
Case 7
Me("C" & Format(D3, "00")).BackColor = 10079487
Case 8
Me("C" & Format(D3, "00")).BackColor = 12632256
Case 9
Me("C" & Format(D3, "00")).BackColor = 10092543
End Select
D3 = D3 + 1
D1 = DateAdd("d", 1, D1)
Loop
Me.Repaint
End Sub

any help appreciated.
 
I'm not sure which of my "amazing" samples you downloaded. If it's the
Annual Calendar report, it doesn't really have any storing of user data.
There is only a table of dates.

I'm not about to try to decipher all of your code but I find formatting
controls in reports much easier in reports than forms. You can use the On
Format event of a report section to run code and set properties of controls
in the section. I would recommend storing color values in a table if
possible so they can be modified.

Duane Hookom
MS Access MVP

Lori said:
I've downloaded and modified Duane Hookom's amazing Annual Calendar and am
using it in my Access 2003 database for employees to enter in their
vacation
schedules. Now, I have two questions and I hope these are possible.

1. Is it possible to set these fields, since they are unbound, to prevent
changes after a data has passed?

and,

2. from the resulting report, I have conditional formatting set up to
highlight certain category "types" if type="v" then the background is
yellow,
if "H" then the background is blue, etc. But of course conditional
formatting
only allows three conditions. I have NINE!

I've modified the original form to include a total of 10 (white bg for
present) categories, is there a way to apply this same type of coding to
the
report?

here is the VB Code used on the form:

Sub RefDates()
Dim D1 As Variant, D2 As Integer, D3 As Integer, TypeAttend
If IsNull(Me![scrEmployee]) Then
MsgBox ("Displaying calendar data can only be done for a specific " _
& "Employee. Select a Employee and continue.")
Exit Sub
End If
Me![scrMonth] = Format(Me![scrCDate], "mmmm")
Me![scrYear] = Format(Me![scrCDate], "yyyy")
D1 = DateSerial(year(Me![scrCDate]), Month(Me![scrCDate]), 1)
D2 = DatePart("w", D1, vbSunday)
Do Until DatePart("w", D1, vbSunday) = 1
D1 = DateAdd("d", -1, D1)
Loop
Me![scr1Date] = D1
D3 = 1
Do Until D3 > 42
Me("C" & Format(D3, "00")) = day(D1)
If Month(D1) <> Month(Me![scrCDate]) Then
Me("C" & Format(D3, "00")).ForeColor = 8421504
Else
Me("C" & Format(D3, "00")).ForeColor = 0
'If Me(strt).ForeColor = -2147483634 Then
End If
TypeAttend = DLookup("AttType", "Attend", "[AttEmp] = " &
Me![scrEmployee] & " AND [AttDate] = #" & Format(D1, "mm/dd/yy") & "#")
If IsNull(TypeAttend) Then
TypeAttend = 0
End If
Select Case TypeAttend
Case 0
Me("C" & Format(D3, "00")).BackColor = 16777215
Case 1
Me("C" & Format(D3, "00")).BackColor = 65280
Case 2
Me("C" & Format(D3, "00")).BackColor = 255
Case 3
Me("C" & Format(D3, "00")).BackColor = 16752543
Case 4
Me("C" & Format(D3, "00")).BackColor = 16362747
Case 5
Me("C" & Format(D3, "00")).BackColor = 16777164
Case 6
Me("C" & Format(D3, "00")).BackColor = 16751052
Case 7
Me("C" & Format(D3, "00")).BackColor = 10079487
Case 8
Me("C" & Format(D3, "00")).BackColor = 12632256
Case 9
Me("C" & Format(D3, "00")).BackColor = 10092543
End Select
D3 = D3 + 1
D1 = DateAdd("d", 1, D1)
Loop
Me.Repaint
End Sub

any help appreciated.
Click to expand...
 
Back
Top