Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bookings that don't clash (97/2K)

    Hi Rob,
    I use the following as part of the room scheduling for our ITS classes.
    <pre>strMessage = "There is already a class scheduled for " & vbCrLf & _
    "this room, day and time period." & vbCrLf & vbCrLf & _
    "Click OK to double schedule this room, " & vbCrLf & _
    "or " & vbCrLf & _
    "Click Cancel to try and schedule again."
    strTitle = "Date/Time Conflict"

    strBegin = Me![txtDateClass].Value & " " & Me![txtTimeStart].Value
    strEnd = Me![txtDateClass].Value & " " & Me![txtTimeEnd].Value

    strSched = "SELECT qryClassInformation.ClassInformationPKID "
    strSched = strSched & "FROM qryClassInformation "
    strSched = strSched & "WHERE qryClassInformation.LocationPKID = " & Me!cboLocation
    strSched = strSched & " And qryClassInformation.FullTimeStart < #" & strEnd & "#"
    strSched = strSched & " And qryClassInformation.FullTimeEnd > #" & strBegin & "#"

    Set db = CurrentDb()
    Set rst = db.OpenRecordset(strSchedule)

    With rst
    If .RecordCount <> 0 Then
    intAnswer = MsgBox(strMessage, vbOKCancel + vbExclamation, strTitle)
    If intAnswer = vbOK Then
    'do nothing
    ElseIf intAnswer = vbCancel Then
    Cancel = True
    Me.Undo
    Exit Sub
    End If
    Else
    'do nothing
    End If
    End With

    'Where qryClassInformation.FullTimeStart =
    FullTimeStart: CDate([DateClass] & " " & [TimeStart]) and
    qryClassInformation.FullTimeEnd =
    FullTimeEnd: CDate([DateClass] & " " & [TimeEnd])
    They are concatenated fields in the source query
    And strBegin and strEnd are the proposed new times for the new booking.
    </pre>



    You've only have to be concerned that a new booking starts before a previous booking ends and
    when the previous bookings ends after the new booking starts.

    I got this from Allen Browne's web site at
    http://users.bigpond.net.au/abrowne1/appevent.html

    Hope this gets you started.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  2. #2
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bookings that don't clash (97/2K)

    Gary

    Thanks for the input. I'll look at your code and the website with a clearer head, ie, manana.

    Cheers

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  3. #3
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Bookings that don't clash (97/2K)

    This will have been the subject of many posts but searching has proved fruitless.
    The db I am trying to develop models a hotel that books out rooms for functions on a given date. One datene room.
    I have a Bookings form with controls bound to a Bookings table. This form has a combo box that displays Room Names and a combo box that displays Date. I also have a table that stores Room Names and all possible dates that each room could be booked. I would like that after selecting a room, only the available Dates are displayed in the Date combo box. I know it can be achieved with two queries and I have made many attempts but I cannot quite get to a solution.

    Any help would be greatfully accepted.

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bookings that don't clash (97/2K)

    This might help you along also.

    Hans wrote this for me recently.

    Dim CourtesyCheck As Integer
    CourtesyCheck = DCount("*", "tblPeriod", "UnitID=" & Me!txtUnitID & " And " & _
    BuildCriteria("FromDate", dbDate, "<=" & Me!txtDateThru) & " And " & _
    BuildCriteria("ThruDate", dbDate, ">=" & Me!txtDateFrom))
    If CourtesyCheck > 0 Then
    ' Warn the user
    MsgBox "This Vehicle Is Already Booked"
    End If

  5. #5
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bookings that don't clash (97/2K)

    Thanks for the code, Dave!
    By the way. What's it like up in Stokeish parts these days?

    Cheers

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  6. #6
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bookings that don't clash (97/2K)

    You're welcome.

    Just as cold as where you are !!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •