i uploaded the most recent table structure. the relationships have copied over intact.
im currently doing away with the Order Date & Time in tblOrders.
i am putting StartDate & EndDate in tblOrderItems
im currently doing away with the Order Date & Time in tblOrders.
i am putting StartDate & EndDate in tblOrderItems
Code:
Public Sub PopulateForEmployeeAndDate(ByVal Employee As Integer, ByVal DiaryDay As Date)
Dim i As Integer
If DCount("*", "tblEmployeeList", "EmployeeListID = " & Employee) <> 1 Then
'Bad Employee
tblEmployeeList.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
tblEmployeeList.Caption = DLookup("FirstName", "tblEmployeeList", "EmployeeListID = " & EmployeeListID)
Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM tblOrdersItems WHERE EmployeeID = " & Employee & " 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
Last edited: