How to prevent double reservation? 2

[khch]

Hi,
I'm making a database for reservation of items with these fields: Item name (combo box), date, from time, to time.
I want to prevent the users to reserve an item that is already reserved at the same date,from time and to time as the user is going to choose. How to do that?. Thanks
I'm using microsoft access 2000.

 

[TheAceMan1]

How are ya khch . . . . .

In the [blue]Before Update[/blue] event of the form, something like following code ([blue]you[/blue] have to substitute names in [purple]purple[/purple]):

[blue]   Dim Msg As String, Style As Integer, Title As String
   Dim DL As String, Criteria As String
   
   Me![[purple][b]Item Name[/b][/purple]].SetFocus
   DL = vbNewLine & vbNewLine
   
   Criteria = "(([[purple][b]Item Name[/b][/purple]] =" & Me![[purple][b]Item Name[/b][/purple]].Text & ") And " & _
              "([[purple][b]Date[/b][/purple]] = #" & Me![purple][b]Date[/b][/purple] & "#) And " & _
              "([[purple][b]From Time[/b][/purple]] = #" & Me![[purple][b]From Time[/b][/purple]] & "#) And " & _
              "([[purple][b]To Time[/b][/purple]] = #" & Me![[purple][b]To Time[/b][/purple]] & "#))"
   
   If Not IsNull(DLookup("[[purple][b]UniqueKey[/b][/purple]]", "[purple][b]TableName[/b][/purple]", Criteria)) Then
      Msg = "Item Already Reserved!" & DL & _
            "Can't reserve same item!" & DL & _
            "Try Again . . . . . ."
      Style = vbInformation + vbOKOnly
      Title = "Duplicate Resevation Error!"
      MsgBox Msg, Style, Title
      
      Cancel = True
      Me.Undo
   End If[/blue]

See Ya! . . . . . .

 

[willir]

khch

Because you need to include start / end time, the problem is pretty tough, BUT there is one thing you should do regardless of code. You can use the "primary key" or unique index to minimize double bookings. But this is still not enough.

A reservation is actually a many-to-many relationship between the reservered item and the person making the reservation.
- A person can reserve many items
- An item can be reserved many times.

Your reservation table is actually the joiner or intermediary table.

Consequently, the start of the design of the reservation table is to include Person + Item.

But since a person can reserve an item multiple times, you need to include the date and time.

tblRservation
ItemCode - foreign key to item table
ContactID - foreign key to contact or customer table
DateOut
TimeOut

Your primary key for this table should be

ItemCode + ContactID + DateOut
if the item can only be reservered once during a day

ItemCode + ContactID + DateOut + TimeOut
or
ItemCode + ContactID + (Date+Time)
if the item can only be reservered several times during a day

The first scenario is the easiest. But the adding time component really adds complexity.

What makes it tough is that although you can block an item on this date at this time, you can not block an item at this date at this time - 1 min, or + 1 min.

Example:

The DVD Lord of the Rings, FOTR is
- signed out July 2, 2PM
- signed in July 3, 3 PM

But the same DVD can also be
- signed out Jul 2 6PM

[COLOR=blue yellow]You need to also keep a flag on the Item table indicating that it is signed out.[/color]

...Moving on
Assuming that
- ItemCode is a string variable used as the primary key to reserve your item, and is referenced on the form as Me.ItemCode
- you have added a field to the Item table CheckedOut (Yes/No boolean), using AceMan's excellent post...

Criteria = "[ItemCode] = " & "'" & Me.ItemCode & "'"
   
If (DLookup("[CheckedOut]", "YourItemTable", Criteria)) Then...

Therefore, when you check out an item, not only do you have to make the reservation, you also have to flag the item as reserved. And then complete the reservation with the CheckIn Date and Time, and uncheck the CheckOut flag.

As a user friendly gesture, if an Item is checked out, your message could indicate who has the item reserved.

This is a real interesting issue since normalization of the reservation table does not present the best solution.

SideBar: AceMan -- I really liked your snippet of code

Richard

 

[TheAceMan1]

How are ya willir . . . . .

You've made some good points there. Appears to be a good path for [blue]khch[/blue] to follow considering the snippet of information given. Why do I get the feeling there's more under the hood that could sway the design? I believe more info would enable a sharper, more discrete focus.

If there's any ambiguities, I'm sure [blue]khch[/blue] will let us know.

As for detecting duplicates, over the years I've learned to do the detection myself, particularly where [blue]Compound Primary Keys[/blue] are concerned. Although there very useful (and I have used them in the past), they add an unneeded complexity to the design. For the past two years, custom detection of duplicates has kept all my tables since, down to one PK! As we say here in New York . . . [purple]Bada Bing! . . . Bada Boom![/purple] Besides, the prompts Microsoft comes up with are so alarming & un-tasteful.

You take care . . . . . . Ya Hear!

See Ya! . . . . . .

 

[willir]

AceMan
Yes, compound primary keys are a pain. I usually stop at two, and only when dealing with M:M or static variables.

...But I feel you can also define a unique index which will not be as painful.