I am trying to color labels on a calendar-type form, based on a query.
A table contains various reservations at a property with a number of check in and check out dates. I have a form with 93 labels (3 for each day) and I need to color them based on the check in and check out dates. With the code below, I have managed to find the first check in and check out dates (using dlookup) and color the correct labels, indicating the dates that a guest is staying at a place. That said, that only works for one check in-checkout period as I am using dlookup. How do I go about using a query instead as source so I could have more than one check in and check out?
This is the code for the form:
This is the SQL for the reservations that I need to somehow have this code use as source.
SELECT tblProperties.PropertyName AS tblProperties_PropertyName, tblProperties.ID AS tblProperties_ID, tblProperties.OwnerName, tblReservations.ID AS tblReservations_ID, tblReservations.GuestName, tblReservations.PropertyName AS tblReservations_PropertyName, tblReservations.CheckInDate, tblReservations.CheckOutDate, tblReservations.ReservationSource, tblReservations.Comments, [CheckOutDate]-[CheckInDate] AS Nights
FROM tblProperties INNER JOIN tblReservations ON tblProperties.[PropertyName] = tblReservations.[PropertyName];
Does anyone know how to do this?
mafhob
A table contains various reservations at a property with a number of check in and check out dates. I have a form with 93 labels (3 for each day) and I need to color them based on the check in and check out dates. With the code below, I have managed to find the first check in and check out dates (using dlookup) and color the correct labels, indicating the dates that a guest is staying at a place. That said, that only works for one check in-checkout period as I am using dlookup. How do I go about using a query instead as source so I could have more than one check in and check out?
This is the code for the form:
Code:
Private Sub Form_Load()
Dim Initial As Long
Dim Length As Long
Dim Counter As Long
Dim lngGreen As Long
Dim Checkin As Date
Dim Checkout As Date
Dim lblName As String
Dim DayOfWeekIn As Long
Dim DayofWeekOut As Long
lngGreen = RGB(0, 255, 0)
Checkin = DLookup("CheckInDate", "qryReservations", "OwnerName='Jean Nicou'")
Checkout = DLookup("CheckOutDate", "qryReservations", "OwnerName='Jean Nicou'")
DayOfWeekIn = Weekday(Checkin, 2) 'Find out which day of the week this is with Monday as 1
DayofWeekOut = Weekday(Checkout, 2) 'Find out which day of the week this is with Monday as 1
MsgBox WeekdayName(DayOfWeekIn, False, 2) 'Find out the name of the day
MsgBox WeekdayName(DayofWeekOut, False, 2) 'Find out the name of the day
Initial = Day(Checkin) 'Find the day of the month that this reservation starts
Length = Day(Checkout) - Day(Checkin) 'Find the length of the reservation
'First label to color. Always third label of the first day
lblName = "lbl" & Initial & "03"
Me(lblName).BackColor = lngGreen
Me(lblName).ForeColor = lngGreen
'Label to color between first and last
For Position = Initial + 1 To Initial + Length - 1
For Counter = 1 To 3
lblName = "lbl" & Position & "0" & Counter
Me(lblName).BackColor = lngGreen
Me(lblName).ForeColor = lngGreen
Next
Next
'Last Label to color
lblName = "lbl" & Position & "01"
Me(lblName).BackColor = lngGreen
Me(lblName).ForeColor = lngGreen
End Sub
This is the SQL for the reservations that I need to somehow have this code use as source.
SELECT tblProperties.PropertyName AS tblProperties_PropertyName, tblProperties.ID AS tblProperties_ID, tblProperties.OwnerName, tblReservations.ID AS tblReservations_ID, tblReservations.GuestName, tblReservations.PropertyName AS tblReservations_PropertyName, tblReservations.CheckInDate, tblReservations.CheckOutDate, tblReservations.ReservationSource, tblReservations.Comments, [CheckOutDate]-[CheckInDate] AS Nights
FROM tblProperties INNER JOIN tblReservations ON tblProperties.[PropertyName] = tblReservations.[PropertyName];
Does anyone know how to do this?
mafhob