{"id":397,"date":"2016-07-12T19:21:43","date_gmt":"2016-07-12T23:21:43","guid":{"rendered":"http:\/\/access-diva.com\/blog\/?p=397"},"modified":"2016-07-13T02:51:34","modified_gmt":"2016-07-13T06:51:34","slug":"appointment-collision-check","status":"publish","type":"post","link":"https:\/\/access-diva.com\/blog\/?p=397","title":{"rendered":"Appointment Collision Check"},"content":{"rendered":"

You’ve created a database for scheduling appointments, one little problem when entering these appointments you can’t tell if it clashes with another appointment (especially, if you are not the only one entering).\u00a0 You could use a query<\/a> but I always thought this would be handled better in a Module besides, I had an old database from 2002 (sorry, site no longer up so I can’t post a link)<\/span> that was already part of the way there.\u00a0 So, this looked like a good time to *make it work*!\u00a0 Let’s get started…<\/p>\n

My parameters:<\/h5>\n

(In this example I am using the below table, you will need to change the names here and in the Module to match yours.)<\/span><\/em><\/p>\n

tblActivities
\naActivityID (PK, Autonumber)
\naAssociateID (FK related to tblAssociateProfile, Number, Long Integer)
\naDate (Date\/Time, Short Date [ No need to use Input Mask at Table Level ])
\naStartTime (Date\/Time, Medium Time [ Input Mask: 99:00\\ >LL;0;_ <\/strong>])
\naEndTime (Date\/Time, Medium Time [ Input Mask: 99:00\\ >LL;0;_ <\/strong>])
\netc…<\/p>\n

Module (Named: modCollisionCheck)…<\/h6>\n

You can incorporate into an already existing Module but for ease\u00a0you can copy\/paste the below into a new Module.<\/p>\n

Option Compare Database\r\nOption Explicit\r\n\r\nPublic pubActivityID As Long\r\n\r\nPublic Function getActivityID()\r\n    getActivityID = pubActivityID\r\nEnd Function\r\n\r\n\r\nFunction fCollisionCheck(dteDate As Date, stStartTime As Date, etEndTime As Date, lngAssociateID As Long)\r\n'7.11.2016 rtw modified\r\n'http:\/\/www.access-diva.com\/blog\/p=397\r\n    \r\n    Dim dbDuration As Double\r\n    Dim se As Date 'Starting time of appointments already made (in table)\r\n    Dim ee As Date 'Ending time of appointments already made (in table)\r\n    Dim blCollision As Boolean\r\n    Dim db As DAO.Database\r\n    Dim rs As DAO.Recordset\r\n    Dim frm As Form\r\n    \r\n    Set frm = Screen.ActiveForm\r\n    Set db = CurrentDb\r\n    Set rs = db.OpenRecordset("tblActivities", dbOpenDynaset)\r\n    \r\n    blCollision = False\r\n    frm.lblCollisionCheck.Caption = " "\r\n    dbDuration = etEndTime - stStartTime\r\n    \r\n        With rs\r\n          Do Until rs.EOF\r\n          'First check if the appointment date on the form is the same as the record in the table.\r\n          'The recordset could be filtered at this point thus avoiding\r\n          'to have to loop through all the records in the Appointment table.\r\n          If !aDate = dteDate And !aAssociateID = lngAssociateID Then\r\n              se = !aStartTime\r\n              ee = !aStartTime + dbDuration\r\n            'Lembit Soobik's nifty formula for checking collisions\r\n            If stStartTime < ee And etEndTime > se And pubActivityID <> !aActivityID Then\r\n                blCollision = True\r\n                frm.lblCollisionCheck.Caption = "Collision"\r\n                'MsgBox "Collision with Appointment ID: " & !aActivityID & vbCrLf _\r\n                & "Starting at: " & se & vbCrLf _\r\n                & "Ending at:  " & ee, vbExclamation, "Collision Check"\r\n            End If\r\n          End If\r\n            .MoveNext\r\n          Loop\r\n            rs.Close\r\n            Set rs = Nothing\r\n        End With\r\n    \r\n    db.Close\r\n    Set db = Nothing\r\n        \r\n        If Not blCollision Then\r\n          frm.lblCollisionCheck.Caption = " "\r\n          'MsgBox "No collisions encountered", vbInformation, "You made it"\r\n        End If\r\n\r\nEnd Function\r\n<\/pre>\n
Form…<\/h6>\n
Place Label, lblCollisionCheck<\/strong>,\u00a0on your Form, in this example it’s above the Command Buttons.\u00a0 You will need<\/strong> to place one\u00a0on your Form\u00a0use that potion of the code.\u00a0 However, if you like you can comment out those lines and use the lines below that are pop-up messages.\u00a0 (I personally like the Label as no extra clicking required by the End User.)<\/p>\n
To use the Module above the Form must be set to Pop-Up = No<\/strong>. If you want the Form to be a Pop-Up in the Module above you must change this…<\/p>\n
Set frm = Screen.ActiveForm<\/pre>\n
to<\/p>\n
Set frm = Forms![YOUR FORM NAME]<\/pre>\n
Side Note<\/strong>: If you want to use a Main Form\\Subform set up then the lblCollisionCheck<\/strong> must be on the Main Form, all other Controls can be on the Subform.\u00a0 Since we are reading the Recordsource of the Subform no need to *worry about* the other Controls.<\/p>\n
In the After_Update<\/strong> event of txtStartTime<\/strong> place (note the name of the Controls):<\/p>\n
    If Not IsNull(Me.txtEndTime) Then\r\n            Call fCollisionCheck(Me.txtDate, Me.txtStartTime, Me.txtEndTime, Me.cboAssociateID)\r\n    End If\r\n<\/pre>\n
In the After_Update<\/strong> event of txtEndTime<\/strong> place (note the name of the Controls):<\/p>\n
    pubActivityID = Me.txtActivityID\r\n    \r\n    Call fCollisionCheck(Me.txtDate, Me.txtStartTime, Me.txtEndTime, Me.cboAssociateID)\r\n<\/pre>\n
In the Lost_Focus<\/strong> event of both the txtStartTime<\/strong> and txtEndTime<\/strong> place (I like the extra Save.):<\/p>\n
DoCmd.RunCommand acCmdSaveRecord<\/pre>\n
In the Forms On_Close<\/strong> event (We want to remember to drop the Public Variable when closing the Form\u00a0in case we want to use it\u00a0elsewhere.):<\/p>\n
pubActivityID = Empty<\/pre>\n
\"Appointments\"<\/p>\n
So, if you enter an appointment that conflicts with another one (See the message?)…<\/p>\n
\"appointmentsCollision\"
\nNow, just change the time and message, Collision, will go away provided there still isn’t one!<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
You’ve created a database for scheduling appointments, one little problem when entering these appointments you can’t tell if it clashes with another appointment (especially, if you are not the only one entering). You could use a query but I always thought this would be handled better in a Module besides, I had an old database […]<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,71],"tags":[93,20,76],"class_list":["post-397","post","type-post","status-publish","format-standard","hentry","category-access-tips","category-forms","tag-collision-check","tag-forms","tag-vba","odd"],"_links":{"self":[{"href":"https:\/\/access-diva.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/397","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/access-diva.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/access-diva.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/access-diva.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/access-diva.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=397"}],"version-history":[{"count":15,"href":"https:\/\/access-diva.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/397\/revisions"}],"predecessor-version":[{"id":414,"href":"https:\/\/access-diva.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/397\/revisions\/414"}],"wp:attachment":[{"href":"https:\/\/access-diva.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=397"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/access-diva.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=397"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/access-diva.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=397"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}