Danny,
The trick is to compare the Start and End times of the new record
with the start and end times of each existing record, if the new
start time is less than the existing record end time AND the new end
time is greater than the existing record start time then the time
periods must overlap.
To try it out paste the code below into a form code module and
change the table and field names as appropriate. Call it with the new
start and end time like this :-
Dim vID As Long
vID = TaskCheck(txtStartTime, txtEndTime)
If vID > 0 then
MsgBox "Times overlap"
Exit Sub
End If
'Add new record to table here.
If the time periods overlap the function returns the ID of the record
which it overlaps, you can then use this information to show
the user which existing record already exists for that time.
The way I normally do it is to create an unbound form which has
controls such as Text and Combo boxes to enter the information. I have
an 'OK' button which, when the user has entered the required start and
end times and any other info, he clicks which does the above test and
then adds a new record to the table or shows an appropriate error
message. I think, as it is a bound form, you could also put the code
above into the BeforeInsert event of the form and then set the Cancel
= True if it returns a value greater than 0.
--------------------------------------------------------------------------------------
Public Function TaskCheck(vStart As Date, vEnd As Date) As Long
'Checks if task period overlaps existing task
'Entry vStart = Start date of new task
' vEnd = End date of new task
'Exit TaskCheck= ID of existing task if new task overlaps or 0 if
no overlap
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM Resource WHERE "
_
& "DateValue([Start Date]) = #" & Format(vStart, "yyyy/m/d") &
"#")
Do Until rst.EOF
If vStart < rst![End Date] And vEnd > rst![Start Date] Then
TaskCheck = rst!ID
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Function
--------------------------------------------------------------------------------------
(Watch out for word wrapping when you paste the code).
Incidentally, I suggest you change your field names (if you can) to
not have any spaces in, i.e. StartDate instead of Start Date, it will
save you a lot of trouble later.
HTH
Peter Hibbs.
Hi All,
I have created a table say "Resource" table.
In this table, I have "ID, Name, Task Desc, Start Date, End Date, Status".
The objective for this is to make sure task assigned to the resource
correctly and there should not be any overlap of different task assign to the
same resource.
Meaning if the first task was assigned to John, start date = 1/1/2008 and
end date = 2/1/2008, the second task should not fall within 1/1/2008 to
2/1/2008.
I have created a form call "Resource Assignment" that directly attach to
this "Resource" table for data entry. How can I check the data input before
it update into the database. I'm looking for the easier way to get this done.
Appreciated it your advise..
