By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,952 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

Scheduling Query/Clashing Events

P: n/a

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

Jan 13 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a

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

--
Brendan Reynolds
Access MVP

"Tom & Carol Satran" <tc******@frontiernet.net> wrote in message
news:Bo***************@news02.roc.ny...

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

Jan 13 '06 #2

P: n/a
Okay, so what does that mean I have to do?

Carol
"Brendan Reynolds" <br******@discussions.microsoft.com> wrote in message
news:OD**************@TK2MSFTNGP12.phx.gbl...

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

--
Brendan Reynolds
Access MVP

"Tom & Carol Satran" <tc******@frontiernet.net> wrote in message
news:Bo***************@news02.roc.ny...

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


Jan 16 '06 #3

P: n/a
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" <tc******@frontiernet.net> wrote in message
news:Bo***************@news02.roc.ny...

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

Jan 16 '06 #4

P: n/a

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" <tc******@frontiernet.net> wrote in message
news:Rt***************@news02.roc.ny...
Okay, so what does that mean I have to do?

Carol
"Brendan Reynolds" <br******@discussions.microsoft.com> wrote in message
news:OD**************@TK2MSFTNGP12.phx.gbl...

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

--
Brendan Reynolds
Access MVP

"Tom & Carol Satran" <tc******@frontiernet.net> wrote in message
news:Bo***************@news02.roc.ny...
>
> 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
>
>
>



Jan 17 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.