Double Booking Problem

[johnblack]

I am creating a tennis court booking system. I am trying to create a macro that will not allow the same court to be booked on the same date at the same time.

I have the following tables and fields

tblMembers
MemberID
FirstName
LastName
etc

tblCourts
CourtID
CourtName

tblSchedule
ScheduleID
ScheduleDate
CourtID

tblSheduleDetails
SheduleDetailsID
SheduleID
MemberID
SheduleStartTime
SheduleEndTime

I have tried to modify the code form a similar post but am getting an error for "Cancel = True"

Private Sub Form_Current()
If Me.NewRecord = True Then
    Dim strWhere As String, strMessage As String
    Dim rsClone As Recordset
 
    strWhere = "(([BookingsSubform].Form![CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
               ") AND ([BookingsSubform].[Form]![ScheduleID]=" & Me.[BookingsSubform].[Form]![ScheduleID] & _
               ") AND ([BookingsTimeSubform].[Form]![ScheduleEndTime]>=#" & _
               Format(Me.[BookingsTimeSubform].[Form]![ScheduleStartTime], "Medium Time") & _
               "#) AND ([BookingsTimeSubform].[Form]![ScheduleStartTime]<=#" & _
               Format(Me.[BookingsTimeSubform].[Form]![ScheduleEndTime], "Medium Time") & _
               "#))"
 
    Set rsClone = Me.RecordsetClone
    rsClone.MoveFirst
    rsClone.FindFirst strWhere
 
    If rsClone.NoMatch Then
        MsgBox ("test")
       Cancel = True
        Exit Sub
    End If
End If
End Sub

Any help would be very nice indeed.
John

[johnblack]

Just to let the know the error is "Compile error: variable not defined"

[MSeda]

try moving your code to the form's before update event. that is the correct event for your validation.

[NeoPa]

As MSeda says, the code is from a

Private Sub Form_BeforeUpdate(Cancel As Integer)
End Sub

...procedure where the variable Cancel is predefined.
Trying to use this code in an OnCurrent event procedure will not work as there is no variable defined there for it to make sense. Hence your error message.

[johnblack]

Ok i have moved the code to the before update event (thanks Mseda) but double bookings are still allowed (the code has no effect). Any ideas from anyone regarding the code.
Thanks
John

[Rabbit]

Private Sub Form_Current()
If Me.NewRecord = True Then
    Dim strWhere As String, strMessage As String
    Dim rsClone As Recordset
 
    strWhere = "(([BookingsSubform].Form![CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
               ") AND ([BookingsSubform].[Form]![ScheduleID]=" & Me.[BookingsSubform].[Form]![ScheduleID] & _
               ") AND ([BookingsTimeSubform].[Form]![ScheduleEndTime]>=#" & _
               Format(Me.[BookingsTimeSubform].[Form]![ScheduleStartTime], "Medium Time") & _
               "#) AND ([BookingsTimeSubform].[Form]![ScheduleStartTime]<=#" & _
               Format(Me.[BookingsTimeSubform].[Form]![ScheduleEndTime], "Medium Time") & _
               "#))"
 
    Set rsClone = Me.RecordsetClone
    rsClone.MoveFirst
    rsClone.FindFirst strWhere
 
    If rsClone.NoMatch Then
        MsgBox ("test")
       Cancel = True
        Exit Sub
    End If
End If
End Sub

Your search criteria is set up wrong. You're using [BookingsSubform].Form![CourtID] when you should be using the name of the field from the table.

[MMcCarthy]

In other words as Rabbit says ...

 
strWhere = "(([CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
 

Mary

[johnblack]

Sorry if i am a slow understandig what you guys mean but i am a newbie to VBA. Now i have this code:

    strWhere = "(([CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
               ") AND ([ScheduleDate]=" & Me.[BookingsSubform].[Form]![ScheduleDate] & _
               ") AND ([BookingsTimeSubform].[Form]![ScheduleEndTime]>=#" & _
               Format(Me.[BookingsTimeSubform].[Form]![ScheduleStartTime], "Medium Time") & _
               "#) AND ([BookingsTimeSubform].[Form]![ScheduleStartTime]<=#" & _
               Format(Me.[BookingsTimeSubform].[Form]![ScheduleEndTime], "Medium Time") & _
               "#))"
 

But it still doesnot work.
Any more ideas?
Thanks
John

[MMcCarthy]

AND [BookingsTimeSubform].[Form]![ScheduleEndTime]>=#" & _
               Format(Me.[BookingsTimeSubform].[Form]![ScheduleStartTime], "Medium Time") & _
               "#) AND ([BookingsTimeSubform].[Form]![ScheduleStartTime]<=#" & _
               Format(Me.[BookingsTimeSubform].[Form]![ScheduleEndTime], "Medium Time") & _
 

All this is doing is comparing values on the form. What is it you are trying to achieve here. If you explain the logic of the criteria I can help further.

Mary

[johnblack]

I am trying to not allow double bookings to be entered in the form. This involves not allowing a particular court to be booked on a particular date for a time that is already taken. For example
Court 1, 18/02/07, 11:00 AM - 01:00 PM and
Court 1, 18/02/07, 11:00 AM - 12:00 PM and
Court 1, 18/02/07, 11:00 AM - 01:00 PM should not be allowed as there are repeats and overlaps

But other entries such as
court 1, 18/02/07, 12:00 PM-01:00 PM and
court 1, 18/02/07, 01:00 PM-02:00 PM and
court 1, 18/02/07, 02:00 PM-05:00 PM should be allowed as there are no repeats or overlaps.

Hope that helps tell me if you need any more detail.
Thanks, John

[MMcCarthy]

Hi John,

Your code won't work because the RecordsetClone won't allow you to reference the start and end times of the bookings. Try the following instead:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim db as DAO.Database
Dim rs as DAO.Recordset
    Dim strMessage As String
Dim startTime As Date
Dim endTime As Date
Dim test As Boolean
 
  test = False
  If Me.NewRecord = True Then
    set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
       "FROM tblSchedule INNER JOIN tblScheduleDetails " & _
       "ON tblSchedule.ScheduleID=tblScheduleDetails.ScheduleID " & _
       "WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
       " AND [ScheduleDate]=#" & Me.[BookingsSubform].[Form]![ScheduleDate] & "#)"
 
    startTime = Me.[BookingsTimeSubform].[Form]![ScheduleStartTime]
    endTime = Me.[BookingsTimeSubform].[Form]![ScheduleEndTime]
    If rs.RecordCount=0 Then Exit Sub
    rs.MoveFirst
    Do Until rs.EOF
      If startTime=rs!ScheduleStartTime Or endTime=rs!ScheduleEndTime Then
        test = True
      ElseIf startTime>rs!ScheduleStartTime And startTime<rs!ScheduleEndTime Then
        test = True
      ElseIf  endTime<rs!ScheduleEndTime And endTime>rs!ScheduleStartTime Then
        test = True
      End If
      If test Then 
        rs.MoveLast
      Else
        rs.MoveNext
      End If
    Loop
 
    If test=False Then
      MsgBox ("Time is available")
    Else
      MsgBox ("Time is unavailable")
      Cancel = True
    End If
  End If
 
  rs.Close
  Set rs=Nothing
  Set db=Nothing
 
End Sub
 

I really think there has to be a better way to do this though.

Mary

[johnblack]

Mary, thanks for your time on this.

    Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
       "FROM tblSchedule INNER JOIN tblScheduleDetails " & _
       "ON tblSchedule.ScheduleID=tblScheduleDetails.Schedule  ID " & _
       "WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
       " AND [ScheduleDate]=#" & Me.[BookingsSubform].[Form]![ScheduleDate] & "#)"
 

This code was highlighted in red with the error "compile error: expected: list seperator or )". I couldnt solve this sorry to be a pain.
John

[MMcCarthy]

Sorry my fault the ending bracket should be outside the quote as follows:

    Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
       "FROM tblSchedule INNER JOIN tblScheduleDetails " & _
       "ON tblSchedule.ScheduleID=tblScheduleDetails.Schedule  ID " & _
       "WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
       " AND [ScheduleDate]=#" & Me.[BookingsSubform].[Form]![ScheduleDate] & "#")
 

[johnblack]

Im sorry to say the code has no effect. I dont know what else to try.
Any ideas?
Thanks for your time anyway.
John

[MMcCarthy]

Try removing it from the form event altogether and put it in the after update event of the ScheduleEndTime control instead with some small amendments as follows:

Private Sub ScheduleEndTime_AfterUpdate()
Dim db as DAO.Database
Dim rs as DAO.Recordset
    Dim strMessage As String
Dim startTime As Date
Dim endTime As Date
Dim test As Boolean
 
  test = False
  If Me.NewRecord = True Then
    set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
       "FROM tblSchedule INNER JOIN tblScheduleDetails " & _
       "ON tblSchedule.ScheduleID=tblScheduleDetails.Schedule  ID " & _
       "WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
       " AND [ScheduleDate]=#" & Me.[BookingsSubform].[Form]![ScheduleDate] & "#)"
 
    startTime = Me.[BookingsTimeSubform].[Form]![ScheduleStartTime]
    endTime = Me.[BookingsTimeSubform].[Form]![ScheduleEndTime]
    If rs.RecordCount=0 Then Exit Sub
    rs.MoveFirst
    Do Until rs.EOF
      If startTime=rs!ScheduleStartTime Or endTime=rs!ScheduleEndTime Then
        test = True
      ElseIf startTime>rs!ScheduleStartTime And startTime<rs!ScheduleEndTime Then
        test = True
      ElseIf  endTime<rs!ScheduleEndTime And endTime>rs!ScheduleStartTime Then
        test = True
      End If
      If test Then 
        rs.MoveLast
      Else
        rs.MoveNext
      End If
    Loop
 
    If test=False Then
      MsgBox ("Time is available")
    Else
      MsgBox ("Time is unavailable")
      Me.ScheduleStartTime = Null
      Me.ScheduleEndTime = Null
    End If
  End If
 
  rs.Close
  Set rs=Nothing
  Set db=Nothing
 
End Sub

[johnblack]

Thanks for your continued help.

    Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
       "FROM tblSchedule INNER JOIN tblScheduleDetails " & _
       "ON tblSchedule.ScheduleID=tblScheduleDetails.ScheduleID " & _
       "WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
       " AND [ScheduleDate]=#" & Me.[BookingsSubform].[Form]![ScheduleDate] & "#")
 

is getting an error of "run time error 2465, MS access can not find the field 'I' referred to in your expression."

rs.Close
 

is getting an error of "run time error 91, object variable or With block variable not set."

John

[MMcCarthy]

Thanks for your continued help.

    Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
       "FROM tblSchedule INNER JOIN tblScheduleDetails " & _
       "ON tblSchedule.ScheduleID=tblScheduleDetails.ScheduleID " & _
       "WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
       " AND [ScheduleDate]=" & Me.[BookingsSubform].[Form]![ScheduleDate])
 

is getting an error of "run time error 2465, MS access can not find the field 'I' referred to in your expression."

Try running this query on it's own but make sure the form is opened in the background. First be sure to chang [MainForm] to the name of your main form. Let me know what happens.

SELECT [ScheduleStartTime], [ScheduleEndTime] 
FROM tblSchedule INNER JOIN tblScheduleDetailsON tblSchedule.ScheduleID=tblScheduleDetails.ScheduleID 
WHERE [CourtID]=[Forms]![MainForm]![BookingsSubform].Form![CourtID] 
AND [ScheduleDate]= [Forms]![MainForm]![BookingsSubform].[Form]![ScheduleDate]

rs.Close
 

is getting an error of "run time error 91, object variable or With block variable not set."

John

Change the last few lines of code from this ...

     End If
  End If
 
  rs.Close
  Set rs=Nothing
  Set db=Nothing
 
End Sub
 

to this ...

     End If
rs.Close
  Set rs=Nothing
  Set db=Nothing
   End If
 
End Sub
 

[johnblack]

I tried to create the query in SQL view with bookings instead of mainform is that right? But i could not save it due to an error of "Syntax error in FROM clause".

John

[MMcCarthy]

I tried to create the query in SQL view with bookings instead of mainform is that right? But i could not save it due to an error of "Syntax error in FROM clause".

John

Try this ...

SELECT [ScheduleStartTime], [ScheduleEndTime] 
FROM tblSchedule INNER JOIN tblScheduleDetails
ON tblSchedule.[ScheduleID]=tblScheduleDetails.[ScheduleID]
WHERE [CourtID]=[Forms]![MainForm]![BookingsSubform].Form![CourtID] 
AND [ScheduleDate]= [Forms]![MainForm]![BookingsSubform].[Form]![ScheduleDate]
 

There were a couple of spacing errors

[johnblack]

I did what you said and got an error of "The Microsoft Jet database engine cannot find the input table or query tblSchedule. Make sure it exists and that its name is spelled correctly. (Error 3078)" when trying to run the query.
I have checked spellings and they are correct.

John

[MMcCarthy]

I did what you said and got an error of "The Microsoft Jet database engine cannot find the input table or query tblSchedule. Make sure it exists and that its name is spelled correctly. (Error 3078)" when trying to run the query.
I have checked spellings and they are correct.

John

That is very strange John. Try opening the table and see if it opens alright and if the data looks OK.

Mary

[MMcCarthy]

That is very strange John. Try opening the table and see if it opens alright and if the data looks OK.

Mary

You could try this test.

Create a new query in access design view and add the tblSchedule and tblSchedule details. Then just drag down the appropriate fields and change the view to SQL query view and see what might be going on with the table name.

[johnblack]

Ok it likes schedule on its own not tblschedule. Thanks this gives me a list of doubles bookings, is there any way I can stop these from being entered in the first place.

Thanks
John

[MMcCarthy]

Ok it likes schedule on its own not tblschedule. Thanks this gives me a list of doubles bookings, is there any way I can stop these from being entered in the first place.

Thanks
John

Did you move the code to the After Update event of the ScheduleStartTime control on the form?

Mary

[johnblack]

Yes i moved it to the after update event. I am still getting this error "run time error 2465, MS access can not find the field 'I' referred to in your expression."

John

[johnblack]

Sorry no i havent moved that code yet. Which part do i replace
i know its a silly question but i had ago and the code went red.
John

[MMcCarthy]

Sorry no i havent moved that code yet. Which part do i replace
i know its a silly question but i had ago and the code went red.
John

Put this in the After Update event of the ScheduleEndTime control

Private Sub ScheduleEndTime_AfterUpdate()
Dim db as DAO.Database
Dim rs as DAO.Recordset
    Dim strMessage As String
Dim startTime As Date
Dim endTime As Date
Dim test As Boolean
 
  test = False
  If Me.NewRecord = True Then
    set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
       "FROM Schedule INNER JOIN tblScheduleDetails " & _
       "ON Schedule.ScheduleID=tblScheduleDetails.Schedule    ID " & _
       "WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
       " AND [ScheduleDate]=#" & Me.[BookingsSubform].[Form]![ScheduleDate] & "#)"
 
    startTime = Me.[BookingsTimeSubform].[Form]![ScheduleStartTime]
    endTime = Me.[BookingsTimeSubform].[Form]![ScheduleEndTime]
    If rs.RecordCount=0 Then Exit Sub
    rs.MoveFirst
    Do Until rs.EOF
      If startTime=rs!ScheduleStartTime Or endTime=rs!ScheduleEndTime Then
        test = True
      ElseIf startTime>rs!ScheduleStartTime And startTime<rs!ScheduleEndTime Then
        test = True
      ElseIf  endTime<rs!ScheduleEndTime And endTime>rs!ScheduleStartTime Then
        test = True
      End If
      If test Then 
        rs.MoveLast
      Else
        rs.MoveNext
      End If
    Loop
 
    If test=False Then
      MsgBox ("Time is available")
    Else
      MsgBox ("Time is unavailable")
      Me.ScheduleStartTime = Null
      Me.ScheduleEndTime = Null
    End If
 
    rs.Close
    Set rs=Nothing
    Set db=Nothing
 
  End If
 
End Sub
 

[johnblack]

Sorry been away for a few days.
For this code:

    Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
       "FROM Schedule INNER JOIN ScheduleDetails " & _
       "ON Schedule.ScheduleID=ScheduleDetails.ScheduleID " & _
       "WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
       "AND [ScheduleDate]=#" & Me.[BookingsSubform].[Form]![ScheduleDate] & "#")
 

I am getting an error of "run time error 2465, MS access cant find the field 'I' referred to in your expression."

While i was away i created a new query that showed the double bookings. Is there any way I can validate these fields using this query as data is entered.

Thanks
johnblack

[MMcCarthy]

Check that all the field names are exactly correct and that the subform object is called BookingsSubform (you will find this by checking the object properties under other for Name).

If they are all correct then try this. I have made one minor change.

    Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
       "FROM Schedule INNER JOIN ScheduleDetails " & _
       "ON Schedule.ScheduleID=ScheduleDetails.ScheduleID " & _
       "WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
       "AND [ScheduleDate]=#" & Me.[BookingsSubform].Form![ScheduleDate] & "#")
 

Mary

[atc]

Hello again mary.
Im sorry to say I have more bad news. I did what you said and am still getting the same error as before. When i click debug the yellow arrow is pointing at this line:

"AND [ScheduleDate]=#" & Me.[BookingsSubform].Form![ScheduleDate] & "#")
 

Any more ideas?
I could send you the database if you want.....
johnblack

[MMcCarthy]

Hello again mary.
Im sorry to say I have more bad news. I did what you said and am still getting the same error as before. When i click debug the yellow arrow is pointing at this line:

"AND [ScheduleDate]=#" & Me.[BookingsSubform].Form![ScheduleDate] & "#")
 

Any more ideas?
I could send you the database if you want.....
johnblack

John

Why has your user profile changed? Which should I send a PM to?

Mary

[atc]

Mary.
I had a problem with the johnblack one so I set up a new one but then got the old one working again.
So pm the johnblack one if you want or other one up 2 you.
John

[MMcCarthy]

Mary.
I had a problem with the johnblack one so I set up a new one but then got the old one working again.
So pm the johnblack one if you want or other one up 2 you.
John

Since you are currently logged in as atc I'll send it there.

Mary

[NeoPa]

Hello again mary.
Im sorry to say I have more bad news. I did what you said and am still getting the same error as before. When i click debug the yellow arrow is pointing at this line:

"AND [ScheduleDate]=#" & Me.[BookingsSubform].Form![ScheduleDate] & "#")
 

Any more ideas?
I could send you the database if you want.....
johnblack

That's because the previous line doesn't include a separator (space) after the previous control reference in the WHERE clause.

[NeoPa]

The code should instead read :

Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
       "FROM Schedule INNER JOIN ScheduleDetails " & _
       "ON Schedule.ScheduleID=ScheduleDetails.ScheduleID " & _
       "WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & " " & _
       "AND [ScheduleDate]=#" & Me.[BookingsSubform].Form![ScheduleDate] & "#")

[MMcCarthy]

Good catch, how did I miss that? :D

[johnblack]

Mary did you receive the file in the end.
John

[MMcCarthy]

Mary did you receive the file in the end.
John

No John the second email had no attachment either.

Mary

[MMcCarthy]

No John the second email had no attachment either.

Mary

There were a couple of problems John.

1. You were trying to refer to controls on SubformA from SubformB so you can't use the Me. you need the full reference.

2. The rs.MoveNext has to be removed from the IF statement as it runs regardless of the whether the test is true or not.

Code should now be as follows:

Private Sub ScheduleEndTime_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMessage As String
Dim startTime As Date
Dim endTime As Date
Dim test As Boolean
 
  test = False
  If Me.NewRecord = True Then
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
            "FROM Schedule INNER JOIN ScheduleDetails " & _
            "ON Schedule.ScheduleID=ScheduleDetails.ScheduleID " & _
            "WHERE [CourtID]=" & Forms![Bookings]![BookingsSubform].Form![CourtID] & " " & _
            "AND [ScheduleDate]=#" & Forms![Bookings]![BookingsSubform].Form![ScheduleDate] & "#")
 
    startTime = Me.ScheduleStartTime
    endTime = Me.ScheduleEndTime
    If rs.RecordCount = 0 Then Exit Sub
    rs.MoveFirst
    Do Until rs.EOF
        If startTime = rs!ScheduleStartTime Or endTime = rs!ScheduleEndTime Then
          test = True
        ElseIf startTime > rs!ScheduleStartTime And startTime < rs!ScheduleEndTime Then
          test = True
        ElseIf endTime < rs!ScheduleEndTime And endTime > rs!ScheduleStartTime Then
          test = True
        End If
        If test Then
          rs.MoveLast
        End If
        rs.MoveNext
    Loop
 
    If test = False Then
      MsgBox ("Time is available")
    Else
      MsgBox ("Time is unavailable")
      Me.ScheduleStartTime = Null
      Me.ScheduleEndTime = Null
    End If
 
    rs.Close
    Set rs = Nothing
    Set db = Nothing
 
  End If
 
End Sub
 

Mary

[atc]

Thanks so much it works like a charm. Would it be possible for you to try and solve one other small problem. At the moment I have this code

Private Sub Form_AfterUpdate(Cancel As Integer)
  Dim strWhere As String
  Dim varKey As Variant
 
  strWhere = "(CourtID=" & Me.CourtID & ") AND " & _
             "(ScheduleDate=" & Format(Me.ScheduleDate, "\#m/d/yyyy\#") & ")"
  varKey = DLookup("[CourtID] & Format([ScheduleDate],' \- d mmm yyyy')", _
                   "[Courts]", _
                   strWhere)
  If Not IsNull(varKey) Then
    Cancel = True
    Call MsgBox(strWhere & vbCrLf & varKey)
    Debug.Print strWhere & vbCrLf & varKey
  End If
 
End Sub
 

On the bookingsubform. This is to try and stop the same court and scheduledate being repeated in different records. Any ideas what is wrong?
ATC

[atc]

By this I mean court 1 on 01/03/2007, court 2 on 01/03/2007, court 1 on 02/03/2007 etc would be acceptable records.
But court 1 on 01/03/2007 and court 1 on 01/03/2007 would not be.
Thanks
ATC

[MMcCarthy]

By this I mean court 1 on 01/03/2007, court 2 on 01/03/2007, court 1 on 02/03/2007 etc would be acceptable records.
But court 1 on 01/03/2007 and court 1 on 01/03/2007 would not be.
Thanks
ATC

Try switching it to the Before Update event.

Mary

[NeoPa]

By this I mean court 1 on 01/03/2007, court 2 on 01/03/2007, court 1 on 02/03/2007 etc would be acceptable records.
But court 1 on 01/03/2007 and court 1 on 01/03/2007 would not be.
Thanks
ATC

You haven't said what is going wrong with this but, as Mary says, there is no Cancel parameter in the AfterUpdate event.
The idea is that you check the data before updating and, optionally, cancel the update. It would not make too much sense to try this after the update has already been applied.

[atc]

Thanks for your help. I
have now moved it to the before update event. When I try to input court 1, 01/03/2007 for example (not a duplicate). I get a message of "(CourtID=1) AND (ScheduleDate=#3/1/2007#) 1 - 1 Mar 2007".
I click OK and then have to press escape to exit the cell (cant click out of it). This takes the datasheet to what it was before. So i cant add new records or edit records.
Any ideas?
ATC

[MMcCarthy]

Thanks for your help. I
have now moved it to the before update event. When I try to input court 1, 01/03/2007 for example (not a duplicate). I get a message of "(CourtID=1) AND (ScheduleDate=#3/1/2007#) 1 - 1 Mar 2007".
I click OK and then have to press escape to exit the cell (cant click out of it). This takes the datasheet to what it was before. So i cant add new records or edit records.
Any ideas?
ATC

Try this...

Private Sub ScheduleDate_AfterUpdate()
   If Not IsNull(Me.CourtID) Then
      CheckDate
   End If
End Sub
 
Private Sub CourtID_AfterUpdate()
   If Not IsNull(Me.ScheduleDate) Then
      CheckDate
   End If
End Sub
 
Function CheckDate()
  Dim strWhere As String
  Dim varKey As Variant
 
  strWhere = "(CourtID=" & Me.CourtID & ") AND " & _
             "(ScheduleDate=" & Format(Me.ScheduleDate, "\#m/d/yyyy\#") & ")"
  varKey = DLookup("[CourtID] & Format([ScheduleDate],' \- d mmm yyyy')", _
                   "[Courts]", _
                   strWhere)
  If Not IsNull(varKey) Then
   Call MsgBox(strWhere & vbCrLf & varKey)
   Debug.Print strWhere & vbCrLf & varKey
   Me.ScheduleDate = Null
  End If
 
End Function
 

Mary

[atc]

Thanks for your continued help.
With this code is place (entering the data courtID of 1 and a date of 01/03/2007 - not a duplicate) I get the same message as before but when I click ok it keeps the courtID of the new record, deletes the schedule date and moves to courtID of a new record below.
I also get the same result if the data is a duplicate.
ATC

[NeoPa]

John,
This is not really the thread to discuss this code.
I suggested that code in a completely different thread and half of it is simply to give debug information. It is not finished code. To work with someone else on this code is not good. How do you expect me to progress it with you if you get changes from outside sources.
If you want me to stop then please just let me know, otherwise I would appreciate being given the chance to deal with it without extraneous influences complicating matters even further (back in the original thread). Quite apart from anything else, the original thread will be no use as a reference if it stops dead in it's tracks half-done.

[atc]

Ok sorry NeoPa i thought two heads may be better than one. I'll post in the orginal thread.
john

[NeoPa]

Ok sorry NeoPa i thought two heads may be better than one. I'll post in the orginal thread.
john

Thank you.
(Stop Duplicates) in case anyone wants to come over.
It's probably best though, within such a tight debugging loop as this one, that the two of us focus together unless and until we find we can't sort it. Extra heads in a situation like this is more likely to distract than help.

[MMcCarthy]

LOL - Are you calling me a distraction Ade.

John

In this case he is quite correct as I was unaware of the other thread I was missing half the information. NeoPa will call me in if he needs any futher assistance although I doubt it. He's really quite good just don't tell him I said that.

Mary