Logic Error when using DCount

[Cyd44]

When editing a booking record I wish to validate the change by looking in the bookings table to check that confilct does not occur.

I have a record as follows:-

Location Start Date Start time End Time

Room 1 3/11/2011 08:00 AM 13:00 PM etc.

When I Edit Either Room or Date it appears to work OK but If I change the Time (either Start or end or Both) the code says it confilcts with another booking.

My Code is as follows:-

'Setting the Variables assigned for the changed record
         dteDate = Me!BookStartDate
         strRoomNo = Me!BookLocation
         dteStartTime = Me!BookTime
         dteEndTime = Me!BookEndTime
        
         Valid = 0
         
         
'        The SQL String Variable below takes the info entered in the booking form to compare a table record.
'        This is a cut and Paste from the Check Booking script within the Bookings form. WILL NOT WORK WHEN CHANGING TIME ONLY?????
         
         
        
          strSQL = "([BookStartDate] = #%D#) AND " & _
         "([BookLocation] = '%R') AND " & _
         "([BookTime] < #%E#) AND " & _
         "([BookEndTime] > #%S#)"
         strSQL = Replace(strSQL, "%D", Format(dteDate, "m/d/yyyy")) ' Substitues form date to % variables
         strSQL = Replace(strSQL, "%R", strRoomNo)
         strSQL = Replace(strSQL, "%E", Format(dteEndTime, "H:m:s"))
         strSQL = Replace(strSQL, "%S", Format(dteStartTime, "H:m:s"))
  
         
         Valid = (DCount("*", "tblRoomsBooking", strSQL))

What is confusing me is that it works fine for changing a room or the date, but it conflicts when changing time (even though there is no other booking for the same date in this room?

Could anyone suggest where my code logic is incorrect?

[ADezii]

To make a Booking Invalid:

1. The Start Date and Location would have to be the same, and at least 1 of the following 3 conditions must exist:
   1. The Start Time is between the Start and End Range.
   2. OR
   3. The End Time is between the Start and End Range.
   4. OR
   5. The Start Time is < the Start Time AND the End Time > the End Time
2. The SQL as I see it, would be:
   

   strSQL = "([BookStartDate] = #" & dteStartDate & "#) AND ([BookLocation] = '" & strLocation & "') AND (" & _
             "(#" & dteStartTime & "# BETWEEN [BookStartTime] AND [BookEndTime]) OR (#" & _
             dteEndTime & "# BETWEEN [BookStartTime] AND [BookEndTime]) OR (#" & dteStartTime & _
             "# < [BookStartTime] AND #" & dteEndTime & "# > [BookEndTime]))"
           
             MsgBox IIf(DCount("*", "tblRoomsBooking", strSQL) > 0, "INVALID", "VALID")

P.S. - The following Logic has been tested, and appears to work quite well. Should you like to see the Demo DB, I would be glad to Attach it.

[Cyd44]

Hi ADezii,
Many thanks for this, I have tried it and the results show VALID, even though the new times conflict with another booking.

I may be trying to do the impossible here however.

I have a form which lists a number of bookings for a chosen date. I am trying to select any of the records from the form and change a field. What I need to do then is check that the new iformation entered onto the form does not conflict with any record held within the table. I am using Me.variable to for both the Form and the table and am wondering if my logic is wrong.

Capturing the variables using MsgBox appears to show that the Me.variable is equal to the [Table Variable]. The result of the SQL however should be Invalid shoudn't it?. When you change date in a bound Form, does this change the record value also?

[ADezii]

Try Saving the Record prior to the actual Validation, Cyd44. If this doesn't work, sends me (Attach) the referenced Form along with the underlying Data Source, and hopefully I can see where the problem lies.

[Rabbit]

I suspect the data types of those fields are strings rather than date time.

Also, the logic can be simplified to
NewStart <= OldEnd AND NewEnd >= OldStart

[Cyd44]

Hi ADezii

Tries your suggestion but got same result. Have extracted the form and related table and taken out the stuff which will cause error. the form will run for you to demonstrate the problem. Just try and edit the date on the form and press the save edit button to see the results. Hope you can fathom this out as I am at a loss here.

[ADezii]

Here is a Demo that should say it all.

[Cyd44]

Hi ADezii

Many thanks for this but I think you have misunderstood what I am trying to achieve. I already have a scripty that successfully checks and adds a NEW record. What I now want to do is edit the old record and check the new values for validity.

I suspect the way I am trying to do it is not possible as I am using a Bound Form. I have trapped by MsgBox both the [BookStartDate] and the Me.StartDate and they appear to be the same, even though the record has not been saved. I think waht I am trying is illogical.

The scenario is
1. A booking record is contained within the table
2. The bound form displays a number of records for a chosen date in tabular format
3. I want to chose a record from the form listing and edit it.
4. Before saving it, I want to check the validity against all records in the table.
5. If no confilct exists I want to save the edited record.
6 If confilct exists I want to undo the change and exit.

What I am percieving is that my approach is wrong as it is changing the old record to the values of the edit so it is checking against itself.
If you use the form data I gave you and try to change any data on the form, the results of clicking the Save Edit button will show you what I mean.
Your valuable Bookings.Zip is great and I fully appreciate it, but I have the Add new booking procedure working fine. It is the Edit old booking I cannot work out.
Hope this makes sense?

[Rabbit]

Exclude the existing record from the check.

[Cyd44]

Hi Rabbit,

I think you are correct but unfortunitely I dont know how to exclude the record from the search?

[ADezii]

@Cyd44:
Try this approach, making change(s) to a record then Validating them:

[Cyd44]

ADezii

I placed 2 records for Room 1 with dates of 11/3/2011 I then tried to Edit record 1 and changed the Start Time to conflict with Record 2. When I clicked Validate booking it comes back Record Saved.



I think that Rabbit has the right Idea in that we need to eliminate the record being changed from the search so that it appears to be a new record?

[ADezii]

@Cyd44:
I think that Rabbit may have just saved the day. I modified the SQL to exclude the Current Record by adding a Primary Key Field to the Table, excluding it in the SQL Statement, then adding it to the Form in order to exclude this Record. I did not, however, have the time to test my Theory, so I'll leave the latest Update with you (via Attachment), then you can tell me.

strSQL = "[BookID] <> " & Me![BookID] & " AND " & _
         "([BookStartDate] = #" & dteStartDate & "#) AND ([BookLocation] = '" & strLocation & "') AND (" & _
         "(#" & dteStartTime & "# BETWEEN [BookStartTime] AND [BookEndTime]) OR (#" & _
         dteEndTime & "# BETWEEN [BookStartTime] AND [BookEndTime]) OR (#" & dteStartTime & _
         "# < [BookStartTime] AND #" & dteEndTime & "# > [BookEndTime]))"

P.S. - Never thought that this Thread would be so challenging! (LOL).

[Cyd44]

Thanks ADezii,

I suspect it is because Access will automatically save a record when you move away from it to click the Save Edit button and therefore it is checking agianst itself! I also think Rabbit is correct as we need to eliminate the pre-edit from the SQL check.

I will try your recommeded solution and get back to you.This might be tomorrow now as it is getting very late and I am almost at the point where I cannot see the wood for the trees. I will get back though.

Thanks to Rabbit for the idea.....think this is the right way to go.

[Cyd44]

ADezii,

Great news! I finally got it to work with a little alteration, here is the final code

 If [BookID] <> Me.BookID Then
        
         strSQL = "([BookStartDate] = #%D#) AND " & _
        "([BookLocation] = '%R') AND " & _
        "([BookTime] < #%E#) AND " & _
        "([BookEndTime] > #%S#)"
        strSQL = Replace(strSQL, "%D", Format(dteDate, "d/m/yyyy")) ' Substitues form date to % variables
        strSQL = Replace(strSQL, "%R", strRoom)
        strSQL = Replace(strSQL, "%E", Format(dteEndTime, "H:m:s"))
        strSQL = Replace(strSQL, "%S", Format(dteStartTime, "H:m:s"))
        
        End If
         
        Valid = (DCount("*", "tblRoomsBooking", strSQL))
        
        If Valid > 0 Then
        MsgBox ("Your Change Confilcts with a Prior Prior Booking, this cannot be completed")
        Me.Undo
        Exit Sub
         
        Else
        MsgBox ("Change is Valid and saved into Database, The old Oulook Appointment will now be deleted")
         
        DoCmd.RunCommand acCmdSaveRecord 'Ensure the changed booking is saved
        End If

All I need to do was to put an If Condition before executing the SQL and this eliminated the search on the record chosen. I already had a key Field avialable for each record (BookID) so I could trap the record on entry.

Can a say a massive thank you to yourself and to Rabbit for coming up with the idea in the first place.
I can finally go to BED.
Cheers guys