Time Interval Overlapping (MS Access 2003)

[Zac Harvey]

Hi there

I'm creating a database to book rooms. I have created a form frmBookings linked to table tblBookings where the data will be stored. The meta data is as follows:

AbsenceID; AutoNumber; PK
EmployeeID; Numeric; FK
ReasonID; String; FK
StartDate; Date;
EndDate; Date;
Comments; Text;

What I'm trying to do is stop any bookings being made for a room that has already been booked at the specified time. And notify the user with a pop-up box.

Example:
Room1 is booked on 31/3/11 from 09:00-12:00. The user tries to book the same room on the same date at 11:00-15:00, at which point a pop-up box displays "Sorry the room is already booked between 9:00 and 12:00"

I have tried applying the following example to my database without any luck. http://bytes.com/topic/access/answers/720025-iif-query

So if you can provide any help it would be most greatly appreciated. Please ask if you need any more information to work with.

Zac

[NeoPa]

Zac, to determine if any two periods overlap (See Definition of Terms below) you check :
X <= B AND Y >= A

If both of these statements are determined to be TRUE then there is an overlap. If a touching overlap (where one starts immediately as the other ends) is not to be considered then lose the equals (=s).


Definition of Terms :
Period 1 is from time A to time B.
Period 2 is from time X to time Y.

[Zac Harvey]

Thanks for your reply NeoPa.

I understand your answer, and have trialed it in an excell spreadsheet. Unfortunately however I have no idea how to implement it. I supplied the information above in the hope that someone could throw together a bit of code that I could use in my form.

[NeoPa]

I'll be happy to help Zac, but we don't do things quite that way (provide specific solutions) here. We prefer that you post your attempt at the solution, or otherwise indicate what you've tried and what went wrong, then we guide you to understand where it's wrong and how better to do it. Otherwise we're not helping you to move on, but just encouraging you (all - not you personally) to allow someone else to do it for you. That's not what we see ourselves as here for.

[Zac Harvey]

Sry for the delay, I've had other work on my plate. I understand your previous post, however I have little to know working knowledge of VBA in Access so unfortunately I didn't really no where to start. Had you previously given me some code to work with contrary to your point that you would not be helping me, it would infact have helped me greatly as I learn on the principal of reverse engineering so would not have just used your code and moved on but studied it and attempted to understand the logic of it before accepting it.

I have however tried something.. which is not working and will attatch pictures showing the code I'm using and the error which comes up. So if you could help me from this point it would be greatly appreciated Neo.

[TheSmileyCoder]

I can't put my finger on a specific point for sure. One thing I note is that you are using a reserved word Date. Date is a function in access and you should try to avoid using Date in your tables. It is possible to work with it, but It will likely save you some headache if you use BookingDate instead of Date for instance.

If you continue to use Date, you need to enclose it in square brackets to force access to recognize it as a field, and not a function, i.e. [Date]

Another thing is that you seem to (maybe intentionally) in the example provided have a startTime of 9 and a endtime of 5. (Ie. The endtime before the starttime) You should probably include a check to ensure that StartTime<Endtime. The first 2 checks for starttime and endtime you make in the screenshot will always return false.

You also have (line 4 of the Dcount code) ENDTIME=>. It should be ENDTIME >=, however I think that access will handle that gracefully enough. But im not 100% sure.

That said your new post is a very good example of a clearly described problem, and is easy for us to work and build on. We can see alot of the logic in and behind your code, and you very accurately give us the error information. (You would be surprised how many posters simple state: "I got AN error message", without giving any details as to which error.)

EDIT: On a somewhat unrelated to your problem note, as I see your function it can only return true or false. In that case I see no reason to declare the function as a variant. You might as well take the full step and declare it as a boolean instead.

[NeoPa]

Zac, in response to your claim I will trust that you will use what I profer as helpful information to learn from and post what I can. I will use the code you've posted and the error message to help me prepare that code though (another reason why it is very helpful to see what you already have first) as it can tell us much about the environment you're working in without you having to go to the trouble of remembering and relaying all the details we'd require to build the code. Code that matches your actual scenario (object names etc) is much easier for you to work with and understand.

I would also go along with Smiley's comments about how well your last post laid out the situation, including both the code and the error message in full. In future though, please post the code as data within [ CODE ] tags instead. There are real advantages to this and our rules insist on it (We appreciate a good first attempt when we see one though of course - just remember for next time if you would).

While I'm here and have your attention, let me also post some helpful comments on dealing with situations such as that which caused your error. It's indented below :

One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.

As for the code, look at the following (and please do understand what it's doing and why it works) :

Dim strWhere As String, strMsg As String
 
With Me
    strWhere = "(([StartTime]<=#%S#) AND " & _
               "([EndTime]>=#%E#) AND " & _
               "([RoomID]=%R) AND " & _
               "([Date]=#%D#))"
    strWhere = Replace(strWhere, "%S", Format(.EndTime, "HH:mm:ss"))
    strWhere = Replace(strWhere, "%E", Format(.StartTime, "HH:mm:ss"))
    strWhere = Replace(strWhere, "%R", .RoomID)
    strWhere = Replace(strWhere, "%D", Format(.Date, "m/d/yyyy"))
    If DCount("*", "[tblBookings]", strWhere) > 0 Then
        strMsg = "This room has already been booked between %F and %T on %D."
        strMsg = Replace(strMsg, "%F", DLookup("Format([StartTime], 'HH:mm')", _
                                               "[tblBooking]", _
                                               strWhere))
        strMsg = Replace(strMsg, "%T", DLookup("Format([EndTime], 'HH:mm')", _
                                               "[tblBooking]", _
                                               strWhere))
        strMsg = Replace(strMsg, "%D", Format(.Date, "Short Date"))
        Call MsgBox(strMsg)
    End If
End With

See how you get on with this. We can answer any questions if there's anything here you struggle to understand. Good luck.

[Zac Harvey]

Hi again Neo, thanks for your response. I really appreciated you taking the time to help. Having acted on Smileys suggestions and changing a few other things in my form code I actually seemed to get it working yesterday, although not entirely sure how. But it still throws an error message. I will detail this below just for purpose of interest however having read your code I will try to use that instead especially since it returns the infromation of the booking that clashes.

Anyway here's the function code. In Code brackets you will be pleased to see. The emboldened line 25 is what gets highlighted but this just points to the whole function. I think it might simply be because I haven't defined an event for =True or False

1.

2.

3.

Private Function ValidateTimeSlot() As Variant
 
     With Me
         If IsNull(.StartTime) Or IsNull(.EndTime) Or _
             IsNull(.RoomID) Or IsNull(.Date) Then
             ValidateTimeSlot = Null
             Exit Function
End If
         If DCount("BookingID", "tblBookings", _
             "((StartTime>#" & .StartTime & "# And StartTime<#" & .EndTime & _
             "#) Or (EndTime>#" & .StartTime & "# And EndTime<#" & .EndTime & _
             "#) Or (StartTime<=#" & .StartTime & "# And EndTime>=#" & .EndTime & _
             "#)) AND RoomID=" & .RoomID & _
             " AND [Date] = #" & .[Date] & "#") <> 0 Then
             MsgBox ("This room has already been booked during this time.")
             ValidateTimeSlot = False
Else
             ValidateTimeSlot = True
End If
     End With
 
 End Function
 
Private Sub cmdSave_Click()
 Run ValidateTimeSlot()
End Sub
 

In the meantime however I do have a question. Might sound silly; but why do you need to define the comparable times as %S etc and then replace them? Why not just ([StartTime]<=(Format(.EndTime, "HH:mm:ss"))) AND " & _ or is that not possible?

[Zac Harvey]

Forgot to mention my thanks to you Smiley. So a BIG THANKS For your constructive feedback! I will take into account all that you have both suggested and advised to improve my knowledge. Thanks also for praising my post, this means a lot as I try to make life easier for those that are willing to help others.

[TheSmileyCoder]

Im guessing the reason for using %S is a matter of making it look neater when your writing it. Its a matter of preference I think, but maybe NeoPa has some reasons I am not able to see. Personally I use the same approach as you initially have done.

Now the

Private Sub cmdSave_Click()
  Run ValidateTimeSlot()
End Sub

What do you expect should happen from this?
ValidateTimeSlot will return either true or False, which will result in for example: Run False making little sense.

Now, instead what should be done is to use the BeforeUpdate event of your form (Note this is all based on the assumption that your using a bound form)

At the top of your forms module:

Private bSave as boolean

For the Click Event:

Private Sub cmdSave_Click()
  'If form is not dirty, just exit, there is nothing to save
    If Not me.Dirty then
      Docmd.Close acform, Me.Name
      exit sub
    End if 
 
  'Indicate that user is saving by choice
    bSave=True
 
  'Dont show error messages if the save fails
    On Error Resume Next
 
  'Force a save   
     Me.Dirty=False
     bSave=False 'Reset
  If Err.Nr<>0 then
    'Some err occured while saving, 
    'likely a booking conflict. 
    'Clear error and return to form.
     Err.Clear
     'Resume normal error handling
     On Error goto 0
     Exit Sub
  If 
 
  'Resume normal error handling
    On Error goto 0
 
End Sub

Private Sub Form_BeforeUpdate(Cancel as Integer)
  If Not bSave then
    'Record is being saved as the result of the form closing,
    ' or by form navigation. 
    'Confirm that user wishes to save
    If vbYes<>Msgbox("Do you wish to save changes?")
      'User does not wish to save
      Cancel=True 'Cancel the save
      Exit sub
    End If
  End If
  'Now set the Cancel to whether or not the validation succeeded
  Cancel=NOT validateTimeSlot
 
End Sub

I know this might be alot of code to take in at once. The BeforeUpdate is fired each time information is saved in Access. Its a very usefull event to manipulate, especially since it can be cancelled. The variable bSave only has the purpose to NOT show the save confirmation dialog if the user clicked save (as opposed to closing the form, which will also result in a save attempt if the form is dirty.)

If you have questions, you know where to find us :P

[NeoPa]

Zac, your attitude is appreciated. Rarer than we'd like, but certainly appreciated ;-)

Let me start at the end with your two questions.

Zac:
but why do you need to define the comparable times as %S etc and then replace them? Why not just ([StartTime]<=(Format(.EndTime, "HH:mm:ss"))) AND " & _ or is that not possible?

Even these I'll answer in reverse order, as the understanding of the second pertains to the first as well, but I'll include both in my explanation.

SQL code (of which the strWhere string is an example) works by containing the actual SQL in a simple string. Actually, this is related to the point in my earlier post (#7) about debugging SQL, but I'll answer directly anyway. SQL is passed as a string. This need not be a variable, but it is a single, pre-worked out, string. What you're actually doing in your VBA code is creating that string. This involves connecting strings together as well using other string functions where they make it easier (such as Replace() to illustrate the structure clearly while inserting the values separately).

Now, consider a simpler scenario. You need to create a string (not SQL - just English) saying who you are - "My name is Zac Harvey.". Simple enough in VBA.

strName = "My name is Zac Harvey."
Debug.Print strName

Result = My name is Zac Harvey.
Consider now the requirement for this to work showing the name entered on the current form in a control called [txtName]. The following code is equivalent to what you were asking about :

strName = "My name is Me.txtName."
Debug.Print strName

Result = My name is Me.txtName.
Notice that the result includes the name of the control rather than its value. To get the value into the string, we need to append the actual value to a string containing the text we already know (as coders) needs to be there.

strName = "My name is " & Me.txtName & "."
Debug.Print strName

Result = My name is Zac Harvey.
This works, but sometimes the code to create such strings can get very involved and complicated (messy), so we use the Replace function to indicate clearly where the changeable items fit into the overall structure of the string, as well as replacing the placeholders with the items themselves.

strName = Replace("My name is %N.", "%N", Me.txtName)
Debug.Print strName

Result = My name is Zac Harvey.

[NeoPa]

Good point there by Smiley (I'm sorry I overlooked the error in your code). I was going to say Run has no meaning, but (particularly after Smiley's point) I checked it in help and found it had functionality I thought was only available in Excel. Nice. Not what you want though. I suspect you're after the Call statement instead. This calls a procedure directly but it drops any function value if returned (has little effect on subroutine type procedures). The syntax is similar to calling the procedure directly except the parameters are included within parentheses. I illustrate the two ways below including parameters for illustration even though that particular function procedure takes none :

ASubRoutine Parameter 1, Parameter2
Call ValidateTimeSlot(Parameter 1, Parameter2)
or
blnX = ValidateTimeSlot(Parameter 1, Parameter2)

[NeoPa]

BTW I have a MultiReplace function that allows me to include a single template string with multiple pairs of parameters to indicate replaceable items. I use it very heavily as the more complicated strings become (working in SQL mostly of course) the more difficult it is to read and understand strings made up of multiple strings concatenated together. It's not very long but a fair portion of my processing must be taken up going through that procedure. I'll include it here for any interested parties :

'MultiReplace replaces all occurrences of varParam in strMain with varReplace.
'Using VbBinaryCompare means that case is not ignored.
Public Function MultiReplace(ByRef strMain As String, _
                             ByVal varParam As Variant, _
                             ByVal varReplace As Variant, _
                             ParamArray avarArgs()) As String
    Dim intIdx As Integer
 
    If (UBound(avarArgs) - LBound(avarArgs)) Mod 2 = 0 Then Stop
    MultiReplace = Replace(Expression:=strMain, _
                           Find:=Nz(varParam, ""), _
                           Replace:=Nz(varReplace, ""), _
                           Compare:=vbBinaryCompare)
    For intIdx = LBound(avarArgs) To UBound(avarArgs) Step 2
        MultiReplace = Replace(Expression:=MultiReplace, _
                               Find:=Nz(avarArgs(intIdx), ""), _
                               Replace:=Nz(avarArgs(intIdx + 1), ""), _
                               Compare:=vbBinaryCompare)
    Next intIdx
End Function

PS. I'm glad I just did that. I just noticed that my live version was returning a value of undeclared type (Variant in effect). I feel sullied and dirty, and it's my own code so not even anyone else to blame. I've fixed it now (I may need a shower to help me forget).

[Zac Harvey]

Information overload me thinks. I had to break for lunch after reading these posts... I'm bk now though and raring to go.

[NeoPa]

I hear you Zac. You seemed interested so I wanted to give you as much as I could (within reason). Clearly it's all optional for you, but anything you gain from this is all bonus. Feel free to ask about anything you may be unsure of.

[Zac Harvey]

Okedoke,

I've tried my best to absorb all the above, but still some of it is unfortunately beyond my current grasp of understanding. I've abandonded the origional code and used Neo's one as I said I would. I have done a little adaptation and incorporated the use of the 'If Dirty' thing, or at least tried. It seems to be working to an extent but throws an error if any of the required fields are blank (not the usual error). Also I tried adding into the message string who had booked the room which clashes but I noticed it wasnt actually picking out the right data.

I have uploaded a archive file of the database this time but with just the nescessary tables and forms for this so you can see everything thats going on. I can assure you it is virus free and it's on my own webspace.

What I would like here is two cmd buttons.
1. Save and close (Navigate backto main menu)
2. Save and go to new record.

TestDatabase.rar

Anyways, see what you think and get back to me when possible. Thanks again guys.

P.S. I don't like fraternizing with filth, so I hope you've had a good shower Neo :P

[NeoPa]

This database doesn't compile Zac. ** Edit ** Actually, maybe it does. unfortunately there's no info yet on what I should be looking at so I tried to open frmCalendar and it crashed on me. If you follow the instructions below you will find one saying to include instructions in the post on how to test the issue(s) in the database. ** /Edit **

What I'll do is post some hints I've used before for posting database attachments and also for posting code (as the code in the db will be part of what I'm to look at). Normally, databases should be posted only when requested, as this implies a much higher level of work and attention than dealing with a simple, well written, post (which in our naievety is what we expect here). I'm happy to accept a database from you to look at though in this instance, but please check through all the recommendations first. I doubt all the points will apply to you but you should get the database code to compile first at least (if you can). If that proves too tricky then we still need to focus on that first, even if I'm to help you over that hurdle.

Obviously ignore any points which don't pertain to this situation.

When attaching your work please follow the following steps first :

1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
3. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
4. If the database includes any code, ensure that all modules are set to Option Explicit (See Require Variable Declaration).
5. If you've done anything in steps 1 to 4 then make sure that the problem you're experiencing is still evident in the updated version.
6. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
7. Compact the database (Tools / Database Utilities / Compact and Repair Database...).
8. Compress the database into a ZIP file.
9. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.

It's also a good idea to include some instructions that enable us to find the issue you'd like help with. Maybe some instructions of what to select, click on, enter etc that ensures we'll see what you see and have the same problems.

When posting any code on here please :

1. For VBA code specifically :
   1. Ensure you have Option Explicit set (See Require Variable Declaration).
   2. Try to compile it. If it doesn't compile for any reason please explain that clearly - including the error message and which line of your code it appears on. Compilation is done from the Visual Basic Editor menu - Debug \ Compile Project (Where Project is the actual name of your project).
2. For SQL as well as VBA :
   1. Copy your code (using the Clipboard - Cut / Copy / Paste) from your project directly into your post. Typing in code is not appreciated as it is likely to introduce typos which cause members to waste their time unnecessarily.
   2. Ensure that the code in your post is enveloped within CODE tags. The hash (#) button in the posting page helps with this. Simply select your code and click on the hash button to have it enveloped automatically.

If all these points are covered then all members will be better able to understand, and therefore attempt to answer, your question.

I will spend some time looking anyway, but please replace the copy you have when you can with a fixed version, or notify me exactly where we stand on it if that's not possible.

PS. Posting your database externally is also a perfectly acceptable option. These instructions are general purpose and not designed for this case specifically.

[NeoPa]

Points in cmdSave_Click() :

1. It's a Save & New, so the code closing the form is inappropriate.
2. Closing the current form doesn't require the parameters. DoCmd.Close is sufficient.
3. Indentation is important, and should never be random.
4. If the form's dirty and you Exit Sub, there's no need for the remaining code to be within the Else portion of the If.
5. Although Dims can occur anywhere in a procedure, it's good practice to keep them together at the top.
6. Dates in SQL are a standard format. This is not optional. Your code will explicitly reverse the day and the month as far as SQL is concerned (See Literal DateTimes and Their Delimiters (#)). Notice in the code I posted at #7 that the formats used for lines #11 and #20 are quite different. This is not because I'm a frequent jet-setter across the Atlantic. In truth, line #20 can be used however you want it to show, but line #11 was as it needs to be.
7. In your extra DLookup() line (where you use Replace(..., "%P")) you have omitted the third parameter (strWhere). This may explain why the data shown is unconnected.
8. In that same line, you will need to handle the possibility of the return value being Null.

For the logic you should remember that :

1. DLookup will only find one record that overlaps with the request, but it's possible to have more than one.
2. As your times (those examples stored) seem to include the finish time as well as the start time typically on the hour or half hour, it makes more sense to treat a request as an overlap only when the times are < or >, rather than <= or >= as they are currently.

[Zac Harvey]

I am pleased to say that I seem to have a working product! xD However I would still really like to incorporate the name of the person that booked the room into the message box as this will show the Admin trying to book the room who to talk to about it. Is there any way of doing this? I tried adding in (strWhere) but it still did not bring out the correct data linking to the overlapping record.

[Zac Harvey]

Oh and another thought, I did a little testing and it gave me the idea to show all room bookings for the room that the user is trying to book on that day.

E.g. If the Large Traning Room had already been booked on a day between 9:00-12:00 and 14:00-16:00. The user tried to book for 11:00-15:00. Message box displays "This room has already been booked between 9:00-12:00 and 14:00-16:00. Is this possible? I'm gona go see if I can make it happen in the meantime.

[TheSmileyCoder]

Its quite possible but requires a somewhat different approach using recordsets instead of domain functions.

If you could upload your current working db as a zip instead of a rar i could look at it.

[Zac Harvey]

hmm I see, might not be worth the time tbh then. I'm hoping to wrap this up asap. But see what you think anyway. Much appreciated.

[TheSmileyCoder]

Okay, I have updated your bookings form with an example of how to do it with recordsets in vba. I hope you find it usefull.

I still want to note that you should look at my earlier post about placing the validation in the forms beforeupdate event. At this point, you can still save the faulty booking by closing the form, without clicking the save button.

[NeoPa]

I agree with Smiley that further info (and a more reliable set of returned data in a situation such as this where multiple overlaps are possible) is better done using recordset processing. DLookup() calls can reliably be used for finding the answer, but when it comes to selecting one of multiple answers, they are largely unpredictable.

Error message strings can be built up by looping through matching records and appending data for each record found (as I suspect will be illustrated by his code).

The approach to take is often highly dependent on exactly what is required from the code. This is why it is always so important to have a clear idea of the exact requirements before you start. Hopefully though, all here will have helped you to gain experience of various different options and where they can be used appropriately.

[Zac Harvey]

lol Smiley... "Booked for SEX by SEXMACHINE"? :P I'll have you know that we will not tollerate behavior like that in our workplace.

So I have just tried it out, and the there seems to be a slight problem. It works great if there is actually an overlap, but it wont save a record that doesnt have an overlap? I've attatched a img of the error and emboldened below the highlited code in debug.

'Move to last record to ensure that recordset has been populuated.
            'This is needed because we wish to access the recordcount property
            rsDao.MoveLast
            rsDao.MoveFirst
            If rsDao.RecordCount = 0 Then
                'Booking is ok.
                Exit Sub
 

Also, I did try running the validation from the before_update event after you posted but it didnt seem to trigger.

[Zac Harvey]

Another question: Is the following piece of code meant to be returning all the overlapping bookings in the msg? Because it doesn't seem to be doing so.

                Do While Not rsDao.EOF 'Do until we reach the eof, (End Of File)
                    strMsg = strMsg & vbNewLine & "%D between [%S] and [%E] by '%B'"
                        strMsg = Replace(strMsg, "%D", Format(rsDao!BookingDate, "dd-mmm-yy"))
                        strMsg = Replace(strMsg, "%S", Format(rsDao![StartTime], "HH:mm"))
                        strMsg = Replace(strMsg, "%E", Format(rsDao![EndTime], "HH:mm"))
                        strMsg = Replace(strMsg, "%B", Format(rsDao!BookedBy, "HH:mm"))
 
                    rsDao.MoveNext
                Loop
 

[Zac Harvey]

Aha, this possibly seems to answer my question regarding the error.

http://allenbrowne.com/ser-29.html - Point 3


So the Move methods should come after the if statement? (If rsDao.RecordCount = 0 Then)

[Zac Harvey]

Ah.. maybe not. Cos that would defeat the point right? As you are using the move methods to actually make the count? Thats what I understand from your annotations Smiley.

[NeoPa]

Zac Harvey:
lol Smiley... "Booked for SEX by SEXMACHINE"? :P I'll have you know that we will not tollerate behavior like that in our workplace.

How very tedious and bourgeois! Such intolerance! :-D

Line #3 of your code is highlighted, but no image was attached of the error message. Posting such images is fine, but equally acceptable would be posting the textual contents of them. That's really the only requirement.

[Zac Harvey]

Oops, my mistake.

Run-time error "3021":

No current record

[NeoPa]

Zac Harvey:
So the Move methods should come after the if statement? (If rsDao.RecordCount = 0 Then)

Definitely not. The code checking RecordCount checks for 0 - not >0. In fact, the MoveLast and MoveFirst are probably unnecessary in this case. Read on.

Allen Browne explained the half of the story he dealt with. Let's see if we can give the complete picture for your perspective.

If rsDao.RecordCount > 0 Then

or even

If Not (rsDao.BOF And rsDao.EOF) Then

indicate that the recordset has records (although at this stage the count itself would be unreliable - See point #4 of Allen Browne's article you linked to). If an accurate reflection of the actual count is required (which I do not believe to be the case in this scenario) then Call rsDao.MoveLast is required. Of course, this code shouldn't be executed without first checking that the recordset is empty otherwise it will fail.

It seems then, that accurately determining the count in most recordsets (dbOpenTable is an exception) requires both techniques (Check records exist; Move to last). In this case though, I suspect you simply need the check, as the actual count is irrelevant. I would suggest, due to the confusing nature of what is required, that determining whether or not a recordset has any records should be done using the second of the two methods shown (If Not (rsDao.BOF And rsDao.EOF) Then) rather than the first. Disambiguation is definitely an aim to strive for when writing code unless you are interested in ensuring people don't understand it.

PS. ** Edit **
Having reread and realised I missed something in the code, I would say here that, for the purposes of disambiguation (still very important) I would recommend using the first approach when simply determining if records exist, and the second prior to determining the accurate count of the recordset. They both do fundamentally the same job of course, but the first seems clearer as an indication of what's required. At the end of the day though, how it reads to you the developer is the most important factor here, so make your own choices.

[Zac Harvey]

Ok here's my revised code. It seems to do the job. I know my code is likely not the best writen and ordered but as I mentioned origionaly I am still pretty new to Access programming.

'If form is dirty, before going to new record check current
 
Private Sub cmdSave_Click()
 
    If Me.Dirty Then
        Dim strWhere As String, strMsg As String
            With Me
            strWhere = "(([StartTime]<#%S#) AND " & _
                       "([EndTime]>#%E#) AND " & _
                       "([RoomID]=%R) AND " & _
                       "([BookingDate]=#%D#))"
            strWhere = Replace(strWhere, "%S", Format(.EndTime, "HH:mm:ss"))
            strWhere = Replace(strWhere, "%E", Format(.StartTime, "HH:mm:ss"))
            strWhere = Replace(strWhere, "%R", .RoomID)
            strWhere = Replace(strWhere, "%D", Format(.BookingDate, "mm/dd/yy"))
 
 
            Dim rsDao As DAO.Recordset
            Set rsDao = CurrentDb.OpenRecordset("SELECT * FROM [tblBookings] WHERE " & strWhere, dbOpenDynaset)
 
 
            'Move to last record to ensure that recordset has been populuated.
            'This is needed because we wish to access the recordcount property
 
 
            If rsDao.RecordCount = 0 Then
                'Booking is ok.
                DoCmd.Save
                DoCmd.GoToRecord , , acNewRec
                Exit Sub
            Else
                'Booking is not ok
 
                Do While Not rsDao.EOF 'Do until we reach the eof, (End Of File)
                    strMsg = strMsg & vbNewLine & "%D between [%S] and [%E] by '%B'"
                        strMsg = Replace(strMsg, "%D", Format(rsDao!BookingDate, "dd-mmm-yy"))
                        strMsg = Replace(strMsg, "%S", Format(rsDao![StartTime], "HH:mm"))
                        strMsg = Replace(strMsg, "%E", Format(rsDao![EndTime], "HH:mm"))
                        strMsg = Replace(strMsg, "%B", Format(rsDao!BookedBy, "HH:mm"))
 
                    rsDao.MoveNext
                Loop
 
            End If
 
            'Cancel entry
                Me.Undo
 
            'Inform user
                strMsg = "Sorry, the booking could not be made. The room is allready booked on:" & strMsg
                MsgBox strMsg
 
            'Cleanup
                Set rsDao = Nothing
 
 
            End With
    End If
End Sub
 

[TheSmileyCoder]

@ NeoPa
Thank you for correcting the mistakes I made.

@ Zac
Good to see you got it working. One thing I need to note is that:

Docmd.Save

will save your object (the form in this case) not the record. You can use:

docmd.RunCommand acCmdSaveRecord

to save the record.

[NeoPa]

TheSmileyCoder:
Thank you for correcting the mistakes I made.

They were few enough Smiley :-)

To be honest I had to read through the linked Allen Browne article to get the full understanding myself. It's not like they were obvious or simple errors, but rather understandings of where Access itself lets the developer down.

[Zac Harvey]

Thanks a lot guys, your help has been most appreciated. I shall give mention to the website and yourselves for all the help. I'm almost there, but I've still a few glitches to iron out. I'm hoping you can still find the time to take a look at these. They will follow on from this post.

[Zac Harvey]

Ok, so first problem is on my form which is used to ammend booking details after they have been made. There is a search facility on the form allowing the user to find bookings records easily. However when they enter a search string that doesn't exist the form is returned completely blank and can't be closed. I will attatch it so you can actually try it for yourself rather than show you by pictures.

[*EDIT*] I have solved the above problem. Turns out it was happening because I had set AllowAdditions to No, and as such when the search returned no records it couldn't show a blank one. So I've got round it by allowing additions and putting 'Cancel = true' in the BeforeInsert form event.

Note: I have actually uploaded my full database, so if you wanted to take a look at it as a whole and just tell me what you think in general that would also be a great help.

Cheers

[NeoPa]

I think we can ignore the extra question you've already answered Zac. I've moved the new one to its own thread (Date Validation) though. Please remember for future questions.

I'll also leave the posted db. Generally that's a great deal to ask, as it involves a lot of work for the benefit of a single member, but we can all choose whether or not to respond so I'll leave it in place in case anyone is interested.

[Zac Harvey]

Okay, no problem. I wasn't assuming you'd look. I didn't want you to inspect the code or anything just have a little try of it and see what you thought. But it's no worry. Thanks again Neo and Smiley.

[NeoPa]

No worries Zac. If I do get an idle moment I may give it a quick look. Those idle moments are in pretty short supply just now mind.