Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Sep 2004
    Wolverhampton, Staffordshire, England
    Thanked 0 Times in 0 Posts

    How to prevent Combined Duplicates? (Access 2000)

    I have a specific sort of problema nd after a brief browsing couldn't find a solution, my database is absed around a booking system in a small hotel, with tables for rooms, customers and bookings, the problem i have is that i want to prevent a double booking of the same date and same room number, but am unaware of how to group this data so a group duplicate cannot be formed, e.g Room 1 Start 11/07/05 End 18/07/05, I can prevent each individually but obviously this is useless as the the same room can appear but at a different date... Can anyone provide me with a solution - thank you for reading guys,

    (Numbers of Ixinia's Ream Eternal Nexus, Eternal Xenerion)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: How to prevent Combined Duplicates? (Access 2000)

    Say you have a table tblBookings with the following fields:

    ID: number, unique key (for example an AutoNumber field)
    RoomID: number, identifies the room
    StartDate: date/time
    EndDate: date/time

    In a form based on this table, you can check for duplicates in the Before Update event of the form:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim strSQL As String
    Dim strMsg As String

    strSQL = "SELECT StartDate, EndDate FROM tblBookings " & _
    "WHERE EndDate > #" & Format(Me.StartDate, "mm/dd/yyyy") & _
    "# AND StartDate < #" & Format(Me.EndDate, "mm/dd/yyyy") & _
    "# AND RoomID = " & Me.RoomID & " AND ID <> " & Me.ID
    Set cnn = CurrentProject.Connection
    rst.Open strSQL, cnn, adOpenStatic

    If rst.RecordCount > 0 Then
    strMsg = strMsg & vbCrLf & "From " & rst!StartDate & " to " & rst!EndDate
    Loop Until rst.EOF
    strMsg = "This booking would overlap with " & rst.RecordCount & _
    " others." & strMsg
    MsgBox strMsg, vbExclamation
    Cancel = True
    End If

    Set rst = Nothing
    Set cnn = Nothing
    End Sub

    You probably want to add code to prevent the start date and end date from being left blank, and to prevent the end date from being earlier than the start date.

    See attached demo.

Posting Permissions

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