complex schedule / appointment book (1 Viewer)

[shutzy]

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

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

 

[VilaRestal]

OK, thanks for redoing the code and uploading the database. Leave it with me for a day or two to give a considered answer. (Very busy at work at the moment.)

I should say that those names don't look random to me and nore do the appointments. I think you should edit that post and remove the attachment. In future, if posting databases delete all the real data first. Hopefully, no harm done this time.

 

[shutzy]

thanks for spending time on this. yes the names are real and so are the appointments. i thought that it would be ok if no contact info was there. ie email mobile etc. i do understand that if there is an appointment with it then it does carry a little more weight and i do hope that i never meet that person who is devious enough to miss use these forums.

i have removed it now. the main reason i left it there is because there is so much data that it is really a great test if it all fits in place.

thanks again

 

[VilaRestal]

I don't think anyone could misuse any of the data that was there.
It's just really in case anybody sees their own name in there. Highly unlikely I'm sure but better not to leave it posted for long

As I said, I'll get on to this in a day or two.

 

[shutzy]

hi, i am wondering if you have had chance to look over my database and how to integrate your diary.

not pushing just wondering.

thanks