473,880 Members | 1,958 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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).BackCol or = 255

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

intMonth = Forms!frmCalend arMain!CboMonth
intYear = Forms!frmCalend arMain!CboYear
' The first box to set is the weekday of the first day of the
month
intFirst = Weekday(DateSer ial(intYear, intMonth, 1), vbSunday)
' Calculate the last day number
intLastDay = Day(DateAdd("m" , 1, DateSerial(intY ear, 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).BackCol or = IIf(Weekday(Dat eSerial(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().Ope nRecordset(sqlH oliday)

iHoliday = 0

Do While Not rsHoliday.EOF
iHoliday = iHoliday + 1
dHoliday(iHolid ay) = rsHoliday!Holid ay
rsHoliday.MoveN ext
Loop

' Then the statement in the loop becomes:

If iHoliday > 0 Then
For nCounter = 1 To iHoliday
If DateSerial(intY ear, intMonth, intJ) =
dHoliday(nCount er) Then
Me("txt" & strNum).BackCol or = 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_AfterUp date

End Sub

Private Sub cmbRoom_AfterUp date()

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(intY ear, intMonth, 1)
datLastDate = DateSerial(intY ear, intMonth, intLastDay)

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

Set rst = db.OpenRecordse t(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!Beginda te) + intFirst - 1
End If
' Calculate the end point
If rst!Endingdate > datLastDate Then
intEnd = intLast
Else
intEnd = Day(rst!Endingd ate) + 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!Reservation No & vbCrLf

Next intI

' Get the next row
rst.MoveNext
Loop

rst.Close

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

' Do nothing if the control contained no value
If IsNothing(varVa lue) 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
Nov 12 '05 #1
3 1490
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******@pcdata sheet.com
www.pcdatasheet.com

"Ray" <wa*****@xtra.c o.nz> wrote in message
news:42******** *************** ***@posting.goo gle.com...
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).BackCol or = 255

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

intMonth = Forms!frmCalend arMain!CboMonth
intYear = Forms!frmCalend arMain!CboYear
' The first box to set is the weekday of the first day of the
month
intFirst = Weekday(DateSer ial(intYear, intMonth, 1), vbSunday)
' Calculate the last day number
intLastDay = Day(DateAdd("m" , 1, DateSerial(intY ear, 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).BackCol or = IIf(Weekday(Dat eSerial(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().Ope nRecordset(sqlH oliday)

iHoliday = 0

Do While Not rsHoliday.EOF
iHoliday = iHoliday + 1
dHoliday(iHolid ay) = rsHoliday!Holid ay
rsHoliday.MoveN ext
Loop

' Then the statement in the loop becomes:

If iHoliday > 0 Then
For nCounter = 1 To iHoliday
If DateSerial(intY ear, intMonth, intJ) =
dHoliday(nCount er) Then
Me("txt" & strNum).BackCol or = 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_AfterUp date

End Sub

Private Sub cmbRoom_AfterUp date()

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(intY ear, intMonth, 1)
datLastDate = DateSerial(intY ear, intMonth, intLastDay)

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

Set rst = db.OpenRecordse t(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!Beginda te) + intFirst - 1
End If
' Calculate the end point
If rst!Endingdate > datLastDate Then
intEnd = intLast
Else
intEnd = Day(rst!Endingd ate) + 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!Reservation No & vbCrLf

Next intI

' Get the next row
rst.MoveNext
Loop

rst.Close

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

' Do nothing if the control contained no value
If IsNothing(varVa lue) 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

Nov 12 '05 #2
Ray
Thanks for your reply. It have to state that there is only one table,
namely "tblReservation s" 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.sp am> wrote in message news:<Kj******* ********@newsre ad3.news.atl.ea rthlink.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******@pcdata sheet.com
www.pcdatasheet.com

"Ray" <wa*****@xtra.c o.nz> wrote in message
news:42******** *************** ***@posting.goo gle.com...
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).BackCol or = 255

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

intMonth = Forms!frmCalend arMain!CboMonth
intYear = Forms!frmCalend arMain!CboYear
' The first box to set is the weekday of the first day of the
month
intFirst = Weekday(DateSer ial(intYear, intMonth, 1), vbSunday)
' Calculate the last day number
intLastDay = Day(DateAdd("m" , 1, DateSerial(intY ear, 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).BackCol or = IIf(Weekday(Dat eSerial(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().Ope nRecordset(sqlH oliday)

iHoliday = 0

Do While Not rsHoliday.EOF
iHoliday = iHoliday + 1
dHoliday(iHolid ay) = rsHoliday!Holid ay
rsHoliday.MoveN ext
Loop

' Then the statement in the loop becomes:

If iHoliday > 0 Then
For nCounter = 1 To iHoliday
If DateSerial(intY ear, intMonth, intJ) =
dHoliday(nCount er) Then
Me("txt" & strNum).BackCol or = 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_AfterUp date

End Sub

Private Sub cmbRoom_AfterUp date()

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(intY ear, intMonth, 1)
datLastDate = DateSerial(intY ear, intMonth, intLastDay)

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

Set rst = db.OpenRecordse t(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!Beginda te) + intFirst - 1
End If
' Calculate the end point
If rst!Endingdate > datLastDate Then
intEnd = intLast
Else
intEnd = Day(rst!Endingd ate) + 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!Reservation No & vbCrLf

Next intI

' Get the next row
rst.MoveNext
Loop

rst.Close

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

' Do nothing if the control contained no value
If IsNothing(varVa lue) 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

Nov 12 '05 #3
Ray,

What are the fields in your TblReservations ?

Steve
PC Datasheet
"Ray" <wa*****@xtra.c o.nz> wrote in message
news:42******** *************** **@posting.goog le.com...
Thanks for your reply. It have to state that there is only one table,
namely "tblReservation s" 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.sp am> wrote in message

news:<Kj******* ********@newsre ad3.news.atl.ea rthlink.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******@pcdata sheet.com
www.pcdatasheet.com

"Ray" <wa*****@xtra.c o.nz> wrote in message
news:42******** *************** ***@posting.goo gle.com...
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).BackCol or = 255

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

intMonth = Forms!frmCalend arMain!CboMonth
intYear = Forms!frmCalend arMain!CboYear
' The first box to set is the weekday of the first day of the
month
intFirst = Weekday(DateSer ial(intYear, intMonth, 1), vbSunday)
' Calculate the last day number
intLastDay = Day(DateAdd("m" , 1, DateSerial(intY ear, 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).BackCol or = IIf(Weekday(Dat eSerial(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().Ope nRecordset(sqlH oliday)

iHoliday = 0

Do While Not rsHoliday.EOF
iHoliday = iHoliday + 1
dHoliday(iHolid ay) = rsHoliday!Holid ay
rsHoliday.MoveN ext
Loop

' Then the statement in the loop becomes:

If iHoliday > 0 Then
For nCounter = 1 To iHoliday
If DateSerial(intY ear, intMonth, intJ) =
dHoliday(nCount er) Then
Me("txt" & strNum).BackCol or = 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_AfterUp date

End Sub

Private Sub cmbRoom_AfterUp date()

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(intY ear, intMonth, 1)
datLastDate = DateSerial(intY ear, intMonth, intLastDay)

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

Set rst = db.OpenRecordse t(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!Beginda te) + intFirst - 1
End If
' Calculate the end point
If rst!Endingdate > datLastDate Then
intEnd = intLast
Else
intEnd = Day(rst!Endingd ate) + 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!Reservation No & vbCrLf

Next intI

' Get the next row
rst.MoveNext
Loop

rst.Close

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

' Do nothing if the control contained no value
If IsNothing(varVa lue) 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

Nov 12 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

44
2415
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. So, for $1/hr they expect me to remain within pager range, remain sober, and be available to come to work. That really sucks on weekends. No going to the beach (out of pager range) or out to a friend's cottage (out of pager range), etc. ...
3
1197
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 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...
10
19377
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, Sally
1
2334
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, however there is a subform too and when I am in the subform this procedure doesnt work. How can I have modify Me.AllowEdits=false to point to both the parent and the subform by name? the parents form name is frm_pe and the subforms name is...
4
2149
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 action=supers_NxtlUpdate.asp> <% Do While Not objRecordset.EOF
6
5997
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 forms in the database that will use this function, so I need to be able to tell the code which form to unlock. What I have is as follows: Public Function akeyEdit()
11
8140
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 correctly, but the moment that you change the record in the parent/master form, the forms "lose" all of the data we enter from the subforms. Upon examination of the tables, I found that the master key is not being populated into the top level subform,...
2
1271
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 the database and returns the matches, simple enough. Now, what I would like to do is take the ID of the entry, let's say the restaurant_id and make the display name (rest_name) as a link with that id number and be able to populate a dynamic page...
4
2940
cassbiz
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 text and I can't find the error on why it isn't working. Any help is greatly appreciated. Here is the form
0
9925
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
11089
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10713
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10809
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10395
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5777
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5972
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4595
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3219
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.