Scheduling Query/Clashing Events

T

Tom & Carol Satran

Hi

I am new to this group and to access. I am trying to make a database for a
NFP Youth Organization. We have mutiple home teams, visiting teams, and
sites. I have the following in a before update event procedure. It isn't
working though and I am not sure why. Could someone please help me?

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String
varResult = DLookup("ScheduleID", "Schedule", strWhere)

If ((Me.StartTime = Me.StartTime.OldValue) And _
(Me.EndTime = Me.EndTime.OldValue) And _
(Me.SiteID = Me.SiteID.OldValue) And _
(Me.ActivityID = Me.ActivityID.OldValue)) And _
(Me.ActivityDate <> Me.ActivityDate.OldValue) Or
IsNull(Me.StartTime) Or IsNull(Me.EndTime) Or IsNull(Me.SiteID) Or
IsNull(Me.ActivityID) Or (Cancelled = True) Then
'do nothing
Else
If Not IsNull((Me.StartTime.OldValue >= Me.EndTime) Or _
(Me.EndTime.OldValue <= Me.StartTime) Or _
(Me.ActivityID <> Me.ActivityID.OldValue) Or _
(Me.SiteID <> Me.SiteID.OldValue)) Then
varResult = DLookup("ScheduleID", "Schedule", strWhere)
strMsg = "Clashes with Event # " & varResult & vbCrLf & "Continue
anyway?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Double-booked")
<> vbYes Then
Cancel = True
End If
End If
End If
End Sub

I have also tried running the Query on its own but then can not add any more
events once I put it on the form. The Query (below) works on its own.

SELECT Schedule.ScheduleID, Schedule.ActivityDate, Schedule.StartTime,
Schedule.EndTime, Schedule.SiteID, Schedule.ActivityID,
Schedule.VisitingActivityID, Nz((Schedule_1.StartTime>=[Schedule].[EndTime])
Or (Schedule_1.EndTime<=[Schedule].[StartTime]) Or
([Schedule].[SiteID]<>Schedule_1.SiteID) Or
([Schedule].[ActivityID]<>Schedule_1.ActivityID) Or
([Schedule].[ScheduleID]<>Schedule_1.ScheduleID),False) AS Expr1,
Nz((Schedule_1.StartTime>=[Schedule].[EndTime]) Or
(Schedule_1.EndTime<=[Schedule].[StartTime]) Or
([Schedule].[SiteID]<>Schedule_1.SiteID) Or
([Schedule].[ActivityID]<>Schedule_1.ActivityID) Or
([Schedule].[ScheduleID]<>Schedule_1.ScheduleID),False) AS Expr2
FROM Schedule, Schedule AS Schedule_1
GROUP BY Schedule.ScheduleID, Schedule.ActivityDate, Schedule.StartTime,
Schedule.EndTime, Schedule.SiteID, Schedule.ActivityID,
Schedule.VisitingActivityID, Nz((Schedule_1.StartTime>=[Schedule].[EndTime])
Or (Schedule_1.EndTime<=[Schedule].[StartTime]) Or
([Schedule].[SiteID]<>Schedule_1.SiteID) Or
([Schedule].[ActivityID]<>Schedule_1.ActivityID) Or
([Schedule].[ScheduleID]<>Schedule_1.ScheduleID),False)
HAVING (((Nz(([Schedule_1].[StartTime]>=[Schedule].[EndTime]) Or
([Schedule_1].[EndTime]<=[Schedule].[StartTime]) Or
([Schedule].[SiteID]<>[Schedule_1].[SiteID]) Or
([Schedule].[ActivityID]<>[Schedule_1].[ActivityID]) Or
([Schedule].[ScheduleID]<>[Schedule_1].[ScheduleID]),False))=True))
ORDER BY Schedule.StartTime;

Any help would be appreciated. I am doing this as a volunteer and to be
honest have spent days just trying to resolve this one issue.

Carol
 
B

Brendan Reynolds

The code never assigns any value to the "strWhere" variable.

--
Brendan Reynolds
Access MVP

Tom & Carol Satran said:
Hi

I am new to this group and to access. I am trying to make a database for
a
NFP Youth Organization. We have mutiple home teams, visiting teams, and
sites. I have the following in a before update event procedure. It isn't
working though and I am not sure why. Could someone please help me?

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String
varResult = DLookup("ScheduleID", "Schedule", strWhere)

If ((Me.StartTime = Me.StartTime.OldValue) And _
(Me.EndTime = Me.EndTime.OldValue) And _
(Me.SiteID = Me.SiteID.OldValue) And _
(Me.ActivityID = Me.ActivityID.OldValue)) And _
(Me.ActivityDate <> Me.ActivityDate.OldValue) Or
IsNull(Me.StartTime) Or IsNull(Me.EndTime) Or IsNull(Me.SiteID) Or
IsNull(Me.ActivityID) Or (Cancelled = True) Then
'do nothing
Else
If Not IsNull((Me.StartTime.OldValue >= Me.EndTime) Or _
(Me.EndTime.OldValue <= Me.StartTime) Or _
(Me.ActivityID <> Me.ActivityID.OldValue) Or _
(Me.SiteID <> Me.SiteID.OldValue)) Then
varResult = DLookup("ScheduleID", "Schedule", strWhere)
strMsg = "Clashes with Event # " & varResult & vbCrLf & "Continue
anyway?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Double-booked")
<> vbYes Then
Cancel = True
End If
End If
End If
End Sub

I have also tried running the Query on its own but then can not add any
more
events once I put it on the form. The Query (below) works on its own.

SELECT Schedule.ScheduleID, Schedule.ActivityDate, Schedule.StartTime,
Schedule.EndTime, Schedule.SiteID, Schedule.ActivityID,
Schedule.VisitingActivityID,
Nz((Schedule_1.StartTime>=[Schedule].[EndTime])
Or (Schedule_1.EndTime<=[Schedule].[StartTime]) Or
([Schedule].[SiteID]<>Schedule_1.SiteID) Or
([Schedule].[ActivityID]<>Schedule_1.ActivityID) Or
([Schedule].[ScheduleID]<>Schedule_1.ScheduleID),False) AS Expr1,
Nz((Schedule_1.StartTime>=[Schedule].[EndTime]) Or
(Schedule_1.EndTime<=[Schedule].[StartTime]) Or
([Schedule].[SiteID]<>Schedule_1.SiteID) Or
([Schedule].[ActivityID]<>Schedule_1.ActivityID) Or
([Schedule].[ScheduleID]<>Schedule_1.ScheduleID),False) AS Expr2
FROM Schedule, Schedule AS Schedule_1
GROUP BY Schedule.ScheduleID, Schedule.ActivityDate, Schedule.StartTime,
Schedule.EndTime, Schedule.SiteID, Schedule.ActivityID,
Schedule.VisitingActivityID,
Nz((Schedule_1.StartTime>=[Schedule].[EndTime])
Or (Schedule_1.EndTime<=[Schedule].[StartTime]) Or
([Schedule].[SiteID]<>Schedule_1.SiteID) Or
([Schedule].[ActivityID]<>Schedule_1.ActivityID) Or
([Schedule].[ScheduleID]<>Schedule_1.ScheduleID),False)
HAVING (((Nz(([Schedule_1].[StartTime]>=[Schedule].[EndTime]) Or
([Schedule_1].[EndTime]<=[Schedule].[StartTime]) Or
([Schedule].[SiteID]<>[Schedule_1].[SiteID]) Or
([Schedule].[ActivityID]<>[Schedule_1].[ActivityID]) Or
([Schedule].[ScheduleID]<>[Schedule_1].[ScheduleID]),False))=True))
ORDER BY Schedule.StartTime;

Any help would be appreciated. I am doing this as a volunteer and to be
honest have spent days just trying to resolve this one issue.

Carol
Click to expand...
 
T

Tom & Carol Satran

Okay, so what does that mean I have to do?

Carol
Brendan Reynolds said:
The code never assigns any value to the "strWhere" variable.

--
Brendan Reynolds
Access MVP

Tom & Carol Satran said:
Hi

I am new to this group and to access. I am trying to make a database for
a
NFP Youth Organization. We have mutiple home teams, visiting teams, and
sites. I have the following in a before update event procedure. It isn't
working though and I am not sure why. Could someone please help me?

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String
varResult = DLookup("ScheduleID", "Schedule", strWhere)

If ((Me.StartTime = Me.StartTime.OldValue) And _
(Me.EndTime = Me.EndTime.OldValue) And _
(Me.SiteID = Me.SiteID.OldValue) And _
(Me.ActivityID = Me.ActivityID.OldValue)) And _
(Me.ActivityDate <> Me.ActivityDate.OldValue) Or
IsNull(Me.StartTime) Or IsNull(Me.EndTime) Or IsNull(Me.SiteID) Or
IsNull(Me.ActivityID) Or (Cancelled = True) Then
'do nothing
Else
If Not IsNull((Me.StartTime.OldValue >= Me.EndTime) Or _
(Me.EndTime.OldValue <= Me.StartTime) Or _
(Me.ActivityID <> Me.ActivityID.OldValue) Or _
(Me.SiteID <> Me.SiteID.OldValue)) Then
varResult = DLookup("ScheduleID", "Schedule", strWhere)
strMsg = "Clashes with Event # " & varResult & vbCrLf & "Continue
anyway?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Double-booked")
<> vbYes Then
Cancel = True
End If
End If
End If
End Sub

I have also tried running the Query on its own but then can not add any
more
events once I put it on the form. The Query (below) works on its own.

SELECT Schedule.ScheduleID, Schedule.ActivityDate, Schedule.StartTime,
Schedule.EndTime, Schedule.SiteID, Schedule.ActivityID,
Schedule.VisitingActivityID,
Nz((Schedule_1.StartTime>=[Schedule].[EndTime])
Or (Schedule_1.EndTime<=[Schedule].[StartTime]) Or
([Schedule].[SiteID]<>Schedule_1.SiteID) Or
([Schedule].[ActivityID]<>Schedule_1.ActivityID) Or
([Schedule].[ScheduleID]<>Schedule_1.ScheduleID),False) AS Expr1,
Nz((Schedule_1.StartTime>=[Schedule].[EndTime]) Or
(Schedule_1.EndTime<=[Schedule].[StartTime]) Or
([Schedule].[SiteID]<>Schedule_1.SiteID) Or
([Schedule].[ActivityID]<>Schedule_1.ActivityID) Or
([Schedule].[ScheduleID]<>Schedule_1.ScheduleID),False) AS Expr2
FROM Schedule, Schedule AS Schedule_1
GROUP BY Schedule.ScheduleID, Schedule.ActivityDate, Schedule.StartTime,
Schedule.EndTime, Schedule.SiteID, Schedule.ActivityID,
Schedule.VisitingActivityID,
Nz((Schedule_1.StartTime>=[Schedule].[EndTime])
Or (Schedule_1.EndTime<=[Schedule].[StartTime]) Or
([Schedule].[SiteID]<>Schedule_1.SiteID) Or
([Schedule].[ActivityID]<>Schedule_1.ActivityID) Or
([Schedule].[ScheduleID]<>Schedule_1.ScheduleID),False)
HAVING (((Nz(([Schedule_1].[StartTime]>=[Schedule].[EndTime]) Or
([Schedule_1].[EndTime]<=[Schedule].[StartTime]) Or
([Schedule].[SiteID]<>[Schedule_1].[SiteID]) Or
([Schedule].[ActivityID]<>[Schedule_1].[ActivityID]) Or
([Schedule].[ScheduleID]<>[Schedule_1].[ScheduleID]),False))=True))
ORDER BY Schedule.StartTime;

Any help would be appreciated. I am doing this as a volunteer and to be
honest have spent days just trying to resolve this one issue.

Carol
Click to expand...
Click to expand...
 
T

Tom & Carol Satran

Someone please help me with this.. I have tried Allene Browne's webpage but
can't seem to get any further. I truly know very little code.

Carol
Tom & Carol Satran said:
Hi

I am new to this group and to access. I am trying to make a database for a
NFP Youth Organization. We have mutiple home teams, visiting teams, and
sites. I have the following in a before update event procedure. It isn't
working though and I am not sure why. Could someone please help me?

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String
varResult = DLookup("ScheduleID", "Schedule", strWhere)

If ((Me.StartTime = Me.StartTime.OldValue) And _
(Me.EndTime = Me.EndTime.OldValue) And _
(Me.SiteID = Me.SiteID.OldValue) And _
(Me.ActivityID = Me.ActivityID.OldValue)) And _
(Me.ActivityDate <> Me.ActivityDate.OldValue) Or
IsNull(Me.StartTime) Or IsNull(Me.EndTime) Or IsNull(Me.SiteID) Or
IsNull(Me.ActivityID) Or (Cancelled = True) Then
'do nothing
Else
If Not IsNull((Me.StartTime.OldValue >= Me.EndTime) Or _
(Me.EndTime.OldValue <= Me.StartTime) Or _
(Me.ActivityID <> Me.ActivityID.OldValue) Or _
(Me.SiteID <> Me.SiteID.OldValue)) Then
varResult = DLookup("ScheduleID", "Schedule", strWhere)
strMsg = "Clashes with Event # " & varResult & vbCrLf & "Continue
anyway?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Double-booked")
<> vbYes Then
Cancel = True
End If
End If
End If
End Sub

I have also tried running the Query on its own but then can not add any more
events once I put it on the form. The Query (below) works on its own.

SELECT Schedule.ScheduleID, Schedule.ActivityDate, Schedule.StartTime,
Schedule.EndTime, Schedule.SiteID, Schedule.ActivityID,
Schedule.VisitingActivityID, Nz((Schedule_1.StartTime>=[Schedule].[EndTime])
Or (Schedule_1.EndTime<=[Schedule].[StartTime]) Or
([Schedule].[SiteID]<>Schedule_1.SiteID) Or
([Schedule].[ActivityID]<>Schedule_1.ActivityID) Or
([Schedule].[ScheduleID]<>Schedule_1.ScheduleID),False) AS Expr1,
Nz((Schedule_1.StartTime>=[Schedule].[EndTime]) Or
(Schedule_1.EndTime<=[Schedule].[StartTime]) Or
([Schedule].[SiteID]<>Schedule_1.SiteID) Or
([Schedule].[ActivityID]<>Schedule_1.ActivityID) Or
([Schedule].[ScheduleID]<>Schedule_1.ScheduleID),False) AS Expr2
FROM Schedule, Schedule AS Schedule_1
GROUP BY Schedule.ScheduleID, Schedule.ActivityDate, Schedule.StartTime,
Schedule.EndTime, Schedule.SiteID, Schedule.ActivityID,
Schedule.VisitingActivityID, Nz((Schedule_1.StartTime>=[Schedule].[EndTime])
Or (Schedule_1.EndTime<=[Schedule].[StartTime]) Or
([Schedule].[SiteID]<>Schedule_1.SiteID) Or
([Schedule].[ActivityID]<>Schedule_1.ActivityID) Or
([Schedule].[ScheduleID]<>Schedule_1.ScheduleID),False)
HAVING (((Nz(([Schedule_1].[StartTime]>=[Schedule].[EndTime]) Or
([Schedule_1].[EndTime]<=[Schedule].[StartTime]) Or
([Schedule].[SiteID]<>[Schedule_1].[SiteID]) Or
([Schedule].[ActivityID]<>[Schedule_1].[ActivityID]) Or
([Schedule].[ScheduleID]<>[Schedule_1].[ScheduleID]),False))=True))
ORDER BY Schedule.StartTime;

Any help would be appreciated. I am doing this as a volunteer and to be
honest have spent days just trying to resolve this one issue.

Carol
Click to expand...
 
B

Brendan Reynolds

Well, as you are using the strWhere variable later, in the call to the
DLookup function, you probably should be assigning an appropriate value to
it first.

--
Brendan Reynolds
Access MVP

Tom & Carol Satran said:
Okay, so what does that mean I have to do?

Carol
Brendan Reynolds said:
The code never assigns any value to the "strWhere" variable.

--
Brendan Reynolds
Access MVP

Tom & Carol Satran said:
Hi

I am new to this group and to access. I am trying to make a database for
a
NFP Youth Organization. We have mutiple home teams, visiting teams,
and
sites. I have the following in a before update event procedure. It isn't
working though and I am not sure why. Could someone please help me?

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String
varResult = DLookup("ScheduleID", "Schedule", strWhere)

If ((Me.StartTime = Me.StartTime.OldValue) And _
(Me.EndTime = Me.EndTime.OldValue) And _
(Me.SiteID = Me.SiteID.OldValue) And _
(Me.ActivityID = Me.ActivityID.OldValue)) And _
(Me.ActivityDate <> Me.ActivityDate.OldValue) Or
IsNull(Me.StartTime) Or IsNull(Me.EndTime) Or IsNull(Me.SiteID) Or
IsNull(Me.ActivityID) Or (Cancelled = True) Then
'do nothing
Else
If Not IsNull((Me.StartTime.OldValue >= Me.EndTime) Or _
(Me.EndTime.OldValue <= Me.StartTime) Or _
(Me.ActivityID <> Me.ActivityID.OldValue) Or _
(Me.SiteID <> Me.SiteID.OldValue)) Then
varResult = DLookup("ScheduleID", "Schedule", strWhere)
strMsg = "Clashes with Event # " & varResult & vbCrLf & "Continue
anyway?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Double-booked")
<> vbYes Then
Cancel = True
End If
End If
End If
End Sub

I have also tried running the Query on its own but then can not add any
more
events once I put it on the form. The Query (below) works on its own.

SELECT Schedule.ScheduleID, Schedule.ActivityDate, Schedule.StartTime,
Schedule.EndTime, Schedule.SiteID, Schedule.ActivityID,
Schedule.VisitingActivityID,
Nz((Schedule_1.StartTime>=[Schedule].[EndTime])
Or (Schedule_1.EndTime<=[Schedule].[StartTime]) Or
([Schedule].[SiteID]<>Schedule_1.SiteID) Or
([Schedule].[ActivityID]<>Schedule_1.ActivityID) Or
([Schedule].[ScheduleID]<>Schedule_1.ScheduleID),False) AS Expr1,
Nz((Schedule_1.StartTime>=[Schedule].[EndTime]) Or
(Schedule_1.EndTime<=[Schedule].[StartTime]) Or
([Schedule].[SiteID]<>Schedule_1.SiteID) Or
([Schedule].[ActivityID]<>Schedule_1.ActivityID) Or
([Schedule].[ScheduleID]<>Schedule_1.ScheduleID),False) AS Expr2
FROM Schedule, Schedule AS Schedule_1
GROUP BY Schedule.ScheduleID, Schedule.ActivityDate,
Schedule.StartTime,
Schedule.EndTime, Schedule.SiteID, Schedule.ActivityID,
Schedule.VisitingActivityID,
Nz((Schedule_1.StartTime>=[Schedule].[EndTime])
Or (Schedule_1.EndTime<=[Schedule].[StartTime]) Or
([Schedule].[SiteID]<>Schedule_1.SiteID) Or
([Schedule].[ActivityID]<>Schedule_1.ActivityID) Or
([Schedule].[ScheduleID]<>Schedule_1.ScheduleID),False)
HAVING (((Nz(([Schedule_1].[StartTime]>=[Schedule].[EndTime]) Or
([Schedule_1].[EndTime]<=[Schedule].[StartTime]) Or
([Schedule].[SiteID]<>[Schedule_1].[SiteID]) Or
([Schedule].[ActivityID]<>[Schedule_1].[ActivityID]) Or
([Schedule].[ScheduleID]<>[Schedule_1].[ScheduleID]),False))=True))
ORDER BY Schedule.StartTime;

Any help would be appreciated. I am doing this as a volunteer and to
be
honest have spent days just trying to resolve this one issue.

Carol
Click to expand...
Click to expand...
Click to expand...
 
Top