Scheduling Query/Clashing Events

  • Thread starter Thread starter Tom & Carol Satran
  • Start date Start date
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
 
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...
 
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...
 
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...
 
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...
 
Back
Top