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
3  1452 
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
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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Richard |
last post by:
My employer currently pays me $1/hour when I carry a pager. I have to
carry the pager every fourth week. The problem is that my employer
insists that I be available when I'm carrying the pager. ...
|
by: Ray |
last post by:
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...
|
by: Sally |
last post by:
When I click on a control in a subform, what is the syntax to return the
name of the subform control on the main form that contains the subform?
MsgBox "MySubform is in " & < ???? >
Thanks,
...
|
by: tdmailbox |
last post by:
I have the statment below that turns off allow edits so thatwhen I run
the commend acCmdFind I only see the find menu rather then find and
replace.
The search button is on the parent form,...
|
by: Matt |
last post by:
I have the following in a page and I am trying to update a record on
the next page but for some reason the form data is not carrying over.
Any ideas why?
<form name=nxtlupdate method=post...
|
by: DMUM via AccessMonster.com |
last post by:
Hello
I am trying to pass the name of my subform to a function/sub but I can't
seem to get it to work. I am using an autokey function (ctrl E) to unlock
text boxes on a subform. I have a few...
|
by: elaina |
last post by:
Hello again.
I have setup a master form linked to a subform (actually nested subforms, but the top level subform is the one giving me trouble.) When entering data, the forms all appear to work...
|
by: xcoaster1 |
last post by:
Ok, kinda of stumped on this and i've been away from PHP development for a while some i'm a tad lost.
I have a database search for where if you enter the name of a location and submit it queries...
|
by: cassbiz |
last post by:
Could use some help here.
This script is carrying over an image just fine but the text isn't coming over. can you see why it is not working????
from the form I want to carry over two lines of...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |
