Time Interval Overlapping (MS Access 2003)

[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.ra r

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
            [b]rsDao.MoveLast[/b]
            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.RecordCou nt = 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