To do the Week view you need a crosstab query. Where the dates become the columns. However in order to ensure this works, you need to make sure there is a value for every day. One way to do this is to create a giant table of dates and do a left join on that. Since I only need 7 dates at a time, I will create a table of dates and fill it with the seven dates I need. Makes it a lot simpler.
tblDates
dateNumber ' 1 to 7
dtmDate ' the actual date
this table only holds the seven dates at a time. That datenumber field is key because it is used in the crosstab as a column heading and my table can be bound to it. If you did not do this you would have to programmatically change the recordsource or the headings.
Now the next big trick is that you need a row heading for the crosstab. So I had to do a ranking query. I could not think of any other way to do this, but there might be a better way. So Here is the Prep that I did
SQL:
qryWeekViewPrep
SELECT
tblDates.DateNumber,
tblDates.dtmDate,
[AbsenceCode]+": "+[EmplName]+", "+[EmpFName] AS EventInfo,
Count(*) AS Rank
FROM
(tbluEmployees
RIGHT JOIN
(tbluAbsenceCodes
RIGHT JOIN
(tblDates LEFT JOIN tbl_YearCalendar AS YC1 ON tblDates.dtmDate = YC1.AbsenceDate)
ON
tbluAbsenceCodes.AbsenceID = YC1.AbsenceID)
ON
tbluEmployees.EmployeeID = YC1.EmployeeID)
LEFT JOIN tbl_YearCalendar AS YC2
ON (YC1.AttendanceID >= YC2.AttendanceID) AND (YC1.AbsenceDate = YC2.AbsenceDate)
GROUP BY
tblDates.DateNumber,
tblDates.dtmDate,
[AbsenceCode]+": "+[EmplName]+", "+[EmpFName]
ORDER BY tblDates.dtmDate;
The join looks pretty complicated but the only tricky part is
LEFT JOIN tbl_YearCalendar AS YC2
ON (YC1.AttendanceID >= YC2.AttendanceID) AND (YC1.AbsenceDate = YC2.AbsenceDate)
That is used to get your rank. So if 4 records occur on a given day they will be numbered 1 to 4
Now the crosstab is this
SQL:
TRANSFORM
First(qryWeekViewPrep.EventInfo) AS FirstOfEventInfo
SELECT
qryWeekViewPrep.Rank
FROM
qryWeekViewPrep
GROUP BY
qryWeekViewPrep.Rank
PIVOT
qryWeekViewPrep.DateNumber;
The form code is pretty simple
Code:
Private Sub Form_Load()
'I could code here to pass in the starting day in week using open args
'Or you could do it after the form opens using the SetStartingDay
Dim DayInTheWeek As Date
DayInTheWeek = Date
FirstDayOfWeek = (GetFirstDayOfWeek(DayInTheWeek, vbSunday))
UpdateCalendar
End Sub
Public Sub SetStartingWeek(DayInTheWeek As Date)
'if not using open args you could pass a day to start the calendar
Me.FirstDayOfWeek = (GetFirstDayOfWeek(DayInTheWeek, vbSunday))
UpdateCalendar
End Sub
Public Property Get FirstDayOfWeek() As Date
FirstDayOfWeek = mFirstDayOfWeek
End Property
Public Property Let FirstDayOfWeek(ByVal Value As Date)
mFirstDayOfWeek = Value
End Property
Private Sub cmdNext_Click()
FirstDayOfWeek = FirstDayOfWeek + 7
UpdateCalendar
End Sub
Private Sub cmdPrevious_Click()
FirstDayOfWeek = FirstDayOfWeek - 7
UpdateCalendar
End Sub
Public Function GetFirstDayOfWeek(DayInWeek As Date, Optional StartDay As vbDayOfWeek = vbSunday)
GetFirstDayOfWeek = DayInWeek - Weekday(DayInWeek, StartDay) + 1
End Function
Public Sub UpdateCalendar()
SetLabels
LoadDates
End Sub
Public Sub SetLabels()
Dim I As Integer
Dim labelDay As Date
labelDay = FirstDayOfWeek
Me.lblDateRange.Caption = Format(labelDay, "dd MMM yyyy") & " - " & Format(labelDay + 6, "dd MMM yyyy")
For I = 1 To 7
Me.Controls("lblDay" & I & "number").Caption = Format(labelDay + (I - 1), "dd")
Next I
End Sub
Public Sub LoadDates()
Dim strSql As String
Dim I As Integer
'Delete out old dates
strSql = "Delete * from tblDates"
CurrentDb.Execute strSql
For I = 0 To 6
strSql = "Insert into tblDates (dateNumber,dtmDate ) values (" & I + 1 & ", " & SQLDate(FirstDayOfWeek + I) & ")"
Debug.Print strSql
CurrentDb.Execute strSql
Next I
Me.Requery
End Sub
Function SQLDate(varDate As Variant) As Variant
If IsDate(varDate) Then
If DateValue(varDate) = varDate Then
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
Else
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
End If
End Function
You would pass in as starting date somehow. I did not code openargs but you could. I did provide a method to pass in a date after the form loads. This will work fine unless you open this form as dialog. If you pass in a date (or use the default) it calculates the first day of the week. It loads the labels starting from that date. Then it fills tblDate with the 7 required dates. The crosstab does everything else.
Lesson 2 would be clicking on a cell and opening the form to add/edit.
1) To do this you would return the value in the cell
2) if there is a value parse it to find the empolyee name
3) calculate which day it is by getting the field name (1,2,3,4...) and the the starting date which is a property of the form
4) use a dlookup to open to that record just like in the year calendar.
Here is a link
be careful of the spam.
What I have read from this forum MajP is a great teacher!
