Carrying over a name to a subform

[Ray]

I'm sorry this is a long post but I though I had better include
everything as I'm new to VB.

I have a a form frmCalendarMain which has five subforms (unbound).
Each form is a reservation calendar frmCalendarRm1,
frmCalendarRm2....Rm5 etc. At the moment if I click on a field which
is already booked then I can open the reservation form (frmGuests) by
calling (GetContract). This workes fine, however I would like the user
to be able to click on a blank date in one of the subforms and when
the frmGuests form opens the subform (frmTemp) will automatically
enter in the [RoomNo] the appropriate room no from the frmCalendarRm1
= 1 and will also enter in to the [BeginDate] the date from the label
of frmCalendarRm1. Is this possible. It maybe a big ask.... but if you
don't ask you'll never learn.

Thanks guys for all your help. I just hope the above makes sense in
conjunction with the code below. TIA - Ray

Dim intFirst As Integer, intLast As Integer, intLastDay As Integer
Dim intMonth As Integer, intYear As Integer

Public Sub SetDays1()
Dim intI As Integer, intJ As Integer, strNum As String

' First, clear all the boxes
For intI = 1 To 42
' Controls are named "lblnn" and "txtnn"
' Where nn = 01 to 42
' Using Format to get 2 digits
strNum = Format(intI, "00")
' Clear the day number
Me("lbl" & strNum).Caption = ""
' Clear the contents of the text box
Me("txt" & strNum).Value = ""
' Grey out the background
Me("txt" & strNum).BackColor = 255

' And disable it
Me.Text111.SetFocus
Me("txt" & strNum).Enabled = False
Next intI

intMonth = Forms!frmCalendarMain!CboMonth
intYear = Forms!frmCalendarMain!CboYear
' The first box to set is the weekday of the first day of the
month
intFirst = Weekday(DateSerial(intYear, intMonth, 1), vbSunday)
' Calculate the last day number
intLastDay = Day(DateAdd("m", 1, DateSerial(intYear, intMonth, 1))
- 1)
' .. and the last box to set
intLast = intFirst + intLastDay - 1

' Now set up all the boxes for the current month
intJ = 1
For intI = intFirst To intLast
strNum = Format(intI, "00")
' Put the day number in the associated label caption
Me("lbl" & strNum).Caption = intJ
' If the day is a weekend then make the background of the box
blue
Me("txt" & strNum).BackColor = IIf(Weekday(DateSerial(intYear,
intMonth, intJ), vbMonday) > 5, 16763904, 16777215)

Dim rsHoliday As Recordset, sqlHoliday As String
Dim iHoliday As Integer
Dim dHoliday(1 To 10) As Date

sqlHoliday = "SELECT Holiday FROM [tblholiday] " & _
"WHERE (DatePart('m', Holiday) = " & intMonth & _
" AND DatePart('yyyy', Holiday) = " & intYear & ");"

Set rsHoliday = CurrentDb().OpenRecordset(sqlHoliday)

iHoliday = 0

Do While Not rsHoliday.EOF
iHoliday = iHoliday + 1
dHoliday(iHoliday) = rsHoliday!Holiday
rsHoliday.MoveNext
Loop

' Then the statement in the loop becomes:

If iHoliday > 0 Then
For nCounter = 1 To iHoliday
If DateSerial(intYear, intMonth, intJ) =
dHoliday(nCounter) Then
Me("txt" & strNum).BackColor = 4227327
End If
Next nCounter
End If

' end of holiday code

' and Enable it
Me("txt" & strNum).Enabled = True
intJ = intJ + 1
Next intI

' Hide the last row if we didn't use it
If intLast < 36 Then
intJ = False
Else
intJ = True
End If
For intI = 36 To 42
Me("txt" & intI).Visible = intJ
Next intI

' Now call the procedure to insert the data for the current
group
cmbRoom_AfterUpdate

End Sub

Private Sub cmbRoom_AfterUpdate()

Dim db As DAO.Database, rst As DAO.Recordset
Dim datFirstDate As Date, datLastDate As Date, strSQL As String
Dim intStart As Integer, intEnd As Integer, intI As Integer, strNum As
String

' Clear all the text boxes
For intI = 1 To 42
strNum = Format(intI, "00")
Me("txt" & strNum).Value = ""
Next intI

' Get a pointer to the current database
Set db = CurrentDb

' Calculate the start and end date for the current month
datFirstDate = DateSerial(intYear, intMonth, 1)
datLastDate = DateSerial(intYear, intMonth, intLastDay)

' Set up to select the guest bookings that are in the current
month
strSQL = "SELECT * FROM qryGuestCalendar " & _
"WHERE RoomNo = 1 AND BeginDate <= #" & datLastDate & _
"# And EndingDate >= #" & datFirstDate & "#"

Set rst = db.OpenRecordset(strSQL)

'Loop through all the rows and set the Guest's names in the
calendar
Do Until rst.EOF
' Calculate the start point
If rst!Begindate < datFirstDate Then
intStart = intFirst
Else
intStart = Day(rst!Begindate) + intFirst - 1
End If
' Calculate the end point
If rst!Endingdate > datLastDate Then
intEnd = intLast
Else
intEnd = Day(rst!Endingdate) + intFirst - 1
End If

' Set in the Reservation Number & Guest's name in the
appropriate days
For intI = intStart To intEnd
strNum = Format(intI, "00")

Me("txt" & strNum).Value = Me("txt" & strNum).Value & _
rst!GuestID & " / " & rst!ReservationNo & vbCrLf

Next intI

' Get the next row
rst.MoveNext
Loop

rst.Close

End Sub
Private Sub GetContract(varValue As Variant)
Dim intGuest As Integer, intI As Integer

' Do nothing if the control contained no value
If IsNothing(varValue) Then
DoCmd.OpenForm "frmGuests", acNormal, , , acFormAdd,
acWindowNormal

Else

' Extract the Reservation number
intI = InStr(varValue, " ") ' Find the ending blank
intGuest = Left(varValue, intI - 1)

' Open the Reservation form
DoCmd.OpenForm "frmGuests", WhereCondition:="GuestID = " &
intGuest

End If

End Sub

[PC Datasheet]

Ray,

You need to relook at the design of your tables - they don't appear to be
correct. The fact that you are using five subforms, one for each room, indicates
there's a problem there. One of your tables has a field for each room which is
incorrect. You should have a table like:
TblRoom
RoomID
RoomNum
<<Other Fields That Describe The Room>>

and you should have a record in this table for each room.

Then you should have a table like:
TblReservation
ReservationID
RoomID
StartDate
EndDate
<<Other Fields that describe the reservation for a specific room>>

and you should have a record in this table for each time a reservation is made
for a specific room.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"Ray" <wa*****@xtra.co.nz> wrote in message
news:42**************************@posting.google.c om...

I'm sorry this is a long post but I though I had better include
everything as I'm new to VB.

I have a a form frmCalendarMain which has five subforms (unbound).
Each form is a reservation calendar frmCalendarRm1,
frmCalendarRm2....Rm5 etc. At the moment if I click on a field which
is already booked then I can open the reservation form (frmGuests) by
calling (GetContract). This workes fine, however I would like the user
to be able to click on a blank date in one of the subforms and when
the frmGuests form opens the subform (frmTemp) will automatically
enter in the [RoomNo] the appropriate room no from the frmCalendarRm1
= 1 and will also enter in to the [BeginDate] the date from the label
of frmCalendarRm1. Is this possible. It maybe a big ask.... but if you
don't ask you'll never learn.

Thanks guys for all your help. I just hope the above makes sense in
conjunction with the code below. TIA - Ray

Dim intFirst As Integer, intLast As Integer, intLastDay As Integer
Dim intMonth As Integer, intYear As Integer

Public Sub SetDays1()
Dim intI As Integer, intJ As Integer, strNum As String

' First, clear all the boxes
For intI = 1 To 42
' Controls are named "lblnn" and "txtnn"
' Where nn = 01 to 42
' Using Format to get 2 digits
strNum = Format(intI, "00")
' Clear the day number
Me("lbl" & strNum).Caption = ""
' Clear the contents of the text box
Me("txt" & strNum).Value = ""
' Grey out the background
Me("txt" & strNum).BackColor = 255

' And disable it
Me.Text111.SetFocus
Me("txt" & strNum).Enabled = False
Next intI

intMonth = Forms!frmCalendarMain!CboMonth
intYear = Forms!frmCalendarMain!CboYear
' The first box to set is the weekday of the first day of the
month
intFirst = Weekday(DateSerial(intYear, intMonth, 1), vbSunday)
' Calculate the last day number
intLastDay = Day(DateAdd("m", 1, DateSerial(intYear, intMonth, 1))
- 1)
' .. and the last box to set
intLast = intFirst + intLastDay - 1

' Now set up all the boxes for the current month
intJ = 1
For intI = intFirst To intLast
strNum = Format(intI, "00")
' Put the day number in the associated label caption
Me("lbl" & strNum).Caption = intJ
' If the day is a weekend then make the background of the box
blue
Me("txt" & strNum).BackColor = IIf(Weekday(DateSerial(intYear,
intMonth, intJ), vbMonday) > 5, 16763904, 16777215)

Dim rsHoliday As Recordset, sqlHoliday As String
Dim iHoliday As Integer
Dim dHoliday(1 To 10) As Date

sqlHoliday = "SELECT Holiday FROM [tblholiday] " & _
"WHERE (DatePart('m', Holiday) = " & intMonth & _
" AND DatePart('yyyy', Holiday) = " & intYear & ");"

Set rsHoliday = CurrentDb().OpenRecordset(sqlHoliday)

iHoliday = 0

Do While Not rsHoliday.EOF
iHoliday = iHoliday + 1
dHoliday(iHoliday) = rsHoliday!Holiday
rsHoliday.MoveNext
Loop

' Then the statement in the loop becomes:

If iHoliday > 0 Then
For nCounter = 1 To iHoliday
If DateSerial(intYear, intMonth, intJ) =
dHoliday(nCounter) Then
Me("txt" & strNum).BackColor = 4227327
End If
Next nCounter
End If

' end of holiday code

' and Enable it
Me("txt" & strNum).Enabled = True
intJ = intJ + 1
Next intI

' Hide the last row if we didn't use it
If intLast < 36 Then
intJ = False
Else
intJ = True
End If
For intI = 36 To 42
Me("txt" & intI).Visible = intJ
Next intI

' Now call the procedure to insert the data for the current
group
cmbRoom_AfterUpdate

End Sub

Private Sub cmbRoom_AfterUpdate()

Dim db As DAO.Database, rst As DAO.Recordset
Dim datFirstDate As Date, datLastDate As Date, strSQL As String
Dim intStart As Integer, intEnd As Integer, intI As Integer, strNum As
String

' Clear all the text boxes
For intI = 1 To 42
strNum = Format(intI, "00")
Me("txt" & strNum).Value = ""
Next intI

' Get a pointer to the current database
Set db = CurrentDb

' Calculate the start and end date for the current month
datFirstDate = DateSerial(intYear, intMonth, 1)
datLastDate = DateSerial(intYear, intMonth, intLastDay)

' Set up to select the guest bookings that are in the current
month
strSQL = "SELECT * FROM qryGuestCalendar " & _
"WHERE RoomNo = 1 AND BeginDate <= #" & datLastDate & _
"# And EndingDate >= #" & datFirstDate & "#"

Set rst = db.OpenRecordset(strSQL)

'Loop through all the rows and set the Guest's names in the
calendar
Do Until rst.EOF
' Calculate the start point
If rst!Begindate < datFirstDate Then
intStart = intFirst
Else
intStart = Day(rst!Begindate) + intFirst - 1
End If
' Calculate the end point
If rst!Endingdate > datLastDate Then
intEnd = intLast
Else
intEnd = Day(rst!Endingdate) + intFirst - 1
End If

' Set in the Reservation Number & Guest's name in the
appropriate days
For intI = intStart To intEnd
strNum = Format(intI, "00")

Me("txt" & strNum).Value = Me("txt" & strNum).Value & _
rst!GuestID & " / " & rst!ReservationNo & vbCrLf

Next intI

' Get the next row
rst.MoveNext
Loop

rst.Close

End Sub
Private Sub GetContract(varValue As Variant)
Dim intGuest As Integer, intI As Integer

' Do nothing if the control contained no value
If IsNothing(varValue) Then
DoCmd.OpenForm "frmGuests", acNormal, , , acFormAdd,
acWindowNormal

Else

' Extract the Reservation number
intI = InStr(varValue, " ") ' Find the ending blank
intGuest = Left(varValue, intI - 1)

' Open the Reservation form
DoCmd.OpenForm "frmGuests", WhereCondition:="GuestID = " &
intGuest

End If

End Sub

[Ray]

Thanks for your reply. It have to state that there is only one table,
namely "tblReservations" which contains all the reservations for all
the rooms, not five as you suggested. The frmTemp is used to enter the
reservation data, once it has been checked for double bookings it is
then appended to the tblreservations. Does that make it a little
clearer? TIA - Ray

"PC Datasheet" <sp**@nospam.spam> wrote in message news:<Kj***************@newsread3.news.atl.earthli nk.net>...

Ray,

You need to relook at the design of your tables - they don't appear to be
correct. The fact that you are using five subforms, one for each room, indicates
there's a problem there. One of your tables has a field for each room which is
incorrect. You should have a table like:
TblRoom
RoomID
RoomNum
<<Other Fields That Describe The Room>>

and you should have a record in this table for each room.

Then you should have a table like:
TblReservation
ReservationID
RoomID
StartDate
EndDate
<<Other Fields that describe the reservation for a specific room>>

and you should have a record in this table for each time a reservation is made
for a specific room.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"Ray" <wa*****@xtra.co.nz> wrote in message
news:42**************************@posting.google.c om...

I'm sorry this is a long post but I though I had better include
everything as I'm new to VB.

I have a a form frmCalendarMain which has five subforms (unbound).
Each form is a reservation calendar frmCalendarRm1,
frmCalendarRm2....Rm5 etc. At the moment if I click on a field which
is already booked then I can open the reservation form (frmGuests) by
calling (GetContract). This workes fine, however I would like the user
to be able to click on a blank date in one of the subforms and when
the frmGuests form opens the subform (frmTemp) will automatically
enter in the [RoomNo] the appropriate room no from the frmCalendarRm1
= 1 and will also enter in to the [BeginDate] the date from the label
of frmCalendarRm1. Is this possible. It maybe a big ask.... but if you
don't ask you'll never learn.

Thanks guys for all your help. I just hope the above makes sense in
conjunction with the code below. TIA - Ray

Dim intFirst As Integer, intLast As Integer, intLastDay As Integer
Dim intMonth As Integer, intYear As Integer

Public Sub SetDays1()
Dim intI As Integer, intJ As Integer, strNum As String

' First, clear all the boxes
For intI = 1 To 42
' Controls are named "lblnn" and "txtnn"
' Where nn = 01 to 42
' Using Format to get 2 digits
strNum = Format(intI, "00")
' Clear the day number
Me("lbl" & strNum).Caption = ""
' Clear the contents of the text box
Me("txt" & strNum).Value = ""
' Grey out the background
Me("txt" & strNum).BackColor = 255

' And disable it
Me.Text111.SetFocus
Me("txt" & strNum).Enabled = False
Next intI

intMonth = Forms!frmCalendarMain!CboMonth
intYear = Forms!frmCalendarMain!CboYear
' The first box to set is the weekday of the first day of the
month
intFirst = Weekday(DateSerial(intYear, intMonth, 1), vbSunday)
' Calculate the last day number
intLastDay = Day(DateAdd("m", 1, DateSerial(intYear, intMonth, 1))
- 1)
' .. and the last box to set
intLast = intFirst + intLastDay - 1

' Now set up all the boxes for the current month
intJ = 1
For intI = intFirst To intLast
strNum = Format(intI, "00")
' Put the day number in the associated label caption
Me("lbl" & strNum).Caption = intJ
' If the day is a weekend then make the background of the box
blue
Me("txt" & strNum).BackColor = IIf(Weekday(DateSerial(intYear,
intMonth, intJ), vbMonday) > 5, 16763904, 16777215)

Dim rsHoliday As Recordset, sqlHoliday As String
Dim iHoliday As Integer
Dim dHoliday(1 To 10) As Date

sqlHoliday = "SELECT Holiday FROM [tblholiday] " & _
"WHERE (DatePart('m', Holiday) = " & intMonth & _
" AND DatePart('yyyy', Holiday) = " & intYear & ");"

Set rsHoliday = CurrentDb().OpenRecordset(sqlHoliday)

iHoliday = 0

Do While Not rsHoliday.EOF
iHoliday = iHoliday + 1
dHoliday(iHoliday) = rsHoliday!Holiday
rsHoliday.MoveNext
Loop

' Then the statement in the loop becomes:

If iHoliday > 0 Then
For nCounter = 1 To iHoliday
If DateSerial(intYear, intMonth, intJ) =
dHoliday(nCounter) Then
Me("txt" & strNum).BackColor = 4227327
End If
Next nCounter
End If

' end of holiday code

' and Enable it
Me("txt" & strNum).Enabled = True
intJ = intJ + 1
Next intI

' Hide the last row if we didn't use it
If intLast < 36 Then
intJ = False
Else
intJ = True
End If
For intI = 36 To 42
Me("txt" & intI).Visible = intJ
Next intI

' Now call the procedure to insert the data for the current
group
cmbRoom_AfterUpdate

End Sub

Private Sub cmbRoom_AfterUpdate()

Dim db As DAO.Database, rst As DAO.Recordset
Dim datFirstDate As Date, datLastDate As Date, strSQL As String
Dim intStart As Integer, intEnd As Integer, intI As Integer, strNum As
String

' Clear all the text boxes
For intI = 1 To 42
strNum = Format(intI, "00")
Me("txt" & strNum).Value = ""
Next intI

' Get a pointer to the current database
Set db = CurrentDb

' Calculate the start and end date for the current month
datFirstDate = DateSerial(intYear, intMonth, 1)
datLastDate = DateSerial(intYear, intMonth, intLastDay)

' Set up to select the guest bookings that are in the current
month
strSQL = "SELECT * FROM qryGuestCalendar " & _
"WHERE RoomNo = 1 AND BeginDate <= #" & datLastDate & _
"# And EndingDate >= #" & datFirstDate & "#"

Set rst = db.OpenRecordset(strSQL)

'Loop through all the rows and set the Guest's names in the
calendar
Do Until rst.EOF
' Calculate the start point
If rst!Begindate < datFirstDate Then
intStart = intFirst
Else
intStart = Day(rst!Begindate) + intFirst - 1
End If
' Calculate the end point
If rst!Endingdate > datLastDate Then
intEnd = intLast
Else
intEnd = Day(rst!Endingdate) + intFirst - 1
End If

' Set in the Reservation Number & Guest's name in the
appropriate days
For intI = intStart To intEnd
strNum = Format(intI, "00")

Me("txt" & strNum).Value = Me("txt" & strNum).Value & _
rst!GuestID & " / " & rst!ReservationNo & vbCrLf

Next intI

' Get the next row
rst.MoveNext
Loop

rst.Close

End Sub
Private Sub GetContract(varValue As Variant)
Dim intGuest As Integer, intI As Integer

' Do nothing if the control contained no value
If IsNothing(varValue) Then
DoCmd.OpenForm "frmGuests", acNormal, , , acFormAdd,
acWindowNormal

Else

' Extract the Reservation number
intI = InStr(varValue, " ") ' Find the ending blank
intGuest = Left(varValue, intI - 1)

' Open the Reservation form
DoCmd.OpenForm "frmGuests", WhereCondition:="GuestID = " &
intGuest

End If

End Sub

[PC Datasheet]

Ray,

What are the fields in your TblReservations?

Steve
PC Datasheet
"Ray" <wa*****@xtra.co.nz> wrote in message
news:42*************************@posting.google.co m...

Thanks for your reply. It have to state that there is only one table,
namely "tblReservations" which contains all the reservations for all
the rooms, not five as you suggested. The frmTemp is used to enter the
reservation data, once it has been checked for double bookings it is
then appended to the tblreservations. Does that make it a little
clearer? TIA - Ray

"PC Datasheet" <sp**@nospam.spam> wrote in message

news:<Kj***************@newsread3.news.atl.earthli nk.net>...

Ray,

You need to relook at the design of your tables - they don't appear to be
correct. The fact that you are using five subforms, one for each room, indicates there's a problem there. One of your tables has a field for each room which is incorrect. You should have a table like:
TblRoom
RoomID
RoomNum
<<Other Fields That Describe The Room>>

and you should have a record in this table for each room.

Then you should have a table like:
TblReservation
ReservationID
RoomID
StartDate
EndDate
<<Other Fields that describe the reservation for a specific room>>

and you should have a record in this table for each time a reservation is made for a specific room.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"Ray" <wa*****@xtra.co.nz> wrote in message
news:42**************************@posting.google.c om...

I'm sorry this is a long post but I though I had better include
everything as I'm new to VB.

I have a a form frmCalendarMain which has five subforms (unbound).
Each form is a reservation calendar frmCalendarRm1,
frmCalendarRm2....Rm5 etc. At the moment if I click on a field which
is already booked then I can open the reservation form (frmGuests) by
calling (GetContract). This workes fine, however I would like the user
to be able to click on a blank date in one of the subforms and when
the frmGuests form opens the subform (frmTemp) will automatically
enter in the [RoomNo] the appropriate room no from the frmCalendarRm1
= 1 and will also enter in to the [BeginDate] the date from the label
of frmCalendarRm1. Is this possible. It maybe a big ask.... but if you
don't ask you'll never learn.

Thanks guys for all your help. I just hope the above makes sense in
conjunction with the code below. TIA - Ray

Dim intFirst As Integer, intLast As Integer, intLastDay As Integer
Dim intMonth As Integer, intYear As Integer

Public Sub SetDays1()
Dim intI As Integer, intJ As Integer, strNum As String

' First, clear all the boxes
For intI = 1 To 42
' Controls are named "lblnn" and "txtnn"
' Where nn = 01 to 42
' Using Format to get 2 digits
strNum = Format(intI, "00")
' Clear the day number
Me("lbl" & strNum).Caption = ""
' Clear the contents of the text box
Me("txt" & strNum).Value = ""
' Grey out the background
Me("txt" & strNum).BackColor = 255

' And disable it
Me.Text111.SetFocus
Me("txt" & strNum).Enabled = False
Next intI

intMonth = Forms!frmCalendarMain!CboMonth
intYear = Forms!frmCalendarMain!CboYear
' The first box to set is the weekday of the first day of the
month
intFirst = Weekday(DateSerial(intYear, intMonth, 1), vbSunday)
' Calculate the last day number
intLastDay = Day(DateAdd("m", 1, DateSerial(intYear, intMonth, 1))
- 1)
' .. and the last box to set
intLast = intFirst + intLastDay - 1

' Now set up all the boxes for the current month
intJ = 1
For intI = intFirst To intLast
strNum = Format(intI, "00")
' Put the day number in the associated label caption
Me("lbl" & strNum).Caption = intJ
' If the day is a weekend then make the background of the box
blue
Me("txt" & strNum).BackColor = IIf(Weekday(DateSerial(intYear,
intMonth, intJ), vbMonday) > 5, 16763904, 16777215)

Dim rsHoliday As Recordset, sqlHoliday As String
Dim iHoliday As Integer
Dim dHoliday(1 To 10) As Date

sqlHoliday = "SELECT Holiday FROM [tblholiday] " & _
"WHERE (DatePart('m', Holiday) = " & intMonth & _
" AND DatePart('yyyy', Holiday) = " & intYear & ");"

Set rsHoliday = CurrentDb().OpenRecordset(sqlHoliday)

iHoliday = 0

Do While Not rsHoliday.EOF
iHoliday = iHoliday + 1
dHoliday(iHoliday) = rsHoliday!Holiday
rsHoliday.MoveNext
Loop

' Then the statement in the loop becomes:

If iHoliday > 0 Then
For nCounter = 1 To iHoliday
If DateSerial(intYear, intMonth, intJ) =
dHoliday(nCounter) Then
Me("txt" & strNum).BackColor = 4227327
End If
Next nCounter
End If

' end of holiday code

' and Enable it
Me("txt" & strNum).Enabled = True
intJ = intJ + 1
Next intI

' Hide the last row if we didn't use it
If intLast < 36 Then
intJ = False
Else
intJ = True
End If
For intI = 36 To 42
Me("txt" & intI).Visible = intJ
Next intI

' Now call the procedure to insert the data for the current
group
cmbRoom_AfterUpdate

End Sub

Private Sub cmbRoom_AfterUpdate()

Dim db As DAO.Database, rst As DAO.Recordset
Dim datFirstDate As Date, datLastDate As Date, strSQL As String
Dim intStart As Integer, intEnd As Integer, intI As Integer, strNum As
String

' Clear all the text boxes
For intI = 1 To 42
strNum = Format(intI, "00")
Me("txt" & strNum).Value = ""
Next intI

' Get a pointer to the current database
Set db = CurrentDb

' Calculate the start and end date for the current month
datFirstDate = DateSerial(intYear, intMonth, 1)
datLastDate = DateSerial(intYear, intMonth, intLastDay)

' Set up to select the guest bookings that are in the current
month
strSQL = "SELECT * FROM qryGuestCalendar " & _
"WHERE RoomNo = 1 AND BeginDate <= #" & datLastDate & _
"# And EndingDate >= #" & datFirstDate & "#"

Set rst = db.OpenRecordset(strSQL)

'Loop through all the rows and set the Guest's names in the
calendar
Do Until rst.EOF
' Calculate the start point
If rst!Begindate < datFirstDate Then
intStart = intFirst
Else
intStart = Day(rst!Begindate) + intFirst - 1
End If
' Calculate the end point
If rst!Endingdate > datLastDate Then
intEnd = intLast
Else
intEnd = Day(rst!Endingdate) + intFirst - 1
End If

' Set in the Reservation Number & Guest's name in the
appropriate days
For intI = intStart To intEnd
strNum = Format(intI, "00")

Me("txt" & strNum).Value = Me("txt" & strNum).Value & _
rst!GuestID & " / " & rst!ReservationNo & vbCrLf

Next intI

' Get the next row
rst.MoveNext
Loop

rst.Close

End Sub
Private Sub GetContract(varValue As Variant)
Dim intGuest As Integer, intI As Integer

' Do nothing if the control contained no value
If IsNothing(varValue) Then
DoCmd.OpenForm "frmGuests", acNormal, , , acFormAdd,
acWindowNormal

Else

' Extract the Reservation number
intI = InStr(varValue, " ") ' Find the ending blank
intGuest = Left(varValue, intI - 1)

' Open the Reservation form
DoCmd.OpenForm "frmGuests", WhereCondition:="GuestID = " &
intGuest

End If

End Sub