Calendar (1 Viewer)

[mafhobb]

I have been working on a reservations database that I have been building slowly for the last couple of years as I've had different needs. it is not perfect or even pretty but it does exactly what I need. Now, I am trying to create a calendar based on a couple of tables that contain the reservation and property data.
I have created a form where I can display a calendar, the way I need it, but based on hardcoded data in the vba code. I now need to find a way to extract that data from the tables instead, but this is way over my head.

So, this is the coded data in the test db:

'Info on Reservation 1 to run a test
    Dim Res1Start As Date
    Dim Res1End As Date
    Dim Res1Duration As Long
    Dim Res1PropertyID As Long
    Dim Res1Comment As String

'''''Ultimately these will need to come from two tables
    Res1Start = #7/4/2022#      ' This should be field "CheckInDate" in table "tblreservations"
    Res1End = #7/15/2022#       ' This should be field "CheckOutDate" in table "tblreservations"
    Res1PropertyID = 2         ' This should be field "ID" in table "tblProperties"
    Res1Comment = "Owner"       ' This should be field "GuestName" in table "tblreservations"

There is an unknown number of reservations for an unknown number of properties.

-Question 1: How do I extract this data from the tables?

I use the hardcoded data as seen in the code below to display it on my calendar.

'    Res1 horizontal starting point
    'Check initial date. If out of range, then replace with BeginningofPeriod
    If Res1Start < BeginingofPeriod Then
        Res1Start = BeginingofPeriod
        Res1Comment = "<----" & Res1Comment
    End If
    If Res1End > EndofPeriod Then
        Res1End = EndofPeriod
        Res1Comment = Res1Comment & "---->"
    End If
    Res1Duration = DateDiff("d", Res1Start, Res1End)
    Me.txt1.Left = (DateDiff("d", BeginingofPeriod, Res1Start) + NameBarWidth) * DayLength + (2 / 3 * DayLength) ' The 2/3 day length is added so the reservation starts on the right side of the date
    Me.txt1.Top = VerStartingPoint + (BarHeight * Res1PropertyID + (50 * Res1PropertyID))
    Me.txt1.Width = Res1Duration * DayLength - (1 / 3 * DayLength)  'The 1/3 is subtracted to have a space with reservations that end and start the first day
    Me.txt1.Height = BarHeight
    Me.txt1.Value = Res1Comment
    If Res1Comment = "Owner" Then
        Me.txt1.BackColor = BoxColor
    End If
    Me.txt1.TextAlign = 1

-Question 2: How can I use the data extracted in Question 1 here?

mafhobb

 

[arnelgp]

there is similar db that you might be interested.
it only needs a "little" tweaking.
Holiday Booking Database | Access World Forums (access-programmers.co.uk)

 

[mafhobb]

That database is indeed very close to what I am looking for. I think that I would only need to make two changes to it:

-The first one would be to displace the starting and ending points 1/2 a day to the right as reservations count nights, not days. So I need to find the code that positions the start of the blue bars, used to draw the employee holiday, in this case
Would that be this line?

            .Left = EMPLOYEE_COL_WIDTH + Abs(rs!datestart > Me.CalendarStartDate) _
                * DAY_WIDTH * DateDiff("d", Me.CalendarStartDate, rs!datestart)

-Second, I would need to filter by department, but that I have no idea where to begin to look.
Any suggestion?
mafhobb

 

[arnelgp]

try:

.Width = lngCtlEnd - .Left - 10

if you want big "gap" increase the 10 there.

 

[arnelgp]

tried experimenting:

            .Left = EMPLOYEE_COL_WIDTH + Abs(rs!DateStart > Me.CalendarStartDate) _
                * DAY_WIDTH * DateDiff("d", Me.CalendarStartDate, rs!DateStart) + 20
            .Width = lngCtlEnd - .Left - 24

 

[mafhobb]

Yeap. It worked...This is what I ended up with:

' Modifying the .Left. The day width is 530. The staring point is moved to the right + 315
            .Left = EMPLOYEE_COL_WIDTH + Abs(rs!DateStart > Me.CalendarStartDate) _
                * DAY_WIDTH * DateDiff("d", Me.CalendarStartDate, rs!DateStart) + 315
' Modifying the width of the reservation. It is decreased by 365, with is 315+50(gap)
            .Width = lngCtlEnd - .Left - 365

Now, how can I filter, so only a specific departmtn/departments would show on the calendar?
I believe that it is in this part of the code, but not sure where

Public Function RefreshCalendar( _
Optional RefreshDates As Boolean = False, _
Optional MoveToRecord, _
Optional ReloadEmployees As Boolean = False)
On Error GoTo suberror
Dim MyReDrawEmployees As Boolean
DoCmd.Hourglass True
Dim datFormattedCalStartDate As String
Dim datFormattedCalEndDate As String
Dim strSQL As String
datFormattedCalStartDate = fn.SQLDate(Me.CalendarStartDate)
datFormattedCalEndDate = fn.SQLDate(DateAdd("d", 27, Me.CalendarStartDate))
strSQL = MyReplace("(holidays.datestart < #@@@# and holidays.dateend > #@@@#) or (holidays.datestart between #@@@# and #@@@#) or (holidays.dateend between #@@@# and #@@@#)", _
      datFormattedCalStartDate, datFormattedCalEndDate, datFormattedCalStartDate, datFormattedCalEndDate, datFormattedCalStartDate, datFormattedCalEndDate)
Me.strHolidaysSQL = "select * from holidays where " & strSQL
If Me.chkHideEmptyRows Then
    If Me.chkHideEmptyRows Then Me.AbsolutePosition = 0
    Me.strEmployeesSQL = "select distinctrow qryemployees.* from qryemployees inner join holidays on qryemployees.employeeid=holidays.employeeid where " & strSQL
    Me.strEmployeesSQL = Me.strEmployeesSQL & " order by qryEmployees.departmentname,qryEmployees.fullname"
Else
    Me.strEmployeesSQL = CurrentDb.QueryDefs("qryemployees").SQL
End If
If Not IsMissing(MoveToRecord) Then
        Me.AbsolutePosition = MoveToRecord
        If Me.AbsolutePosition > Me.RecordCount - 1 Then Me.AbsolutePosition = Me.RecordCount - 1
        If Me.AbsolutePosition < 0 Then Me.AbsolutePosition = 0
        Me.SetScrollbarPos
        MyReDrawEmployees = True
End If
If MyReDrawEmployees Or ReloadEmployees Or Me.RecordCount = 0 Or Me.chkHideEmptyRows Then Me.LoadEmployees IIf(Me.chkHideEmptyRows, True, ReloadEmployees)
If RefreshDates Then Me.LoadDates
If ReloadEmployees Then Me.SetScrollbarPos
Me.LoadHolidays
fn.HideHolidayOverlay
DoCmd.Hourglass False
Exit Function
suberror:
LogError Error
End Function

It is in the definition of STRSQL, correct?

strSQL = MyReplace("(holidays.datestart < #@@@# and holidays.dateend > #@@@#) or (holidays.datestart between #@@@# and #@@@#) or (holidays.dateend between #@@@# and #@@@#)", _

      datFormattedCalStartDate, datFormattedCalEndDate, datFormattedCalStartDate, datFormattedCalEndDate, datFormattedCalStartDate, datFormattedCalEndDate)

mafhobb

 

[arnelgp]

try this with department filtering.

 

[arnelgp]

another revision. i added my custom calendar (if you like).

 

[mafhobb]

another revision. i added my custom calendar (if you like).

Wow. Thanks!
This planner with your changes does exactly what I need. I am now going to attempt to merge it with my current database and its structure. If I can get this done it will be a huge help.
My data in my db is stored in the same number of tables and they have the same relationships. The fields are the same, except for the field names, so I will first see if I can import this database into mine and then adjust table and field names. I hope that I can adapt this into my db!

mafhobb

 

[Gasman]

Might want to load your data into the new db?
That way if it does not work, you can go back to step one?

 

[mafhobb]

Ok, so I have uploaded the holiday planner into a simplified version of my db so I could try to do this without risking the original data.
Also attached I have an image of the tables as they are now. The row of three above is from my db and the three below are from the holiday planner. I know that I am probably oversimplifying, but here is what I see

-The tables in my db are nearly identical to the ones in the planner. here are the correspondences:
Table Employees becomes tblproperties
Table Departments becomes tblCleaners
Table Holidays becomes tblReservations

-The field correspondence on tblProperties is this:
EmployeeID is PropertyName
FullName is also PropertyName
DepartmentID is CleanerName

-The field correspondence on tblCleaners is this:
DepartmentID is ClanerName
DepartmentCode is CleanerCompany

-The field correspondence on tblReservations is this:
HolidayID is ID
EmployeeID is PropertyName
DateStart is CheckInDate
DateEnd is CheckOutDate
Notes is GuestName
DateCreated is ReservationDate
Status: I realize that I also need to add an additional filter here and it is that only the reservations with a status of "Avtive" need to be shown in the calendar

Two issues that I see right away:
1- My table relationship between tblProperties and tblcleaner is not the same as Employees and Departments
2-My key in tblproperties is text while in Employees is numbers.
I do not know if any of these two are show stoppers...

So the plan is to go through the code and rename fields and tables. Keep sub names as they are as well as query names. Adjust the querys so they look for data on the correct tables. Is this a good strategy? Any suggestions?

mafhobb

 

[arnelgp]

i have not fully tested this.
i leave that to you.

 

[arnelgp]

i added ControlTooltip so when you hover and pause over the "blue" reservation, it will show you the guest name and the checkin/checkout date.
also remove the "gap" on the blue reservation since you you can know the duration of the reservation through tooltip.

also fix list going back to first record when you press "1 week, 4 week, etc. button. it now stays to the current view.

 

[mafhobb]

This is great. I will be back in front of my computer later today and will test all this.
Will the previous strategy to move the starting point of reservations towards the right side of the day “box” in the calendat still work? This would help much as a reservation can end and a new one begin the same day, and it helps to visually see this, and also organize the cleanings for that day.

 

[mafhobb]

Hi arnelgp.
I've now tested the calendar and I have to say that it is very close to being perfect. Many, many thanks for your help so far. I have found a couple of things that need tweaking and I've tried to do some of the changes myself but I run into erros.
1) I forgot to mention earlier that on this reservation table the reservations can have the status "Active", "Cancelled" and "Temporary". On the calendar only the active ones need to appear. These values are on the field "Status" on tblreservations. Is it possible to change this so only the "Active" ones are added to the calendar?
2) Also on the tblproperties table, there is a field called "Status", which is used also to specify if the properties are "Active", "Not Active" or "n/a". Is it possible to make it so only the "Active" ones show up in the calendar?
3) Finally, it would be fantastic if the reservation "bar" started about 2/3 to the right of the day "box" (on the check in date, of course) and it ended about 1/3 to the right of the day "box" on the checkout day. This clarifies greatly that the check in is in the later part of the day assigned and the check out in the early part of the day, with the cleaning in between. I tried to do this by adjusting some of the code here:

            .Left = EMPLOYEE_COL_WIDTH + Abs(rs!checkindate > Me.CalendarStartDate) _
                * DAY_WIDTH * DateDiff("d", Me.CalendarStartDate, rs!checkindate) + 353
            .Width = lngCtlEnd - .Left - 353

The + and then -353 positions the start and end of the reservations correctly, which is great, however in some instances I get an error "The control or subform control is too large for this location" but I do not know what it is referring to. Also, it has an odd effect causing the reservations on the far left and right of the calendar to not go all the way out to the edge of the calendar. This last thing is, of course, a very minor issue, but it might have something to do with the error? I have uploaded the db as is now and also an image of the error.

 

[arnelgp]

you make sure that you do not have any Overlapping Reservation dates (i see many overlapping on your reservations).
if you have overlapping, you will not see the "gap" between them.

try going to date: mar-12-2021 and you will see the "gap" that i made.

like i said, if you want to see these "gaps", ensure there is no Overlap on the reservations.
only Active (properties and reservations) are included on the calendar.

 

[mafhobb]

Hi arnelgp.
Thank you for the last db version. I am reviewing the calendar that is displayed when the date 12-Mar-2021 is entered and here is what I found:
-As you mention, there seems to be some overlapping reservations, however when I look at the reservations table, the ones that overlap are reservations marked as "Cancelled", so they should not show up at all. I went through the code (what my limited level allows) and I can see that you added "Active" as parameter on both the property queries and the reservation queries, however it does not seem to be having an effect on the reservation queries.
An example of this are reservations 182 (Active) and 57 (Cancelled). Reservation 182 is from April 1st to 6th and 57 is April 2nd to 9th. On the calendar, they overlap, however reservation 57 is marked as "Cancelled" and it should not appear. The same thing happens with reservations 185 and 4. Why would the query ignore this parameter?
-One other thing that I noticed is that when a reservation ends on a specific date and another one starts on the same date, the code does produce a gap between these two reservations on the calendar, however when a reservation ends on one day and the next one starts the next day, then there is no gap. Is it possible to make all reservations end in the same location in the day "box" (about 1/3 to the right) and also start in the same location (about 2/3 to the right)? This happens with reservations 256 and 257, Also with 30 and 33 and then 130.Also 2 and 178.
By the way, the show only "Active" properties does work real well and the calendar is going to be a really big help!

 

[arnelgp]

check if "Cancel" reservations is not included.
about the "gap", i am leaving that to you. experiment with some values.

 

[mafhobb]

Hi arnelgp.
Cancelled reservation are not not shown in the calendar...THANKS YOU!!!
I will see if I can figure out the gap deal. Since the effect that your coding added is not the same as the one that I did Could you indicate where is it that you adjusted it? I belice the it is here:

            'arnelgp
            If current_ID = rs!id And rs!CheckInDate <= last_Date Then
                With Me("lblHoliday" & last_Position)
                    .Width = .Width - ((DAY_WIDTH \ 2) + 20)
                End With
                
                .Left = EMPLOYEE_COL_WIDTH + Abs(rs!CheckInDate > Me.CalendarStartDate) _
                    * DAY_WIDTH * DateDiff("d", Me.CalendarStartDate, rs!CheckInDate) + ((DAY_WIDTH \ 2) + 20)
                .Width = lngCtlEnd - .Left
                
            Else
            'end of arrnelgp
                .Left = EMPLOYEE_COL_WIDTH + Abs(rs!CheckInDate > Me.CalendarStartDate) _
                    * DAY_WIDTH * DateDiff("d", Me.CalendarStartDate, rs!CheckInDate)
                .Width = lngCtlEnd - .Left
            End If
            .Visible = True
            current_ID = rs!id: last_Position = rs.AbsolutePosition: last_Date = rs!CheckOutDate
        End With

Correct?
mafhobb

 

[arnelgp]

Correct?

yes.