Ray,
What are the fields in your TblReservations?
Steve
PC Datasheet
"Ray" <wattles@xtra.co.nz> wrote in message
news:4205783b.0404141401.3d9bc2b@posting.google.co m...[color=blue]
> 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" <spam@nospam.spam> wrote in message[/color]
news:<Kj3fc.6763$zj3.35@newsread3.news.atl.earthli nk.net>...[color=blue][color=green]
> > 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,[/color][/color]
indicates[color=blue][color=green]
> > there's a problem there. One of your tables has a field for each room which[/color][/color]
is[color=blue][color=green]
> > 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[/color][/color]
made[color=blue][color=green]
> > for a specific room.
> >
> > --
> > PC Datasheet
> > Your Resource For Help With Access, Excel And Word Applications
> >
resource@pcdatasheet.com
> >
www.pcdatasheet.com
> >
> >
> >
> > "Ray" <wattles@xtra.co.nz> wrote in message
> > news:4205783b.0404131930.5ea4a3b5@posting.google.c om...[color=darkred]
> > > 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[/color][/color][/color]
