I think you're working with the code from the article that compares every
record against every other record to find the clashes. For your case, you
only need to check if there is any record that matches the one being
entered.
Something like this (untested aircode):
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String
Const conJetDate = "\#mm\/dd\/yyyy\#"
If IsNull(Me.[Start]) Or IsNull(Me.[End Date]) Or _
IsNull(Me.[TechID]) Or IsNull(Me.EventID) Then
Cancel = True
MsgBox = "Both dates, TechID, and EventID required."
Else
strWhere = "([Start] < " & Format(Me.[End Date], conJetDate) & _
") AND (" & Format(Me.Start, conJetDate) & " < [End Date]) AND
(TechID = " & _
Me.TechID & ") AND (EventID = " & Me.EventID & ")"
If Not Me.NewRecord Then 'Doesn't clash with itself.
strWhere = strWhere & " AND ([ScheduleID] <> " & Me.[ScheduleID]
& ")"
End If
varResult = DLookup("[ScheduleID]", "Schedule", strWhere)
If Not IsNull(varResult) Then
strMsg = "Clash with ScheduleID " & varResult & vbCrLf & _
"Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2) <> vbYes Then
Cancel = True
End If
End If
End If
End Sub
That assumes that the primarykey is ScheduleID, and fields TechID, EventID,
and ScheduleID are both Number type fields. Needs extra quotes if they are
Text fields.
If that doesn't work, add:
Debug.Print strWhere
Then mock up a query, switch it to SQL View (View menu, in query design),
and compare the WHERE clause with what is printed in the Debug window
(Ctrl+G) when the code runs.
General help on DLookup():
http://members.iinet.net.au/~allenbrowne/casu-07.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Wakequest said:
I seem to be stuck.
I don't understand how I am supposed to do this. My query loos like this
NoClash: ([Schedule ID_1].[Start]>=[Schedule ID].[End Date]) Or ([Schedule
ID_1].[End Date]<=[Schedule ID].[Start date]) Or ([Schedule ID].[Tech
ID]<>[Schedule ID_1].[Tech ID]) Or ([Schedule ID].[EventID]=[Schedule
ID_1].[EventID])
currently in BeforeUpdate I have
=[Start date]<=Schedule![Task Start Date] And [End Date]>=Schedule![Task
End
Date]
How do I include both of these functions to run before update.
Thanks again,
Allen Browne said:
Use DLookup() in the BeforeUpdate event of the form to see if a clashing
record exists.
The 3rd argument (Criteria) will be involved, but it will work.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Thank you for you reply.
Your website was very helpful in giving me the correct code to use.
I have one more question though. Is it possible for the conflict to be
determined without running a query? So at the point of entering the
conflicting schedule the user will receive a warning that the employee
is
already busy during the time requested. This way instead of searching
for
conflicts they are restricted form being entered. If not I can create
a
workaround however it would be best if I could avoid the conflicts all
together.
:
Perform the validation in the BeforeUpdate event of the form where
employees
are assigned to the project, i.e. in Form_BeforeUpdate.
Two assignments overlap if:
- A begins before B ends, AND
- B begins before A ends.
The assignment start date and end date should both be between the
project
start date and end date.
Use those facts to create a SQL statement, and OpenRecordset().
I am building a database for tracking employees assigned to projects
and
project tasks. I have two primary tables set up with Start Date and
End
Date, One for the project itself and one for the Employee asssigned
to
the
task. Mulipul Employees can be assigned to each task and project
which
is
perfect. However I need to limit the fields so an employee does not
get
double booked on two seperated projects during the same date range.
Additional I would like for the date range for the assignment to not
go
outside of the project date range.
Please Help!
