473,836 Members | 1,265 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Time Interval Overlapping (MS Access 2003)

28 New Member
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
Mar 31 '11
38 8072
NeoPa
32,584 Recognized Expert Moderator MVP
Zac Harvey:
So the Move methods should come after the if statement? (If rsDao.RecordCou nt = 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.
Expand|Select|Wrap|Line Numbers
  1. If rsDao.RecordCount > 0 Then
or even
Expand|Select|Wrap|Line Numbers
  1. 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.
Apr 19 '11 #31
Zac Harvey
28 New Member
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.

Expand|Select|Wrap|Line Numbers
  1. 'If form is dirty, before going to new record check current
  2.  
  3. Private Sub cmdSave_Click()
  4.  
  5.     If Me.Dirty Then
  6.         Dim strWhere As String, strMsg As String
  7.             With Me
  8.             strWhere = "(([StartTime]<#%S#) AND " & _
  9.                        "([EndTime]>#%E#) AND " & _
  10.                        "([RoomID]=%R) AND " & _
  11.                        "([BookingDate]=#%D#))"
  12.             strWhere = Replace(strWhere, "%S", Format(.EndTime, "HH:mm:ss"))
  13.             strWhere = Replace(strWhere, "%E", Format(.StartTime, "HH:mm:ss"))
  14.             strWhere = Replace(strWhere, "%R", .RoomID)
  15.             strWhere = Replace(strWhere, "%D", Format(.BookingDate, "mm/dd/yy"))
  16.  
  17.  
  18.             Dim rsDao As DAO.Recordset
  19.             Set rsDao = CurrentDb.OpenRecordset("SELECT * FROM [tblBookings] WHERE " & strWhere, dbOpenDynaset)
  20.  
  21.  
  22.             'Move to last record to ensure that recordset has been populuated.
  23.             'This is needed because we wish to access the recordcount property
  24.  
  25.  
  26.             If rsDao.RecordCount = 0 Then
  27.                 'Booking is ok.
  28.                 DoCmd.Save
  29.                 DoCmd.GoToRecord , , acNewRec
  30.                 Exit Sub
  31.             Else
  32.                 'Booking is not ok
  33.  
  34.                 Do While Not rsDao.EOF 'Do until we reach the eof, (End Of File)
  35.                     strMsg = strMsg & vbNewLine & "%D between [%S] and [%E] by '%B'"
  36.                         strMsg = Replace(strMsg, "%D", Format(rsDao!BookingDate, "dd-mmm-yy"))
  37.                         strMsg = Replace(strMsg, "%S", Format(rsDao![StartTime], "HH:mm"))
  38.                         strMsg = Replace(strMsg, "%E", Format(rsDao![EndTime], "HH:mm"))
  39.                         strMsg = Replace(strMsg, "%B", Format(rsDao!BookedBy, "HH:mm"))
  40.  
  41.                     rsDao.MoveNext
  42.                 Loop
  43.  
  44.             End If
  45.  
  46.             'Cancel entry
  47.                 Me.Undo
  48.  
  49.             'Inform user
  50.                 strMsg = "Sorry, the booking could not be made. The room is allready booked on:" & strMsg
  51.                 MsgBox strMsg
  52.  
  53.             'Cleanup
  54.                 Set rsDao = Nothing
  55.  
  56.  
  57.             End With
  58.     End If
  59. End Sub
  60.  
Apr 19 '11 #32
TheSmileyCoder
2,322 Recognized Expert Moderator Top Contributor
@ 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:
Expand|Select|Wrap|Line Numbers
  1. Docmd.Save
will save your object (the form in this case) not the record. You can use:
Expand|Select|Wrap|Line Numbers
  1. docmd.RunCommand acCmdSaveRecord
to save the record.
Apr 19 '11 #33
NeoPa
32,584 Recognized Expert Moderator MVP
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.
Apr 20 '11 #34
Zac Harvey
28 New Member
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.
Apr 20 '11 #35
Zac Harvey
28 New Member
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
Attached Files
File Type: zip RoomBookings.zip (306.4 KB, 192 views)
Apr 20 '11 #36
NeoPa
32,584 Recognized Expert Moderator MVP
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.
Apr 20 '11 #37
Zac Harvey
28 New Member
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.
Apr 21 '11 #38
NeoPa
32,584 Recognized Expert Moderator MVP
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.
Apr 21 '11 #39

Sign in to post your reply or Sign up for a free account.

Similar topics

1
5093
by: Rob | last post by:
Hi, I have a question, when you use a random number generator Random() in a loop to generate say 50000 random numbers; is it any difference if you use different time interval between each loop instead of a fixed amount of time, for example using an exponential time interval instead of a fixed time interval in a loop which generates 50000 random number makes any changes in results? Thanks for any help. Rob
4
2978
by: Andrew Poulos | last post by:
How do I convert a length of time, measured in seconds, into a "point in time" type time interval or what's represented as: time (second,10,2) The format is: PS]] where: y: The number of years (integer, >= 0, not restricted) m: The number of months (integer, >=0, not restricted) d: The number of days (integer, >=0, not restricted)
0
1515
by: peterleeds | last post by:
Could anyone explain why Access 2003 is continuously calculating a continuous form, when it works perfectly in previous versions? On reading previous messages I gather that overlapping controls cause this, but why? I use a hidden control underneath the whole line to highlight it when selected. If I cannot do this in Access 2003, how does one go about getting the
13
2658
by: Noesis Strategy | last post by:
When I ordered my new laptop, Sony didn't offer Access 2003 in its bundles. Recently, I have begun to design Access databases using an copy of Access 2002 from my previous laptop. It works fine, but I would like to have all the office apps on the same version. So I have a few questions: 1) Is the file format the same as 2002? Can 2002 users read 2003 files? 2) What are the major reasons for upgrading to 2002 ?
13
7507
by: Manuel Lopez | last post by:
I have a puzzling form timer problem that I didn't experience prior to Access 2003 (though I'm not sure access 2003 is to blame). Here's the situation: a computer has two access 2003 databases on it, a frontend and a backend. Case 1: If vba code on the frontend updates many rows (360,000) on the backend, a form's timer event (from the frontend) will stop firing until the user gives the form focus. (Note that the update itself always...
10
3059
by: Lauren Wilson | last post by:
Ok I have searched the MS website for info on this. I am totally confused. If I want to deploy an Access 2003 app and allow my users to run it using Access 2003 Runtime, where do I get the Runtime? I just purchased Office 2003 Professional. Is Access 2003 Runtime included with that or not? It APPEARS that the only way I can get Access 2003 Runtime is to
49
3253
by: Mell via AccessMonster.com | last post by:
I created databases on Access 2003 and I want to deploy them to users. My code was also done using 2003. If they have Ms Access 2000 or higher, will they be able to use these dbs with all code, etc? Please explain -- Message posted via http://www.accessmonster.com
2
1487
by: Ducknut | last post by:
Hi all, I was thinking that an expert like FishVal might be interested in solving this one (based on his name). I have several fish tagged with radio telemetry tags. These tags send a signal to a telemetry receiver, which I download and import into an Access 2003 database. So I have a table with the following columns: an AutoNumber, Station, Datetime, and Code (the tag number). Here is some sample data: ID Station Datetime ...
2
5897
by: Satheesh V | last post by:
hi, I use DAO recordset to query database tables of MSAccess. I open the recordset and miss/forget to close it. Can anyone tell me the maximum number of recordsets that can be open at a time (on querying Access 2003 and Access 2007 table) after which it gives an error saying "no more tables can be open"? Thanks, Satheesh
0
10829
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10535
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10582
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7778
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6976
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5645
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5815
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4446
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4005
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.