Annual Calendar

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.
 
D

Duane Hookom

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calendar Form-Dlookup not working 0
VBA - exporting serial letters to single documents 0
conditional formula needed 2
macro hangs if no data is found 6
Please suggest any formulae / functions 2
using more than one detail record in one line on report 2
EXPORT with IF Statement 2
My eyes Are Tiered, Forehead Bruised, My Desk Dented.!! Help??? 1

Top