thanks for the reply. i kind of knew that the crosstab query would be an option. when i tried to create 1 i got the column header as the employee and each employee was added automatically dependant on the date and if they had any treatments in. i do also appreciate that turning the results of the query into a functional diary would require code beyond my own capabilities. so at least i know its an option if i ever do need to go beyond the capabilities of the DiaryDemo.accdb.
i have gone through the sub routine and im unsure if i have done it correctly. i dont really follow which table that the code is refering to when it wants the EmployeeID.
ive put it below
Public Sub PopulateForEmployeeAndDate(ByVal Employee As Integer, ByVal DiaryDay As Date)
Dim i As Integer
If DCount("*", "tblEmployeeList", "EmployeeListID = " & Employee) <> 1 Then
'Bad Employee
lblEmployee.Caption = "ERROR"
EmployeeID = 0
For i = 1 To PERIODCOUNT
With Me.Controls("txt" & i)
.Value = ""
.BackColor = RGB(127, 127, 127)
End With
Next i
Else
EmployeeListID = Employee
DiaryDate = DiaryDay
lblEmployee.Caption = DLookup("FirstName", "tblEmployeeList", "EmployeeListID = " & EmployeeListID)
Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM tblOrdersItems WHERE EmployeeID = " & EmployeeID & " AND Int(StartDate) = " & CLng(DiaryDate)
Debug.Print strSQL
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
If rs.RecordCount = 0 Then
For i = 1 To PERIODCOUNT
With Me.Controls("txt" & i)
.Value = ""
.BackColor = vbWhite
End With
Next i
Else
rs.MoveFirst
Dim StartTime As String, EndTime As String
Dim StartHour As Integer, StartMinute As Integer, EndHour As Integer, EndMinute As Integer
Dim PeriodsCovered As Integer, StartPeriod As Integer, LoopEnd As Integer
Dim OrdersItemsID As Integer
Do While Not rs.EOF
ActivityID = rs!ActivityID
StartTime = Format(rs!StartDate, "hh:mm")
StartHour = Left(StartTime, 2)
StartMinute = Right(StartTime, 2)
EndTime = Format(rs!EndDate, "hh:mm")
EndHour = Left(EndTime, 2)
EndMinute = Right(EndTime, 2)
PeriodsCovered = 4 * (EndHour - StartHour) + (EndMinute / 15 - StartMinute / 15) - 1
StartPeriod = 4 * (StartHour - EarliestHour) + StartMinute / 15 + 1
LoopEnd = StartPeriod + PeriodsCovered
If LoopEnd > PERIODCOUNT Then LoopEnd = PERIODCOUNT
For i = StartPeriod To LoopEnd
With Me.Controls("txt" & i)
.Value = DLookup("Items", "tblItems", "ID = " & ItemsID)
Select Case ActivityID
Case 1
.BackColor = RGB(127, 255, 255)
Case Else
.BackColor = RGB(200, 200, 200)
End Select
End With
Next i
rs.MoveNext
Loop
End If
rs.Close
End If
End Sub
thanks again