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:
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.
-Question 2: How can I use the data extracted in Question 1 here?
mafhobb
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:
Code:
'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.
Code:
' 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