Re: Booking duplicates

Two events overlap if:
A starts before B ends, AND
B starts before A ends, AND
A and B are not the same event.

To check for this when a record is being entered, use the BeforeUpdate event
of the Form, and use DLookup to get the EventID (or whatever your primary
key is called) for the first event that clashes.

This aircode example assumes a table named tblEvent, with a primary key
autonumber named EventID, and date/time fields named Start and End which are
both required:

Private Sub Form_BeforeUdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Const strcJetDate = "\#mm\/dd\/yyyy hh:nn:ss\#"

If Me.Start = Me.Start.OldValue OR Me.End = Me.End.OldValue Then
'do nothing
Else
If IsNull(Me.Start) OR IsNull(Me.End) Then
Cancel = True
MsgBox "Start and End dates required."
Else
strWhere = "(Start < " & Format(Me.End, strcJetDate) & _
") AND (" & Format(Me.Start, strcJetDate) & _
" < End) AND (EventID <> " & Me.EventID & ")"
varResult = DLookup("EventID", "tblEvent", strWhere)
If Not IsNull(varResult) Then
MsgBox "Event " & varResult & " clashes."
Cancel = True
Me.Undo
'Add your code here to move to the other record.
End If
End If
End If
End Sub

The code to move to the other record will involve a FindFirst in the
RecordsetClone of the form, similar to this one:
http://allenbrowne.com/ser-03.html

If you want to compare all events against all other events to find clashes,
or handle the possibility of open-ended events (end date/time unknown), see:
Clashing Events/Appointments
at:
http://allenbrowne.com/appevent.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ron Weaver" <RonWeaver@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DF89365C-5959-4F60-B3AA-ECDBCFAAD89A@xxxxxxxxxxxxxxxx
Hi all
I am putting the finishing touches on a project. I have received a lot of
help from everyone and do appreciate it very much. This question concerns
preventing duplicate bookings on equipment. The user has a large inventory
of
equipment (about 100 pcs.), that he books for certain days and times. He
needs to be prompted if he is about to overbook based on date and time.
Also
I would like go to the conflicting order when the prompt is answered. Can
someone help with code, or point me in the right direction? I am not up to
speed on programming.
Thanks


.

Relevant Pages

  • Re: Booking duplicates
    ... Dim strWhere As String ... Cancel = True ... If you want to compare all events against all other events to find clashes, ... Tips for Access users - http://allenbrowne.com/tips.html ...
    (microsoft.public.access.modulesdaovba)
  • Re: Cancel button works when logging in without password
    ... Mark asks a question to do with PCs, Super_Geek dives in and tries to ... Or you can cancel the prompt all together: ... Create a screensaver with a password setting ...
    (microsoft.public.security)
  • Re: unwanted prompts
    ... If I cancel the prompt, the home page is still there - so I don't see why I get this prompt in the first place. ... Another tool when you want information overload, is HijackThis. ...
    (microsoft.public.windowsxp.general)
  • Re: Compulsory Cells based on Condition
    ... Private Sub Workbook_BeforeSave(ByVal SaveAsUI _ ... As Boolean, Cancel As Boolean) ... am not sure how to prompt the user to do that and when to prompt them. ...
    (microsoft.public.excel.misc)
  • Re: How do I make a List box Prompt the user to Populated the fiel
    ... I would like the Prompt to be when they ... Private Sub Form_BeforeUpdate ... and you can set Cancel to True to prevent the user from closing the ... Dim iAns As Integer ...
    (microsoft.public.access.gettingstarted)