Full Version: preventing duplicate data e.g. a time in appointments
UtterAccess Forums > Microsoft® Access > Access Forms
Pages: 1, 2
scuzz
Hello people,
I've got a small fairly easy question here! I've got a combobox which provides the user with a selection of times from 09:00 through to 19:00 in half hour intervals.
The appointments are displayed on a continuous form which shows all the appointments for that particular day.
My question is, what code can i write to prevent my user from double booking a time slot. In other words, how to i prevent my user from creating one appointment at 10:00 and then trying to create another at 10:00??
Thanks for any help you can offer.
My combobox is called Time, and it is populated from a table called Timeslots.
Fletch
It sounds like in your case all you want is to prevent an appointment from starting at the same time since you're assuming all appointments are of the same length (30 minutes) and only start on the half hour. In that case, just make the date/time field of the appointment a unique field. Alternatively, you could query to see if a record exists with the given time slot prior to saving it. If you have one field for the time slot and another for the actual date, then just make the combination of the date field and time slot field a unique index.
If you think you might ever have to deal with an appointment starting at any given time and lasting any given amount of time, then you need to take a different approach. I recently had a similar question posed to me and I came up with this logic. When you want to create a new appointment, reservation, meeting, whatever, existing meetings have a designated start/stop time. If dtmPS represents the date/time of the proposed start time of a new appointment and dtmPE is the proposed ending time, then in your tblAppointments with fields dtmStart and dtmEnd, you could query
Select. . . Where dtmPS < dtmEnd And dtmPE > dtmStart
should select all conflicting appointments. If nothing is returned, then you know you're safe to schedule an appointment for the proposed times.
If instead of saving the end time, you save the duration, then you can simply do an on-the-fly calculation to calcuate the end time (probably using the DateAdd function), or using the proposed times to calculate a proposed duration.
HTH frown.gif
Girn13
I'll assume that you've got an appointment table somewhere showing dates, appointment time and some customer identification. Then the source of your Time combobox becomes a left join between timeslots and appointments where the appointment date is the date you're interested in and the customer name is nulls. This will return only those timeslots that are vacant on a particular date.
scuzz
aarrgh, too many big words!! lol
abrandt, i read your post, three times and im still confused as to which language you are using! i think i have a combination of idiotisis and readers block! haha, oh well, i've attached a copy of my calendar. It should give you the jist of what im doing. Click "Book Appointment" when it opens and you should be able to follow from there.
Ideally i would like to be able to select a time, and then a duration, and with this data prevent any appointments from being made which would conflit with each other. For example book an appointment at 09:00 for 2hours, thus stopping appointments being made before 11:00!! the person to help me do that earns himself a cyber beer!
Cheers!
aoh
Scuzz,
The attached module is adapted from something I got here that checked date overlaps.
It will take a start time and end time and check against existing appointments - you'll obviously need to change the table and field references, but I used this to stop a dentist double-booking himself.
Enjoy.
scuzz
Brilliant! I'll check it out now! It'll probably invole me adding a few more invisible fields but if it gets the job done im not gona complain! Thanks again!
aoh
No problem - hope you get it working. If you have any problems, post them up and I'll check back tomorrow.
nne
scuzz
CODE
Function TimeOverlap(FDId As String, FDate As Date, FStartTime As Date, FEndTime As Date) As Boolean
nbsp;   Dim rst As Object
    Dim strSQL As String
    Dim FTime3 As Date
    strSQL = "Select AStartTime, AEndTime from tblAppointment " _
        & "Where DId = " & FDId & " And ADate = #" & Format(FDate, "long date") _
        & " Order by AStartTime"
    Set rst = CurrentDb.OpenRecordset(strSQL)
    If Not rst.EOF Then
        Do While Not rst.EOF
            FTime3 = FStartTime
            Do While FTime3 < FEndTime
                If FTime3 >= rst(0) And FTime3 < rst(1) Then
                    TimeOverlap = True
                    Exit Function
                Else
                    FTime3 = DateAdd("n", 1, FTime3)
                End If
            Loop
            rst.MoveNext
        Loop
    End If
    
    TimeOverlap = False
    rst.Close
    Set rst = Nothing
End Function

This is the code which Anne kindly provided. I'm not too sure what the DId and FDId stands for? I'm presuming it's something like date and finished date??
CODE
& "Where DId = " & FDId & " And ADate = #" & Format(FDate, "long date")
aoh
Sorry - DId is the Dentist Id - if there's more than one in a practise, they have independent diaries, but it's only used in the Select. You could leave this out - and just use Date, start time and end time.
scuzz
aaah right, thanks for that! I'll do my best to adapt it to my needs. How would i get this to work then? would i call the funtion in the on_current event of my form, or in the afterupdate event of a control on my form? I hardly ever deal with functions, so its quite new to me!
Fletch
Well, guess I'm a bit late in getting back to you so you may have gotten an alternate solution to work. Of course, sorry for confusing you, but the way you worded your initial question led me to believe you had structured things a bit differently than they actually were when I looked in your database.

That being said, this might be a possible solution:

1) In the AppointmentTimes table, change Time to a date/time value. To be consistent in format, you might want to change the format to "Short Time" and on the AppointmentTimes sub form, you might also need to change the Time control's format to "Short Time"

2) For the subform AppointmentTimes, create an event procedure for the form's BeforeUpdate procedure. In that event, you can place this code
CODE
If Not IsNull(DLookup("DateId", "AppointmentTimes", "DateID = #" & Me.DateId & "# And #" & _
nbsp;                     Me.Time & "# < CDate(Format(DateAdd(""n"", [LessonLength]*60, [Time]), " & _
                      """hh:nn"")) And #" & CDate(Format(DateAdd("n", Me.LessonLength*60, Me.Time), _
                      "hh:nn")) & "# > [Time]")) Then
    Cancel = True
    MsgBox "That time slot has already been taken!"
End If
I think that will give you an error message if you select a slot that has already been taken (at least it seemed to on my end) and prevent the record from being saved.

Since your lesson lengths are in units of hours but you allow non-integer values, note that in the Date Add function I had to add minutes and use a 60 minutes to an hour conversion.

Now, that being said, this code will generate other unanticipated errors with existing code you have, so be forewarned. For example, in the LessonLength_AfterUpdate procedure, the code

Me.Parent!CloseCalendar.SetFocus

Will generate an error if you try to change the length and that change ends up causing the record to conflict with an existing record. So, you'll need to decide how to handle that. Also, in the Time_AfterUpdate procedure, the line

Me.AllowAdditions = False

will also give you an error if the record update doesn't happen due to an time conflict. So, again, you'll have to address that.

HAs a side note, I recommend changing the field named Time to something else since Time is a reserved word in VBA (it returns the current system time).

If the above doesn't work for you (i.e. I forgot to mention something else I changed, let me know and I'll update the database as I have it modified).

frown.gif

EDIT: Just noticed one other problem. If you try to change an existing record to a different time, if that new time conflicts with the old time, this will still cause a conflict. I think this can be solved, however, by adding a unique id to each appointment--will try and verify for you.
Edited by: babrandt on Thu Oct 28 14:17:56 EDT 2004.
scuzz
wow, never let it be said babrandt that you don't put 110% effort into helping someone out!
Appologies for not making myself clearer in the first instance, and i will impliment your suggestions right away! Thankyou very much!
Fletch
Hey, if you get this before you give it a try, just try this database as it includes the update that should allow you to change the current appointment without conflicting with itself. I commented out the 2 lines of code that I know cause a problem that I mentioned in the last post.
scuzz
Hi babrandt,
I've had a tinker with your updated version of the database, and i found a few difficulties. I found that making an appointment with the same time wasnt stopped when the time field was updated and that it was only when i moved the focus that the error message was displayed, then it was almost impossible to delete the appointment to resolve the issue. Should i move the code to the after-update event of the time field?
scuzz
Alrighty then,
think i've found the solution. I placed the code into the before update event of the time control, and placed me.undo after the cancel=true. All seems to work.
Babrandt, you are the winner of the cyber beer! thanks very much for your help! I owe you alot more than one!
Cheers!
Fletch
Right, the code doesn't fire until the form attemtps to update the entire record. At that point, to resolve the issue, you'd press Esc to undo the changes to the record if you couldn't find a better time.
However, sure, another option is to move the code to the after update event of the time combo box AND the after update event for the Lesson Length combo box since you need to check any time either one of those changes. Additionally, since it's possible on a new record that you can either edit the lesson length of the time first, the other one will be null at that point. So, if either one is null, you have to prevent the look up as in
If IsNull(Me.Time) Or IsNull(Me.LessonLength) Then
Exit Sub
End If
before each check in the BeforeUpdate event for the combo box.
Another option is to create a blank UNBOUND form. When you click Add Appointment, this form opens and allows you to input the data. Then, the user clicks Cancel and the form closes and nothing happens, or, the user clicks OK. If OK is pressed, then you do the same kind of check. If there's a conflict, you keep the form open and allow the user to make changes. If there's no conflict, you save the update, requery the subform showing appointments, and then close the pop up form.
No doubt it needs lots more work, but take a look at this attachment--I removed the record source from the Calendar form as I didn't see how that was being used--maybe it is and you need to add it back in. The calendar is always visible. You can edit existing appointments. To add a new appointment, select the date on the calendar. Then, click book appointment. A pop up form appears and you enter the data (so you never have to mess with AllowAdditions). Click OK or cancel as appropriate. The new entry now appears and can be edited directly. A lot of code needs cleaning up (such as making everything visible/invisible as in this approach you probably want the calendar always visible). Also, the pop up form needs to be able to add other data such as pupil, but hopefully you get the point.
Anyway, by all means try aoh's suggestion or another path--just wanted to illustrate one option.
Wooops--just got a message saying you posted again while I was writing--anyway, will still post this in case it helps.
At any rate, thanks for the cyber beer! o!
scuzz
Hi babrandt,
I've had a go at putting in a pupil selector, but i cant quite modify the code in the onClick event of the OK button. Could you perhaps take a look at help me out a little more?
Thanks!
aoh
Scuzz
I was knackered last night - otherwise I would have hung around a bit longer.
Basically, you need to call this whan you have all the data you need, but see below for a caveat.
To call it, I use this in the afterupdate of StartTime (because I calculate the end time):
If TimeOverlap(Me.DId, Me.ADate, Me.AStartTime, Me.AEndTime) Then
' there is an overlap - tell them
Else
' it's ok to use these times
So if your users put in date, then start time, then end time, call it then. You'll have to cater for the user changing the date or starttime afterthe check has been run.
One thing I didn't have to worry about was multiple users - if it's a multi-user base, you'll want to do this just before the save to prevent two overlapping appointments being created at the same time. If this is the case, you might define a global boolean which is originally set to No. Anytime you call the function successfully, set it to yes and then in the afterupdate event of each of the necessary fields set it to No again - then, just before the save, you can check the flag, save the record and reset the flag to No.
Oshould be around most of today, so let me know how you get on.
Anne
PS Don't call the module the same name as the function - Access doesn't like this at all.
scuzz
Anne,
hankyou for your reply! I too was knackered last night but managed to stay awak just long enough to try out babrandt's db. I'm sorry to have put you to more trouble, but im going to use babrandt's suggestion as it works, and it can understand it a bit better, although with time i'm sure i would have figured out your suggestion aswell. I have to account for everything that is in this project of mine so if i dont understand it properly the examiners are going to smell a rat! If you look at my previous post you will see there is an attachment, this is almost exactly what i was attempting to do in the first place but never thought possible!
Thankyou again for the time you've spent helping me out. I only hope babrandt comes back online soon as i need to crack on with this. My deadline is monday! I'm not sure where babrandt lives so i have no idea what time zone he is in... shrug.gif
I hope it's not too wet in Dublin, it's grey sky as far as the eye can see over here!! :(
aoh
No worries Scuzz - I missed a whole page of this thread this morning - guess I wasn't quite awake. If you get it working (sorry, WHEN you get it working), would you post it up - I'm always interested in better ways of doing things, as a lot of what I end up with is trial and error.
Good Luck with the deadline - and it's a really soft day here - Irish for cold, grey, wet, poxy weather
thumbdn.gif
Fletch
Okay, no worries, I'm back. wink.gif
I used to have a location listed of where I live, but for some reason after you made the comment about not knowing where I live I realized that it had gone away. Anyway, I'm in northern Virginia (USA), so I'm 4 hours behind GMT/UT, though it'll be 5 hours after this weekend as we revert to standard time.
So, anyway, you have until Monday to complete this--ah, man, talk about having the luxury of time as you still have 2 whole days!!! laugh.gif
Anyway, I think the only line you need to change is the line that actually inserts the values into the table. I believe it should look like
CurrentDb.Execute "INSERT INTO AppointmentTimes ([DateID], [Time], [LessonLength], [RecID]) " & _
"Values (#" & OpenArgs & "#,#" & Me.cboTime.Value & "#," & _
Me.cboLessonLength.Value & "," & Me.cboPupilPicker.Value & ");"
At at least, that seems to insert the pupil's RecID value into the AppointmentTimes table on my end with no problem.
Also, as a side note, it appears that the details is a calculated field. So, I would recommend setting the control source for the Details textbox to
=[RecID].Column(2) & ", " & [RecID].Column(3) & ", " & [RecID].Column(4) & ", " & [RecID].Column(5)
and get rid of the Details field in the AppointmentTimes table. Then, you can get rid of the line
Me![Details] = (([RecID].Column(2)) & ", " & ([RecID].Column(3)) & ", " & ([RecID].Column(4)) & ", " & ([RecID].Column(5)))
in the after update event for the RecID combo box. You may also want to get rid of the code that hides the calendar in that event as well.
Ben
scuzz
Brilliant stuff mate, i'll check it out as soon as i've been to the shops!!
scuzz
Also, as a side note, it appears that the details is a calculated field. So, I would recommend setting the control source for the Details textbox to
=[RecID].Column(2) & ", " & [RecID].Column(3) & ", " & [RecID].Column(4) & ", " & [RecID].Column(5)
And get rid of the Details field in the AppointmentTimes table. Then, you can get rid of the line
Me![Details] = (([RecID].Column(2)) & ", " & ([RecID].Column(3)) & ", " & ([RecID].Column(4)) & ", " & ([RecID].Column(5)))
I'll keep that in mind, but i'm not sure whether it would interfere with the rest of the db. I'm at the point now where its like a house of cards, im scared to alter one thing incase the whole thing comes tumbling down. But i'll certainly give it a go, and if it doesnt work then ho hum! Thanks again!
Fletch
Sure, good luck. Let me know if you have any further issues. frown.gif
scuzz
I've managed to incorporate all your suggestions and alterations with very little difficulty babrandt! thanks so much. The one last thing i would like to do is re-position the booking form when it opens. i've tried using the grid X and grid Y options, but it appeared to have no effect. How can i re-position it?
Fletch
Look up the
oCmd.MoveSize
function in the help files. You can move or resize or do both with that command.
For example,
DoCmd.MoveSize 0, 0
should move it to the upper left hand corner of the screen.
scuzz
Fantastic,
I'll give it a go. Once again i am endebted to you. If i wasn't an atheist i'd defintately make you my god! lol
Fletch
ohyeah.gif
ol!
scuzz
Ok i've researched the movesize method. However i don't know where to put the code.
Fletch
Just figure out what event you want to trigger the code. More than likely, it sounds like you want it when the form opens. Therefore, use the form's load event. frown.gif
scuzz
I'm having a really strange problem with my VB now....its almost like its trying to auto check the syntax before i get a change to type things. For example, when i write docmd. you would then expect a dropdown list to appear, but i get a quick glimpse before it disappears and the whole line turns red!?!? whats going on!?!
Fletch
I've had that happen to me before when I have a form open with a timer event. While I'm typing code, the timer event fires and and essentially acts like you typed an enter key on the current line you're typing, which often results in an error or other problems. Bottom line, ensure all forms are either closed or in design view. Also, if you have a form open that's modal, I think it may be the case that you can't type VB at all--again, make sure all forms are closed and/or in design view. Hopefully that's it! frown.gif
scuzz
I thought it might have something to do with that! Well i put the following code into the form's open event and im given an error message saying that there is a syntax error:
ocmd.MoveSize([1701], [3402])
Although, i cant see where i've boo-boo'd!
Fletch
try
oCmd.MoveSize 1701, 3402
(no parentheses, no brackets)
scuzz
ooo dam* ur good! That was possibly the only way i didnt try it!! The autohelp isn't much help when it shows the figures in parentheses! Cheers again!
Fletch
So here's the deal--VB/VBA differentiates between subs (procedures that don't return values) and functions (procedures that do return values). If you're calling a sub, don't surround the arguments with parentheses. If it's a function and you are utilizing the return value, do surround the arguments with parentheses.
hisIsASubCall argument1, "arg2"
x = ThisIsAFunctionCall(argument1, "arg2")
ThisIsAFunctionCall argument1, "arg2" 'notice how I'm not using the return value so no parentheses
The brackets really aren't used in VBA, except they can be used to refer to controls, but generally avoid the brackets in VBA, but use them in SQL.
scuzz
talking of calling arguments.....i saw a post a long time ago but i can't seem to find it, (am i the only person who finds the search engine on this site a little less than helpful sometimes) on how to call a procedure that belongs to another form. How would one do this?? i think i'll start a new topic for this one though!!
aoh
Hey Scuzz,
It's half past five on Friday - and you're still working? I'm off down the pub - I'll think of you when I have that big glass of the black stuff in front of me.
Anne
scuzz
Hahaha,
My parent have been saying the same thing for the past three months!! whilst you're at the pub see if you can find a life for me,... i've mislaid mine! hehe, nah im off to a halloween party later, (its actually an excuse for a [censored] up, no-one is coming in facny dress. I may go along with a name sticker which says "The Devil", that's the extent of the effort im willing to put in!)
Fletch
If you make a procedure public, then it is available to other code modules. However, if it's in a form code module, then I believe for it to be called the form must be open AND in normal/form view. Then, it would look something like
orms("Name of form").NameOfFunctionOrSub
Anyway, hope you, scuzz, and Anne enjoy yourselves at the pub. laugh.gif I've still got a few more hours of work left. :(
scuzz
Hey Anne,
Here is the final (slightly hacked down squashed and pummled) version of the appointment maker I promised to post for you. Hope you find it useful!
aoh
Scuzz,
Thanks for sharing - it's certainly cleaner and quicker than my version.
Anne
scuzz
ok, little problem displaying the date with a day aswell.
would like the day and date to be displayed in the header on the Calendar form, and also in the BookAppointment form where you can choose the time and pupil to take the lesson! Thanks very much if you figure it out!
Fletch
Didn't see any new attachment based on our PMs, so just went with the last attachment you gave. Made some changes so be careful about whether or not you want to use the whole thing.
ome notes, though, if you don't use the whole thing
-You had RecID in AppointmentTimes as a Byte. So, as soon as you have a student whose unique ID > 255, you'd be in trouble. Changed it to a long to match the data type in Pupil Details.
-Modified the formats for the date/time and the way its displayed and when the subform is requeried.
-The BookAppointment form--had to adjust the length of the label to fully display the full date--so, you will need to adjust to align everything back up or change the format if you don't want it in this format on this form.
-Got rid of a lot of the code to hide and show the calendar and associated controls. Other than the buttons to view and hide it, not sure you need/want such code. But, if you do, in most cases you can just call the sub that shows or hides it instead of copying all the code over again.
-In the case of a conflict, I threw in a little extra note about who the conflicting appointment is with, at what time, and for how long
Probably some other stuff. Anyway, think it's working--let me know if it's not what you had in mind. frown.gif
scuzz
Hi babrandt,
I've encorporated your new version of the db into my current db. I have to say i was very impressed when i looked at the code and saw that you intended to show who's appointment clashed with the current appointment being made. However when i went to test it, i managed to make several conflicting appointments. I dont know how or where is went wrong...
Fletch
Can you give me some sample data that is allowed yet creates a conflict? The data that I'm testing seems to successfully detect conflicts. In other words, can you tell me step-by-step what data to add as in
. Add an appointment for 0800 on 1-Nov-04 for 2.5 hours.
2. Add an appointment for 1600 on 1-Nov-04 for 0.5 hours.
3. Try adding an appointment for 0900 on 1-Nov-04 for 1 hour (should conflict but doesn't generate warning message).
scuzz
Sure,
tw, i re-downloaded your attachment thinking that i may have ommited something when i was exporting over to my current db and found that it too was allowing conflicts.
Ok, well with the version i have...
1. Book an appointment on Tuesday, 2 November 2004 at 11:30 for 3 hours
2. Book an appointment on Tuesday, 2 November 2004 13:30 for 1 hour.
This should cause a conflict as the 3hour lesson has not yet finished. I've tried numerous combinations and they are all allowed.
Fletch
Okay, that's weird. When I run it here I get the conflict message as expected! So, let's think. If we're running the same code, then what could be different? The only thing that comes to mind is possibly the regional settings on our computers being different and thus perhaps a problem with the way dates are handled. In the code that checks for conflicts there is not explicit formatting of date values, so it is a possibility.
On the cmdOK_Click procedure, there is a line that reads
varAppointmentID = DLookup( . . . and runs on for a couple of lines.
Can you add this line either right before or right after it
CODE
Debug.Print "DateID = #" & CDate(OpenArgs) & "# And #" & _
                      Me.cboTime.Value & "# < CDate(Format(DateAdd(""n"", " & _
                      "[LessonLength]*60, [Time]), " & _
                      """hh:nn"")) And #" & CDate(Format(DateAdd("n", _
                      Me.cboLessonLength.Value * 60, Me.cboTime), "hh:nn")) & "# > [Time]"
Then, follow these steps
1) Delete all appointments for 2-Nov-04
2) Add the 2 appointments you previously described for 2 Nov
3) Go to the immediate window (press Ctrl+G)
4) Compare what's in the immediate window to what I get when I do it
When I do that, I get the following
DateID = #11/2/2004# And #11:30:00 AM# < CDate(Format(DateAdd("n", [LessonLength]*60, [Time]), "hh:nn")) And #2:30:00 PM# > [Time]
DateID = #11/2/2004# And #1:30:00 PM# < CDate(Format(DateAdd("n", [LessonLength]*60, [Time]), "hh:nn")) And #2:30:00 PM# > [Time]
Do you have the same thing? If not, please post what you've got. If it is the same (or even if it's not), then let's try this for kicks. Change the assignment of varAppointmentID to this
CODE
varAppointmentID = DLookup("lngAppointmentID", "AppointmentTimes", "DateID = #" & OpenArgs & _
                      "# And #" & Format(Me.cboTime.Value, "hh:nn") & _
                      "# < DateAdd(""n"", [LessonLength]*60, [Time]) " & _
                      "And #" & Format(DateAdd("n", _
                      Me.cboLessonLength.Value * 60, Me.cboTime), "hh:nn") & _
                      "# > [Time]")
Of course, if you have any other thoughts as to why the exact same code is working differently for us, clue me in! wink.gif
scuzz
Hi babrandt,
did as you said and i got this:
CODE
DateID = #02/11/2004# And #11:30:00# < CDate(Format(DateAdd("n", [LessonLength]*60, [Time]), "hh:nn")) And #14:30:00# > [Time]
DateID = #02/11/2004# And #13:30:00# < CDate(Format(DateAdd("n", [LessonLength]*60, [Time]), "hh:nn")) And #14:30:00# > [Time]

Oalso changed the assignment of the varAppointmentID to the code you suggested and was greeted with an error saying:
"Syntax error in date in query expression 'DateID = ## And #11:30# < DateAdd("n", [LessonLength]*60, [Time]) And #14:30# > [Time]"
However, on attempting again, it sorted itself out and began to work, displaying the error message as expected. I noticed though that our dates are displayed differently. Mine is 02/11/2004 whereas yours is 11/02/2004. However that does not seem to be causing a problem now. Very curious as to why its working all of a sudden!?!? dazed.gif
Thanks for the help, i'm sure i'll find a problem again because i've boo-boo'd somewhere else, but i think i shall burden some other poor sod with that one!
scuzz
oh i know what i forgot to mention, i found that by changing the duration of the lesson lenght AFTER the appointment was made, no error message was displayed even though it would have caused conflicts, the same is true with the time field.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.